InterSystems SQL Reference
TIMESTAMPDIFF


A scalar date/time function that returns an integer count of the difference between two timestamps for a specified date part.
Synopsis
{fn TIMESTAMPDIFF(intervaltype,startdate,enddate)}
Arguments
Description
The TIMESTAMPDIFF function returns the difference between two given timestamps (that is, one timestamp is subtracted from the other) for the specified date part interval (seconds, days, weeks, etc.). The value returned is an INTEGER, the number of these intervals between the two timestamps. (If enddate is earlier than startdate, TIMESTAMPDIFF returns a negative INTEGER value.)
The
startdate and
enddate are timestamps. These timestamps can be in either
%Library.TimeStamp data type format (yyyymmdd hh:mm:ss.ffff) or
%Library.PosixTime data type format (an encoded 64bit signed integer).
The intervaltype argument can be one of the following timestamp intervals:

SQL_TSI_FRAC_SECOND

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_YEAR
These timestamp intervals may be specified with or without enclosing quotation marks, using single quotes or double quotes. They are not casesensitive.
TIMESTAMPDIFF and DATEDIFF do not handle quarters (3month intervals).
Note that
TIMESTAMPDIFF can only be used as an ODBC scalar function (with the curly brace syntax). Similar time/date comparison operations can be performed on a timestamp using the
DATEDIFF general function.
%TimeStamp Format
If the
startdate or
enddate argument is in
%Library.TimeStamp data type format (yyyymmdd hh:mm:ss.ffff) the following rules apply:

If either timestamp expression specifies only a time value and intervaltype specifies a date interval (days, weeks, months, or years), the missing date portion of the timestamp defaults to '1900–01–01' before calculating the resulting interval count.

If either timestamp expression specifies only a date value and intervaltype specifies a time interval (hours, minutes, seconds, fractional seconds), the missing time portion of the timestamp defaults to '00:00:00.000' before calculating the resulting interval count.

You can include or omit fractional seconds of any number of digits of precision. SQL_TSI_FRAC_SECOND returns a difference of fractional seconds as an integer count of thousandths of a second (three digits of precision). %PosixTime values always includes six digits of precision.
Range and Value Checking
TIMESTAMPDIFF performs the following checks on input values.

All specified parts of the startdate and enddate must be valid before any TIMESTAMPDIFF operation can be performed.

A date string must be complete and properly formatted with the appropriate number of elements and digits for each element, and the appropriate separator character. Years must be specified as four digits. An invalid date value results in an SQLCODE 8 error.

Date values must be within a valid range. Years: 0001 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 00 through 23. Minutes: 0 through 59. Seconds: 0 through 59. The number of days in a month must match the month and year. For example, the date '02–29' is only valid if the specified year is a leap year. An invalid date value results in an SQLCODE 8 error.

Date values less than 10 (month and day) may include or omit a leading zero. Other noncanonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.

Time values may be wholly or partially omitted. If startdate or enddate specifies an incomplete time, zeros are supplied for the unspecified parts.

An hour value less than 10 must include a leading zero. Omitting this leading zero results in an SQLCODE 8 error.
Examples
The following example returns 7 because the second timestamp (20171220 12:00:00) is 7 months greater than the first one:
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
'2017519 00:00:00','20171220 12:00:00')}
The following example returns 566 because the second timestamp ('12:00:00') is 566 minutes greater than the first one (02:34:12):
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'02:34:12','12:00:00')}
The following example returns 1440 because the second timestamp is one day (1440 minutes) lesser than the first one:
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'20171206','20171205')}
See Also
Content Date/Time: 20190919 06:56:35