EXTRACT()

<< EXP() | FB 2.1 Language Reference | FLOOR() >>

EXTRACT()

MILLISECOND
WEEK

`EXTRACT()`

Available in: DSQL, ESQL, PSQL

Added in: IB 6

Changed in: 2.1

Description

Extracts and returns an element from a DATE, TIME or TIMESTAMP expression. This function was already added in InterBase 6, but not documented in the Language Reference at the time.

Result type: SMALLINT or DECIMAL(6,4)

Syntax

 EXTRACT (<part> FROM <datetime>)

 <part>     ::= YEAR | MONTH | WEEK
              | DAY | WEEKDAY | YEARDAY
              | HOUR | MINUTE | SECOND | MILLISECOND
 <datetime> ::= a DATE, TIME or TIMESTAMP expression

The returned datatypes 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 12.2. Types and ranges of EXTRACT results

Part	Type	Range	Comment
`YEAR`	`SMALLINT`	`1–9999`
`MONTH`	`SMALLINT`	`1–12`
`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.0000–999.9`	broken in 2.1, 2.1.1

`MILLISECOND`

Added in: 2.1 (with bug)

Fixed in: 2.1.2

Description

Firebird 2.1 and up support extraction of the millisecond from a TIME or TIMESTAMP. The datatype returned is NUMERIC(9,1).

Bug alert: MILLISECOND extraction is broken in Firebird 2.1 and 2.1.1. In those versions, the number returned is an INTEGER including SECOND*1000, so if the time is e.g. 20:48:17.637, the MILLISECOND value is 17637 while it should be 637. This bug has been fixed in version 2.1.2.

Note: If you extract the millisecond from CURRENT_TIME, be aware that this variable defaults to seconds precision, so the result will always be 0. Extract from CURRENT_TIME(3) or CURRENT_TIMESTAMP to get milliseconds precision.

`WEEK`

Added in: 2.1

Description

Firebird 2.1 and up support extraction of the ISO-8601 week number from a DATE or TIMESTAMP. ISO-8601 weeks start on a Monday and always have the full seven days. Week 1 is the first week that has a majority (at least 4) of its days in the new year. The first 1–3 days of the year may belong to the last week (52 or 53) of the previous year. Likewise, a year's final 1–3 days may belong to week 1 of the following year.

Caution: Be careful when combining WEEK and YEAR results. For instance, 30 December 2008 lies in week 1 of 2009, so extract (week from date '30 Dec 2008') returns 1. However, extracting YEAR always gives the calendar year, which is 2008. In this case, WEEK and YEAR are at odds with each other. The same happens when the first days of January belong to the last week of the previous year.

Please also notice that WEEKDAY is not ISO-8601 compliant: it returns 0 for Sunday, whereas ISO-8601 specifies 7.