Unix timestamp to DB2 date and time conversion

milacek · #1 (**permalink**) 11-11-05, 08:07

Hi all,

I have the following problem:

In the column I have integer values with Unix timestamp, and need to write a trigger which will fill TIME and DATE column with the appropriate data.

Can someone help me how to build the trigger ?

Thanks to all

J Petruk · #2 (**permalink**) 11-11-05, 10:09

Looks like you're going to have to write a Java or C function, doesn't seem to be a built-in function to convert the epoch to DATE/TIME.

n_i · #3 (**permalink**) 11-11-05, 10:43

How about this:

Code:

$ db2 "values (timestamp('1970-01-01','00.00.00') + int(1131722349/86400) days + int(mod(1131722349,86400)) seconds)"

1
--------------------------
2005-11-11-15.19.09.000000

  1 record(s) selected.

It gives you a UTC timestamp; you may need to adjust for the local time zone.

J Petruk · #4 (**permalink**) 11-11-05, 11:02

Nice solution.

milacek · #5 (**permalink**) 11-23-05, 06:05

Nice - thank you in advance ....

Internet Services

Web Development

Digital Marketing

Consumer Tech