Skip to main content

EXTRACT function

Extracts a specified date or time component from a date, timestamp, or interval value.

Syntax

EXTRACT(field FROM source)

Arguments

  • field: The component to extract. Supported fields are:

    • For dates and timestamps: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND (Vertica only), MICROSECOND (Vertica only)

    • For intervals: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

  • source: A DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or INTERVAL value.

Returns

A BIGINT representing the extracted component.

Examples

Extracting from Dates

-- Example 1: Extract year
> SELECT EXTRACT(YEAR FROM DATE '2025-06-15');
2025

-- Example 2: Extract month
> SELECT EXTRACT(MONTH FROM DATE '2025-06-15');
6

-- Example 3: Extract day
> SELECT EXTRACT(DAY FROM DATE '2025-06-15');
15

Extracting from Timestamps

-- Example 4: Extract hour
> SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-06-15 14:30:45');
14

-- Example 5: Extract minute
> SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-06-15 14:30:45');
30

-- Example 6: Extract second
> SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-06-15 14:30:45');
45

-- Example 7: Extract milliseconds (Vertica only)
> SELECT EXTRACT(MILLISECOND FROM TIMESTAMP '2025-06-15 14:30:45.123456');
45123 -- Vertica
ERROR -- Databricks does not support MILLISECOND extraction

Extracting from Intervals

-- Example 8: Extract months from year-month interval
> SELECT EXTRACT(MONTH FROM INTERVAL '2-6' YEAR TO MONTH);
6

-- Example 9: Extract hours from day-time interval
> SELECT EXTRACT(HOUR FROM INTERVAL '3 12:30:00' DAY TO SECOND);
12

Using in Queries

-- Example 10: Group sales by year and month
> SELECT 
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(MONTH FROM sale_date) AS month,
    SUM(amount) AS total_sales
  FROM sales
  GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date);

-- Example 11: Filter by hour of day
> SELECT * FROM events
  WHERE EXTRACT(HOUR FROM event_time) BETWEEN 9 AND 17;

See Also