Skip to main content

TIMESTAMPDIFF (SQL)

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,startDate,endDate)}

Description

  • {fn TIMESTAMPDIFF(interval,startDate,endDate)} returns the difference between the starting and ending timestamps (startDate minus endDate) for the specified date part interval (seconds, days, weeks, and so on). The function returns an INTEGER value representing the number of intervals between the two timestamps. If endDate is earlier than startDate, TIMESTAMPDIFF returns a negative INTEGER value.

    This statement returns 12 because the second timestamp is 12 days greater than the first one. Both timestamps have a default time of 00:00:00.

    SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY,'2022-4-1','2022-4-13')}

    Example: Calculate Difference Between Timestamps

Arguments

interval

The type of time or date interval that the returned timestamp difference represents, specified as one of these timestamp intervals:

  • SQL_TSI_FRAC_SECOND — Fractional second intervals

  • SQL_TSI_SECOND — Second intervals

  • SQL_TSI_MINUTE — Minute intervals

  • SQL_TSI_HOUR — Hour intervals

  • SQL_TSI_DAY — Day intervals

  • SQL_TSI_WEEK — Week intervals

  • SQL_TSI_MONTH — Month intervals

  • SQL_TSI_YEAR — Year intervals

startDate,endDate

Timestamp value expressions representing the start and end date being compared, specified as one of these values:

You can specify these timestamp intervals with or without enclosing quotation marks, using single quotes or double quotes. They are not case-sensitive.

If either startDate or endDate uses the %Library.TimeStampOpens in a new tab format, these rules apply:

  • If either timestamp expression specifies only a time value and interval 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 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). %Library.PosixTimeOpens in a new tab values always includes six digits of precision.

Examples

Calculate Difference Between Timestamps

This statement returns 7 because the second timestamp (2021-12-20 12:00:00) is 7 months greater than the first one:

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
     '2021-5-19 00:00:00','2021-12-20 12:00:00')}

This statement 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')}

This statement returns -1440 because the second timestamp is one day (1440 minutes) less than the first one:

SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'2021-12-06','2021-12-05')}

Limitations

  • You can use TIMESTAMPDIFF only as an ODBC scalar function, which requires the curly brace syntax. To perform similar time and date comparison operations on a timestamp, use the DATEDIFF function.

More About

Range and Value Checking

Prior to performing the difference calculation, TIMESTAMPDIFF performs these checks on input values:

  • All specified parts of startDate and endDate are valid. Time values can be wholly or partially omitted. If startDate or endDate specifies an incomplete time, TIMESTAMPDIFF supplies zeros for the unspecified parts.

  • Date strings are complete and properly formatted with the appropriate number of elements, number of 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 are 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 valid only if the specified year is a leap year. An invalid date value results in an SQLCODE -8 error.

  • Date values contain only canonical integer values. Exception: Months and days with values less than 10 (month and day) can include a leading zero. Therefore, a day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.

  • Hour values less than 10 include a leading zero. Omitting this leading zero results in an SQLCODE -8 error.

See Also

FeedbackOpens in a new tab