EXTRACT(datetime)

Last Updated:2026-09-03 07:27:37

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 YEAR or MONTH, datetime is of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH data type.
If you specify DAY, datetime is of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND data type.
If you specify HOUR, MINUTE, or SECOND, datetime is of the TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND data type.
If you specify TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, or TIMEZONE_REGION, datetime is of the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data type.

Return type

If you extract TIMEZONE_REGION or TIMEZONE_ABBR, the return type is VARCHAR2.
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

Previous topic

DBTIMEZONE

Last

Next topic

FROM_TZ