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

Nine Stars

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

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
http://www.talendforge.org/forum/viewtopic.php?pid=21780
http://www.talendforge.org/forum/viewtopic.php?pid=35672

Accepted Solutions
Nine Stars

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

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

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Nine Stars

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

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
Nine Stars

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

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

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

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.
Community Manager

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

Shong, what do you think of this?
(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?nullSmiley Sadnew 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
----------------------------------------------------------
Talend | Data Agility for Modern Business