Purpose
EXTRACT(datetime) extracts a specific component, such as the year, month, day, hour, minute, second, and time zone, from the specified time 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 | Requests to return the year portion. |
| MONTH | Requests to return the month portion. |
| DAY | Requests to return the day portion. |
| HOUR | Requests to return the hour portion. |
| MINUTE | Requests to return the minute portion. |
| SECOND | Requests to return the seconds portion. |
| TIMEZONE_HOUR | Requests to return the hour portion of the time zone. |
| TIMEZONE_MINUTE | Requests to return the minute portion of the time zone. |
| TIMEZONE_REGION | Requests to return the region name of the time zone. Note: If a time zone numeric offset is provided in the time expression and the numeric offset may map to more than one time zone, UNKNOWN is returned. |
| TIMEZONE_ABBR | Requests to return the abbreviation of the name of the time zone. Note: If a time zone numeric offset is provided in the time expression and the numeric offset may map to more than one time zone, 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
YEARorMONTH,datetimeis of theDATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL YEAR TO MONTHdata type. - If you specify
DAY,datetimeis of theDATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL DAY TO SECONDdata type. - If you specify
HOUR,MINUTE, orSECOND,datetimeis of theTIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL DAY TO SECONDdata type. - If you specify
TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_ABBR, orTIMEZONE_REGION,datetimeis of theTIMESTAMP WITH TIME ZONEorTIMESTAMP WITH LOCAL TIME ZONEdata type.
Return type
If you extract
TIMEZONE_REGIONorTIMEZONE_ABBR, the return type isVARCHAR2.If you extract other components, the return type is
NUMBER.
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