FirebirdSQL logo

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

 

MILLISECOND

Extracts the millisecond value from a TIME or TIMESTAMP.The data type returned is NUMERIC(9,1).

Note

If you extract the millisecond from [fblangref50-contextvars-current-time], be aware that this variable defaults to seconds precision, so the result will always be 0.Extract from CURRENT_TIME(3) or [fblangref50-contextvars-current-timestamp] to get milliseconds precision.

WEEK

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

FIRST_DAY()

Returns the first day of a time period containing a datetime value

Result Type

DATE, TIMESTAMP (with or without time zone)

Syntax
FIRST_DAY(OF <period> FROM date_or_timestamp)

<period> ::= YEAR | MONTH | QUARTER | WEEK
Table 1. FIRST_DAY Function Parameters
Parameter Description

date_or_timestamp

Expression of type DATE, TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITH TIME ZONE

FIRST_DAY returns a date or timestamp (same as the type of date_or_timestamp) with the first day of the year, month or week of a given date or timestamp value.

Note
  • The first day of the week is considered as Sunday, following the same rules as for [fblangref50-scalarfuncs-extract] with WEEKDAY.

  • When a timestamp is passed, the return value preserves the time part.