One Star

[resolved] Split date and time from a TIMESTAMP in a tMap

Hi all,
I have 3 tables :
- table_1 includes a field_1 TIMESTAMP column (yyyy-mm-dd hh:mm:ss)
- table_2 includes a field_2 DATE column (yyyy-mm-dd)
- table_3 includes a field_3 TIME column (hh:mm:ss)
I'm trying to create 2 different inner join from table_1 to table_2 and table_3 inside a tMap.
Is Talend enough powerful to understand the two following INNER JOIN? Otherwise, which function should I use?
INNER JOIN ON table_1.field1 = table_2.field_2
INNER JOIN ON table_1.field1 = table_3.field_3

Thx a lot for any advice.
6 REPLIES
Five Stars

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

Before joining, I'd think you'd want to split field_1 TIMESTAMP in to two fields, one for the date and one for the time. Presumably, you're using some default dates and times in your two DIM tables, so you'd want to construct these two new fields using those.
Your join is then simple.
One Star

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

Hi tal00000,
This is johanglasses, I just found out that I have 2 accounts... anyway..
I'm sorry but I didin't really get the idea of your message.
Could you elaborate please?
Thanks.
Five Stars

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

You have a date dimension and a time dimension.
Assuming that your look-up keys are of a Date type (not clear from your screenshot), then you must be using a default time (midnight?) in the date dimension and a default date (1900-01-01?) in the time dimension.
In your input data, you have a date and time.
Use a tMap component to create two new Date output fields. One contains your date (with default time) and the other contains your time (with default date).
You can then use these for a simple join to your dimension tables.
One Star

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

Hi,
Ok, my bad, I'm not clear enough. Here is 3 screenshots of 3 tables.
- BI_ODS_ERP_CUSTOMER_ORDER is an operational table containing the field created_at
The datatype of this field is a TIMESTAMP (yyyy-mm-dd hh:mm:ss)
- BI_DWH_dimension_date is the Date dimension table containing the field full_date
The datatype of this field is a DATE (yyyy-mm-dd)
- BI_DWH_dimension_time is the Time dimension table containing the field full_time
The datatype of this field is a TIME (hh:mm:ss)
Now, in order to load data in my Fact_table, I want to do graphically the following Mysql code inside a tMap component.
SELECT * FROM BI_ODS_ERP_CUSTOMER_ORDER t1
INNER JOIN BI_DWH_dimension_date t2 ON t2.full_date = DATE(t1.created_at)
INNER JOIN BI_DWH_dimension_time t3 ON t3.full_time = TIME(t1.created_at)


So I am not sure I need any default date or default time.
I just want to truncate my field created_at so I can make 2 inner join.
Hope I was clearer.
Thanks for following up my issue.

One Star

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

Hi Seeusoon (still you Smiley Happy),
Maybe a job like this one can do the trick (sorry for my bad screenshot)
 
Hope it helps...
One Star

Re: [resolved] Split date and time from a TIMESTAMP in a tMap

Hi tdz,
Thx again for resolving 2 of my problems in a row Smiley Happy
I just adapt the code for the time as the one for the date like this:
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row1.fullDate))
TalendDate.parseDate("HH:mm:ss",TalendDate.formatDate("HH:mm:ss",row1.fullDate))