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.
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)