Oracle EXTRACT (Date/Time) Function: A Simple Guide

The EXTRACT function in Oracle SQL is a powerful tool for "pulling out" a single, specific part from a date or timestamp.

Want just the YEAR from a DATE? Or the HOUR from a TIMESTAMP? EXTRACT is the function you need. It's essential for filtering and grouping data by specific time periods.

What is the EXTRACT Function in Oracle?

The EXTRACT function retrieves the value of a specific datetime field (like MONTH, DAY, HOUR, etc.) from a DATE, TIMESTAMP, or INTERVAL value.

This is most commonly used in a WHERE clause to filter data (e.g., WHERE EXTRACT(YEAR FROM hire_date) = 2024) or in a GROUP BY clause to aggregate data (e.g., GROUP BY EXTRACT(MONTH FROM order_date)).

EXTRACT Function Syntax

The syntax for EXTRACT is a bit different from other functions. It uses a FROM keyword, making it very readable:

EXTRACT(field FROM datetime_expression)

Let's break that down:

• field: This is the part you want to pull out. Common values are:
  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
• datetime_expression: This is the source value, which is typically a column of type DATE or TIMESTAMP (e.g., hire_date, SYSTIMESTAMP).

Important: The DATE vs. TIMESTAMP Trap

This is the most common point of confusion when using EXTRACT.

• YEAR, MONTH, DAY: You can extract these from both DATE and TIMESTAMP types.
• HOUR, MINUTE, SECOND: You can only extract these from a TIMESTAMP or INTERVAL type.

The EXTRACT function treats the standard Oracle DATE type as an ANSI DATE, which (by definition) has no time components. If you try to EXTRACT(HOUR FROM SYSDATE), it will not work as expected. You must use SYSTIMESTAMP or CAST your DATE column to a TIMESTAMP.

Rule of Thumb:

• To get the year/month/day: EXTRACT(YEAR FROM my_date_column) is fine.
• To get the hour/minute/second: Use EXTRACT(HOUR FROM my_timestamp_column) or EXTRACT(HOUR FROM CAST(my_date_column AS TIMESTAMP)).

Oracle EXTRACT Function Examples

Here are two practical examples of how to use EXTRACT.

Example 1: Filtering by YEAR and MONTH (from a DATE) using EXTRACT

This is a very common use case. Let's find all employees from the employees table who were hired in June of 2005.

Query:

SELECT
  employee_id,
  last_name,
  hire_date
FROM employees
WHERE
  EXTRACT(YEAR FROM hire_date) = 2005
  AND EXTRACT(MONTH FROM hire_date) = 6;

Result: (Results will vary, but will only include employees hired in June 2005)

EMPLOYEE_ID LAST_NAME                 HIRE_DATE
----------- ------------------------- ---------
        105 Hunold                    25-JUN-05
        110 Chen                      28-JUN-05
        115 Urman                     07-JUN-05

Example 2: Getting the HOUR and MINUTE (from a TIMESTAMP) with EXTRACT

This example shows how to get the time components. We must use a TIMESTAMP value, such as LOCALTIMESTAMP (which returns the current time as a TIMESTAMP).

Query:

SELECT
  EXTRACT(HOUR FROM LOCALTIMESTAMP) AS "Current_Hour",
  EXTRACT(MINUTE FROM LOCALTIMESTAMP) AS "Current_Minute"
FROM DUAL;

Result: (Assuming the query was run at 16:30 or 4:30 PM)

Current_Hour Current_Minute
------------ --------------
          16             30