Search code examples
db2ibm-cloudepochdashdb

Db2: How to convert Unix / epoch time to timestamp?

I have a BIGINT value which represents a UNIX timestamp (epoch). How can I convert it to the built-in TIMESTAMP type?

As example, I want to turn 1611140400 into the related date and time. TIMESTAMP_FORMAT does not work.

Solution

  • You can use datetime arithmetics in Db2 and Db2 on Cloud. For Db2 on Cloud (which is running in UTC):

    VALUES (TIMESTAMP('1970-01-01') + 1611140400 seconds)

    Epoch is seconds since January 1st, 1970 GMT / UTC. Thus, adding your number as seconds to that date will give:

    2021-01-20 11:00:00.0

    If you are running in a different timezone, you need to take care of it, e.g.:

    VALUES (TIMESTAMP(‘1970-01-01-00.00.00.000000’) + 1611140400 seconds + current timezone)