FirebirdSQL logo

DATEDIFF()

Difference between two datetime values in a datetime unit

Result type

BIGINT, or NUMERIC(18,1) for MILLISECOND

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
Table 1. DATEDIFF Function Parameters
Parameter Description

unit

Date/time unit

moment1

An expression of the DATE, TIME or TIMESTAMP type

moment2

An expression of the DATE, TIME or TIMESTAMP type

Returns the number of years, months, weeks, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.

  • DATE and TIMESTAMP arguments can be combined.No other mixes are allowed.

  • With TIMESTAMP and DATE arguments, all units can be used.

  • 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.

DATEDIFF 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))

EXTRACT()

Extracts a datetime unit from a datetime value

Result type

SMALLINT or NUMERIC

Syntax
EXTRACT (<part> FROM <datetime>)

<part> ::=
    YEAR | MONTH | QUARTER | WEEK
  | DAY | WEEKDAY | YEARDAY
  | HOUR | MINUTE | SECOND | MILLISECOND
  | TIMEZONE_HOUR | TIMEZONE_MINUTE
<datetime> ::= a DATE, TIME or TIMESTAMP expression
Table 1. EXTRACT Function Parameters
Parameter Description

part

Date/time unit

datetime

An expression of the DATE, TIME or TIMESTAMP type

Extracts and returns an element from a DATE, TIME or TIMESTAMP expression.

Returned Data Types and Ranges

The returned data types and possible ranges are shown in the table below.If you try to extract a part that isn’t present in the date/time argument (e.g. SECOND from a DATE or YEAR from a TIME), an error occurs.

Table 1. Types and ranges of EXTRACT results
Part Type Range Comment

YEAR

SMALLINT

1-9999

 

MONTH

SMALLINT

1-12

 

QUARTER

SMALLINT

1-4

 

WEEK

SMALLINT

1-53

 

DAY

SMALLINT

1-31

 

WEEKDAY

SMALLINT

0-6

0 = Sunday

YEARDAY

SMALLINT

0-365

0 = January 1

HOUR

SMALLINT

0-23

 

MINUTE

SMALLINT

0-59

 

SECOND

NUMERIC(9,4)

0.0000-59.9999

includes millisecond as fraction

MILLISECOND

NUMERIC(9,1)

0.0-999.9

 

TIMEZONE_HOUR

SMALLINT

-23 - +23

 

TIMEZONE_MINUTE

SMALLINT

-59 - +59