4.9.2.87 TIMESTAMPDIFF function (ODBC compatible)

Syntax

TIMESTAMPDIFF(interval, date_time_exp1, date_time_exp2)
interval::
          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

Description

Returns an integer representing the number of intervals by which date_time_exp2 is greater than date_time_exp1.

Arguments

interval

Keywords that specify the interval in which to express the difference between the two date-time arguments. The SQL_TSI_FRAC_SECOND keyword specifies fractional seconds as billionths of a second.

date_time_exp1

A date-time expression which TIMESTAMPADD subtracts from date_time_exp2.

date_time_exp1

A date-time expression from which TIMESTAMPADD subtracts date_time_exp1.

Example

The following example displays difference in seconds between the current system time and one day later.

> select timestampdiff(sql_tsi_second, sysdate, sysdate + 1) from
syscalctable;
86400
-----
86400

Notes

If either date-time expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that expression is set to the current date before calculating the difference between the expressions.

If either date-time expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that expression is set to 0 before calculating the difference between the expressions.