Extract Expressions
Syntax
extract_expression ::= EXTRACT "(" id FROM expression ")"Semantics
The extract expression extract a component from a timestamp. Specifically, the expression after the FROM keyword must return at most one timestamp or NULL. If the result of this expression is NULL or empty, the results of EXTRACT is also NULL or empty, respectively. Otherwise, the component specified by the id is returned. This id must be one of the following keywords:
- YEAR
- Returns the year for the timestamp, in the range -6383 ~ 9999.
- MONTH
- Returns the month for the timestamp, in the range 1 ~ 12.
- DAY
- Returns the day of month for the timestamp, in the range 1 ~ 31.
- HOUR
- Returns the hour of day for the timestamp, in the range 0 ~ 23.
- MINUTE
- Returns the minute for the timestamp, in the range 0 ~ 59.
- SECOND
- Returns the second for the timestamp, in the range 0 ~ 59.
- MILLISECOND
- Returns the fractional second in millisecond for the timestamp, in the range 0 ~ 999.
- MICROSECOND
- Returns the fractional second in microsecond for the timestamp, in the range 0 ~ 999999.
- NANOSECOND
- Returns the fractional second in nanosecond for the timestamp, in the range 0 ~ 999999999.
- WEEK
- Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54.
- ISOWEEK
- Returns the week number within the year based on IS0-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53.
There are specific built-in functions to extract each of the above components from a time stamp. For example, EXTRACT(YEAR from expr) is equivalent to year(expr). These and other built-in functions are described in Built-in Functions.