Purpose
This function extracts elements such as year, month, day, hour, minute, second, and time zone from a specified datetime field or expression.
Syntax
EXTRACT ({ YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM datetime)
Parameters
Parameter |
Description |
|---|---|
| YEAR | Specifies the year to extract. |
| MONTH | Specifies the month to extract. |
| DAY | Specifies the date to extract. |
| HOUR | Specifies the hour to extract. |
| MINUTE | Specifies the minute to extract. |
| SECOND | Specifies the second to extract. |
| TIMEZONE_HOUR | Specifies the hour of the time zone to extract. |
| TIMEZONE_MINUTE | Specifies the minute of the time zone to extract. |
| TIMEZONE_REGION | Specifies the name of the time zone region to return. Note If the time expression provides a numeric time zone offset and this offset can map to multiple time zone region names, UNKNOWN is returned. |
| TIMEZONE_ABBR | Specifies the abbreviation of the time zone region name to return. Note If the time expression provides a numeric time zone offset and this offset can map to multiple time zone region names, UNK is returned. |
| datetime | A value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND data type. |
Note
- If you specify to extract
YEARorMONTH, thedatetimedata type must beDATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL YEAR TO MONTH. - If you specify to extract
DAY, thedatetimedata type must beDATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL DAY TO SECOND. - If you specify to extract
HOUR,MINUTE, orSECOND, thedatetimedata type must beTIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL DAY TO SECOND. - If you specify to extract
TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_ABBR, orTIMEZONE_REGION, thedatetimedata type must beTIMESTAMP WITH TIME ZONEorTIMESTAMP WITH LOCAL TIME ZONE.
Return type
When you extract the
TIMEZONE_REGIONorTIMEZONE_ABBRelement, the return value is of theVARCHAR2data type.When you extract other elements, the return value is of the
NUMBERdata type.
Examples
Execute the following statement:
obclient> SELECT EXTRACT(HOUR FROM TIMESTAMP '2021-11-17 12:38:40') "HOUR",
EXTRACT(MINUTE FROM TIMESTAMP '2021-11-17 12:38:40') "MINUTE",
EXTRACT(SECOND FROM TIMESTAMP '2021-11-17 12:38:40') "SECOND",
EXTRACT(DAY FROM TIMESTAMP '2021-11-17 12:38:40') "DAY",
EXTRACT(MONTH FROM TIMESTAMP '2021-11-17 12:38:40') "MONTH",
EXTRACT(YEAR FROM TIMESTAMP '2021-11-17 12:38:40') "YEAR",
EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP '2021-11-17 12:38:40 +08:00') "TIMEZONE_HOUR",
EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '2021-11-17 12:38:40 +08:00') "TIMEZONE_REGION",
EXTRACT(TIMEZONE_ABBR FROM TIMESTAMP '2021-11-17 12:38:40 +08:00') "TIMEZONE_ABBR"
FROM DUAL;
+------+--------+--------+------+-------+------+---------------+-----------------+---------------+
| HOUR | MINUTE | SECOND | DAY | MONTH | YEAR | TIMEZONE_HOUR | TIMEZONE_REGION | TIMEZONE_ABBR |
+------+--------+--------+------+-------+------+---------------+-----------------+---------------+
| 12 | 38 | 40 | 17 | 11 | 2021 | 8 | UNKNOWN | UNK |
+------+--------+--------+------+-------+------+---------------+-----------------+---------------+
1 row in set