CODEWITHSUNDEEP
sqldb2
JSS
JSS

Reputation: 2183

How can I convert DB2 TIMESTAMP to UTC milliseconds?

Is there a DB2 function to convert TIMESTAMP to UTC milliseconds?

Upvotes: 4

Views: 13495

Answers (2)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

Assuming that you're looking to get the number of milliseconds since the epoch (1970-01-01 00:00:00), there is not a built-in function to do this.

However it's trivial to create a UDF that can:

CREATE OR REPLACE FUNCTION utcmillis (in db2ts timestamp)
   returns bigint
   language sql
   deterministic
   no external action
   return (
      bigint((days(db2ts - current timezone) - days('1970-01-01-00.00.00.000000')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000
   );

Upvotes: 2

Lucas
Lucas

Reputation: 14939

You could use TIMESTAMPDIFF. Something like this:

TIMESTAMPDIFF(1,CHAR(timestampField -
    TIMESTAMP('1970-01-01-00.00.00.000000')))

Assuming you mean milliseconds since unix epoch. I did not test this, and you may have to adjust the value by some factor of 10 as the db2 documentation only says Fractions of a second.

Upvotes: 0

Related Questions