CODEWITHSUNDEEP
db2timestamp
Manoj
Manoj

Reputation: 5612

Conversion between timestamp to milliseconds in DB2

I have a column of datatype timestamp. Now I need to convert it to MiiliSeconds and put in another column. How can I do that. the input is of the format 2011-10-04 13:54:50.455227 and the output needs to be 1317900719

Upvotes: 1

Views: 8308

Answers (2)

Stavr00
Stavr00

Reputation: 3314

Your requested output is not miliseconds, but the equivalent to CLib localtime(), here's how to do it:

SELECT
    86400*
    (
        DAYS(TIMESTAMP(v_timestamp))
        -
        DAYS(TIMESTAMP('1970-01-01-00:00:00'))
    )
    +
    MIDNIGHT_SECONDS(timestamp(v_timestamp))
FROM
    SYSIBM.SYSDUMMY1;

where v_timestamp is the variable or column to be calculated.

Upvotes: 1

user918176
user918176

Reputation: 1800

There's a function called timestampdiff. Using it against January 1st 1970 would work otherwise but the function gives approximate results. If you want accuracy you will want to calculate the correct answer with something like

create function ts2millis(t timestamp)
returns bigint
return (
 ( 
  (bigint(year(t-1970))*bigint(31556926000))+
  (bigint(month(t))*bigint(2629743000))+
  (bigint(day(t))*bigint(86400000))+
  (bigint(hour(t))*bigint(3600000))+
  (bigint(minute(t))*bigint(60000))+
  (bigint(second(t))*bigint(1000))+
  (bigint(microsecond(t))/bigint(1000))
 )
)
@

Upvotes: 1

Related Questions