DB2 convert long value to timestamp

Question

Is there a scalar function in DB2 to convert a long number to TIMESTAMP?

Solution

As @Dan1111 points out; no, there's nothing built in.

However, if you've got a 'long' number (I'm assuming BIGINT), I'm guessing you have a count of seconds (or similar) from the Unix Epoch (1970-01-01 00:00:00.000 UTC). If so, it's easy to 'cheat', and you can use this logic to write your own:

SELECT TIMESTAMP('1970-01-01', '00:00:00') + <your_column> SECONDS
FROM <your_table>

This of course presumes that the count is actually from UTC (and that you plan to interpret the results as such), as daylight savings time (and timezones, to a lesser extent) screws things up royally.

---

A quick example:

SELECT TIMESTAMP('1970-01-01', '00:00:00') + 1348241581 SECONDS                                      
FROM sysibm/sysdummy1      

Yields the expected:

2012-09-21-15.33.01.000000

(GMT, obviously)

Comments

[Stavr00]

Add the CURRENT TIMEZONE system variable to convert to local time.

[Clockwork-Muse]

@Stavr00 - When you're dealing with absolute times, it's better to work in UTC, and only translate when you go to display it (if necessary).