Epoch string to Date/Timestamp?

[l***@hsbc.com]

Hi,

I have a table which stores an epoch time string (in milliseconds) as a bigint - is there any way using DB2/SQL alone that I can cast/convert this as a 'readable' timestamp (no real preference on the formatting)?

Thanks,

Louis

[Denis Vasconcelos]

Could you provide a sample of this epoch format?
---
Denis Vasconcelos
DB2 DBA
Global Business Services - Application Services
IBM Brazil

[Louis Brook]

Denis,

Thanks for your reply.

An example of an epoch date entry would be "1176468650000" - this equates to April, 13 2007 13:50:50.

As far as I'm aware, we don't record down to milliseconds so the trailing numbers are always zeroes.

Thanks,

Louis

[Doug Doole]

DB2 actually has microsecond resolution in its TIMESTAMP data type, but not all platforms have that resolution in their clocks.

For your specific question, take a look at datatime arithmetic in the SQL Reference. Then you can write something like:

SELECT TIMESTAMP('1970-01-01-00.00.00.000000') + (epoch/1000) SECONDS FROM mytab

1
--------------------------
2007-04-13-12.50.50.000000

You could encapsulate the datetime expression into a UDF if you need to use it in a lot of places
-----------------------------------
Doug Doole
DB2 Universal Database Development
IBM Toronto Labs

[Louis Brook]

Thanks Doug, that works a treat.

Our box does have the accurarcy built in but we're not that accurate when recording times so the solution you've provided works great.

Thanks again,

Louis