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

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

Anonymous — Wed, 06 Jan 2016 02:32:01 GMT

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.

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

Anonymous — Wed, 06 Jan 2016 10:48:44 GMT

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.

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

Anonymous — Fri, 08 Jan 2016 05:52:07 GMT

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.

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

Anonymous — Sat, 09 Jan 2016 09:30:07 GMT

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.

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

Anonymous — Tue, 12 Jan 2016 03:37:58 GMT

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-d d 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.

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

Anonymous — Tue, 12 Jan 2016 10:21:31 GMT

Hi Seeusoon (still you ),
Maybe a job like this one can do the trick (sorry for my bad screenshot)

Hope it helps...

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

Anonymous — Wed, 13 Jan 2016 03:47:50 GMT

Hi tdz,
Thx again for resolving 2 of my problems in a row
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))