DB2: Converting Unix time

Sometimes you've got to work with POSIX time, which might be known more commonly as UNIX time (http://en.wikipedia.org/wiki/Unix_time). While some newer systems already use a 64bit value, the standard Unix time_t usually is a signed 32bit integer which represents the seconds elapsed since 1970-01-01UTC00:00:00.

Assuming in your DB2 database there's sample.table with a column containing t_time and named the same. A conversion from time_t to a DB2 timestamp could be done like this:

SELECT
  DISTINCT time_t,
  timestamp('1970-01-01-00.00.00') + (time_t) seconds
  as calculated_timestamp
FROM
  sample.table
WHERE
  time_t=946684800
;

The result would look like the following:

TIME_T      CALCULATED_TIMESTAMP
----------- --------------------------
  946684800 2000-01-01-00.00.00.000000

  1 record(s) selected.

This is also useful for changing the time value permanently from time_t to timestamp when working with DB2 on Unix log data. Daily logs containing time_t could be loaded into a temporary table in the first step. When adding the data to the permament table (working with a timestamp column) , a load is to be done from a cursor which has to be defined as SELECT using the above mentioned conversion.