Talend community forum / Tmap - Integer (with Unix epoch time) convert to Timestamp?

talendtester · 2011-04-08 00:54:22

Tags: [convert, CSV file, date, delimited file, epoch, java, TimeStamp, UNIX]

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/postga … name=1.png
http://www.talendforge.org/forum/viewto … ?pid=21780
http://www.talendforge.org/forum/viewto … ?pid=35672

shong · 2011-04-08 03:24:07

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/5350 … ate-object

Best regards
Shong

talendtester · 2011-04-08 20:52:21

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

talendtester · 2011-04-08 23:09:27

Shong, what do you think of this?

(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?null:(new java.util.Date((Long.parseLong(row1.myTimeColumn )+ 21600)*1000));

talendtester · 2011-04-09 01:54:39

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.

shong · 2011-04-11 04:32:24

talendtester wrote:
Shong, what do you think of this?

(row1.myTimeColumn ==null || row1.myTimeColumn =="" )?null:(new 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

Announcement

Unanswered posts

#1 2011-04-08 00:54:22

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

#2 2011-04-08 03:24:07

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

#3 2011-04-08 20:52:21

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

#4 2011-04-08 23:09:27

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

#5 2011-04-09 01:54:39

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

#6 2011-04-11 04:32:24

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

talendtester wrote:

Board footer