Skip to main content
Previous sectionNext section

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(interval-type,startdate,enddate)}

Arguments

Argument Description
interval-type The type of time/date interval that the returned value will represent.
startdate A timestamp value expression.
enddate A timestamp value expression that will be compared to startdate.

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 (yyyy-mm-dd hh:mm:ss.ffff) or %Library.PosixTime data type format (an encoded 64-bit signed integer).

The interval-type 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 case-sensitive.

TIMESTAMPDIFF and DATEDIFF do not handle quarters (3-month 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 (yyyy-mm-dd hh:mm:ss.ffff) the following rules apply:

  • If either timestamp expression specifies only a time value and interval-type 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 interval-type 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 non-canonical 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 (2017-12-20 12:00:00) is 7 months greater than the first one:

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
     '2017-5-19 00:00:00','2017-12-20 12:00:00')}
Copy code to clipboard

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')}
Copy code to clipboard

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,'2017-12-06','2017-12-05')}
Copy code to clipboard

See Also