EXTRACT

Overview

EXTRACT is the standard SQL syntax for reading a specific field out of a temporal value: EXTRACT(YEAR FROM order_date), EXTRACT(DOW FROM order_date) for day of week, EXTRACT(HOUR FROM event_timestamp), and so on. It returns a plain number, not another date, which makes it useful for filtering, grouping by calendar components, or building calculated columns like "is this a weekend order."

Copy code
SELECT
  order_id,
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(DOW FROM order_date) AS day_of_week   -- 0 = Sunday
FROM orders;

Common fields include YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), and DOY (day of year); support for less common fields (like ISOYEAR or EPOCH) varies by engine.

EXTRACT vs DATE_TRUNC

Use EXTRACT when you need a raw number for filtering or a calculated field (e.g., WHERE EXTRACT(HOUR FROM ts) BETWEEN 9 AND 17 for business hours). Use DATE_TRUNC when you want to group by a time period while keeping a real date/timestamp value for display, sorting, or joining against a calendar dimension.

DuckDB specifics

DuckDB supports both the ANSI EXTRACT(field FROM source) syntax and a function-call form, date_part('field', source), which is easier to use with dynamic or parameterized field names:

Copy code
SELECT date_part('year', order_date) AS order_year FROM orders;

DuckDB additionally supports EXTRACT(EPOCH FROM ts) to get a Unix timestamp (seconds since 1970-01-01), which is handy for converting timestamps into a sortable numeric form for computations like duration math.