TIMESTAMPDIFF returns the difference between two timestamp values, based on a specified interval type.

Syntax

{fn TIMESTAMPDIFF ( timeInterval, timestampExp1, timestampExp2 )}

Arguments

  • timeInterval: A day or time unit. The following values are supported:
    • SQL_TSI_FRAC_SECOND
    • SQL_TSI_SECOND
    • SQL_TSI_MINUTE
    • SQL_TSI_HOUR
    • SQL_TSI_DAY
    • SQL_TSI_WEEK
    • SQL_TSI_MONTH
    • SQL_TSI_QUARTER
    • SQL_TSI_YEAR
  • timestampExp1, timestampExp2: A TIMESTAMP expression. Use the CAST or the TIMESTAMP function to convert date and time values.

Returns

  • An integer value.

Examples

-- This query returns the fractional second processing times of query plans in the fqoplans table.

SELECT ssid, {fn TIMSTAMPDIFF(SQL_TSI_FRAC_SECOND, starttime, endtime)} AS timediff, starttime, endtime
FROM CIRRO.cirrosys.cirrosys.fqoplans
WHERE starttime < endtime LIMIT 10;

VALUES {fn TIMESTAMPDIFF(SQL_TSI_HOUR, CAST('2012-06-20 15:23:45.587' AS TIMESTAMP), CAST('2012-06-20 16:23:45.587' AS TIMESTAMP))};
-- Returns 1