---
title: "EXTRACT"
description: "EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value."
canonical: "https://motherduck.com/glossary/extract/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Why Use DuckDB for Analytics?"
    url: "https://motherduck.com/blog/six-reasons-duckdb-slaps/"
  - title: "MD_GET_DIVE_VERSION | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/dives/md-get-dive-version/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# EXTRACT

> EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value.

## 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."

```sql
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:

```sql
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.