Search code examples
db2db2-luw

DB2 Milliseconds between two dates

I have two dates with datatype timestamp(10). Now I want to calculate the milliseconds between these two dates.

SELECT TO_CHAR(TIMESTAMPDIFF(1,CHAR(endtime- starttime))) AS "ms"
FROM TABLE

I got current error:

[Code: -183, SQL State: 22008]  A datetime arithmetic operation or a datetime scalar function has a result that is not within the va`lid range of dates.. SQLCODE=-183, SQLSTATE=22008, DRIVER=4.28.11
Solution

  • There is more accurate TIMESTAMPDIFF alternative.

    SELECT 
  TIMESTAMPDIFF(1, CHAR (endtime - starttime)) / 1000
    AS TSDIFF_ORIG
, (DAYS             (endtime) - DAYS             (starttime)) * BIGINT (86400000)
+ (MIDNIGHT_SECONDS (endtime) - MIDNIGHT_SECONDS (starttime)) * 1000
+ (MICROSECOND      (endtime) - MICROSECOND      (starttime)) / 1000
    AS TSDIFF_REAL
FROM (VALUES (CURRENT TIMESTAMP, CURRENT TIMESTAMP + 35 MINUTE + 1.001 SECOND)) T (starttime, endtime)
    TSDIFF_ORIG TSDIFF_REAL
    2101001 2101001