I have a column which gives date as Java long type - 1241436617660,1241436617145 etc. How do I convert this to

Required Result as -
Mon May 04 07:30:18 EDT 2009

select td from table_time;

Input
td - 1241436617660,1241436617145

I assume the value is in milliseconds based on you other post.

select timestamp('01/01/1970','00:00:00') + (Mycol * 1000) microseconds from ...


Andy

I thoght that you need to do conversions of these three steps.
1) Miliseconds to DB2 TIMESTAMP.
2) UTC to EDT(local time zone).
3) Format it as 'DAY MON DD HH24:MI:SS EDT YYYY'.

Andy answered for step 1).

For step 2), add CURRENT TIMEZONE to UTC.
Or, you may want to use NEW_TIME in Sample UDFs for Migration.

For step 3), you may want to use VARCHAR_FORMAT(a synonym is TO_CHAR) for DD, HH24, MI. SS and YYYY
on DB2 for LUW 9.5, DB2 for iSeries V6R1 or DB2 for z/OS V9.
You can use SUBSTR(DAYNAME(ts), 1, 3) for DAY and SUBSTR(MONTHNAME(ts), 1, 3) for MON.

I'm using this, but time seems to invalid....

DATE(
TIMESTAMP('1970-01-01','00.00.00')
+ (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,

TIME(
TIMESTAMP('1970-01-01-00.00.00')
+ (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds

Please advise.

Conversion from Timestamp to Decimal will return yyyymmddhhmmss.nnnnnn.

So, CAST(t.timestampis as DECIMAL(31,0))/1000 may return yyyymmddhhm.