topic Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp? in Talend Studio

[resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

talendtester — Sat, 16 Nov 2024 12:59:34 GMT

I have a table with a column that is an integer data type that contains Unix epoch time data.
Here is a sample of the data:
1298226724
1298226722
1298226723
Using this converter:
http://www.epochconverter.com/
I get the following:
Epoch Timestamps: 1298260810
GMT: Mon, 21 Feb 2011 04:00:10 GMT
Your timezone: Sunday, February 20, 2011 9:00:10 PM
This is the code I am using for checking for nulls and then doing the conversion:
(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?null:
(TalendDate.parseDate("yyyy-MM-dd",row1.myTimeColumn));
My Input:| My Output:
1298226724|1300-02-24T00:00:00
1298226722|1300-02-22T00:00:00
1298226723|1300-02-23T00:00:00
It looks like the month and the day is correct, but the year and the time don't seem to work.
I found these posts, but not sure what I should do differently:
http://www.talendforge.org/forum/postgallery.php?pid=21776&filename=1.png
https://community.talend.com/t5/Archive/Choose-between-two-tOracleConnection/td-p/179455
http://www.talendforge.org/forum/viewtopic.php?pid=35672

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

Anonymous — Fri, 08 Apr 2011 02:24:07 GMT

Hello
Try this expression on tMap:
(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?null:
(new java.util.Date(Long.parseLong(row1.myTimeColumn) * 1000) );
About how to convert Unix epoch time to human readable date in Java, see
http://stackoverflow.com/questions/535004/unix-epoch-time-to-java-date-object
Best regards
Shong

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

talendtester — Fri, 08 Apr 2011 19:52:21 GMT

That helps get me closer, just need to figure out why the hours don't work, looks like they are off by 6 hours.
1298226723 would be GMT: Sun, 20 Feb 2011 18:32:03 GMT
My Input:|My Output:
2011-02-20 12:32:04|1298226724
2011-02-20 12:32:02|1298226722
2011-02-20 12:32:03|1298226723

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

talendtester — Fri, 08 Apr 2011 22:09:27 GMT

Shong, what do you think of this?
(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?null new java.util.Date((Long.parseLong(row1.myTimeColumn ) + 21600)*1000));

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

talendtester — Sat, 09 Apr 2011 00:54:39 GMT

I think the 6 hour difference is because the difference between GMT and CST in Feb 2011 was 6 hours and the server I am running Talend on is using Central time.

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

Anonymous — Mon, 11 Apr 2011 03:32:24 GMT

Shong, what do you think of this?
(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?nullnew java.util.Date((Long.parseLong(row1.myTimeColumn )+ 21600)*1000));

You are right, you need add 6 hours if you are on CST.
Best regards
Shong

Re: [resolved] Tmap - Integer (with Unix epoch time) convert to Timestamp?

SJeshwani1618330394 — Thu, 23 Sep 2021 18:39:57 GMT

Hi,

I am also facing the same issue.

I resolved to convert epoch time but it's giving me 1985-11-15 01:00:00 instead of 1985-11-15 00:00:00. The only difference is the hours.

It would be great if you can help me 🙂

Regards,

Sheetal