0

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.

data_henrik
  • 16,724
  • 2
  • 28
  • 49

1 Answers1

2

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)
data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • 1
    Unix epoch time is the number of seconds since 1970-01-01 00:00:00 *GMT*, so you need to account for this when doing the conversion to a local time stamp (unless you’re in GMT already): `VALUES TIMESTAMP(‘1970-01-01-00.00.00.000000’) + 1611140400 seconds + current timezone` – Ian Bjorhovde Apr 14 '21 at 14:23
  • added it in, also that Db2 on Cloud is in UTC – data_henrik Apr 14 '21 at 14:36
  • I found I had to divide the BIGINT value by 1000, presumably in my case the BIGINT represents milliseconds since 1 Jan 1970, and there is no option to add/subtract milliseconds. – Vince Hill May 23 '22 at 04:41