DATEDIFF()

Available in: DSQL, PSQL

Added in: 2.1

Changed in: 2.5

Description: Returns the number of years, months, weeks, days, hours, minutes, seconds or milliseconds elapsed between two date/time values. (The WEEK unit is new in 2.5.)

Result type: BIGINT

Syntax:

DATEDIFF (<args>)

<args>     ::=  <unit> FROM <moment1> TO <moment2>
                | <unit>, <moment1>, <moment2>

<unit>     ::=  YEAR | MONTH | WEEK | DAY
                  | HOUR | MINUTE | SECOND | MILLISECOND
<momentN>  ::=  a DATE, TIME or TIMESTAMP expression
DATE and TIMESTAMP arguments can be combined. No other mixes are allowed.

With TIMESTAMP and DATE arguments, all units can be used. (Prior to Firebird 2.5, units smaller than DAY were disallowed for DATEs.)

With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Computation:

DATEDIFF doesn't look at any smaller units than the one specified in the first argument. As a result,
- “datediff (year, date '1-Jan-2009', date '31-Dec-2009')” returns 0, but
- “datediff (year, date '31-Dec-2009', date '1-Jan-2010')” returns 1
It does, however, look at all the bigger units. So:
- “datediff (day, date '26-Jun-1908', date '11-Sep-1973')” returns 23818
A negative result value indicates that moment2 lies before moment1.

Examples:

datediff (hour from current_timestamp to timestamp '12-Jun-2059 06:00')
datediff (minute from time '0:00' to current_time)
datediff (month, current_date, date '1-1-1900')
datediff (day from current_date to cast(? as date))