EXTRACT()

Syntax

How it works

EXTRACT(part FROM date) reads one field out of a date or timestamp and returns it as a number. Common parts are YEAR, MONTH, DAY, HOUR, MINUTE and SECOND, plus a few calendar helpers: QUARTER (1 to 4), DOW (day of week), DOY (day of year) and EPOCH (seconds since 1970-01-01 in PostgreSQL). The part is written as a bare keyword, so it is EXTRACT(YEAR FROM order_date), not EXTRACT('YEAR' FROM order_date).

It is part of the SQL standard, and both MySQL and PostgreSQL implement it directly. MySQL also ships shorthand functions such as YEAR(), MONTH() and DAY() that do the same job for a single field. Use whichever reads more clearly; the shorthands are handy but not portable.

SQL Server has no EXTRACT function. It uses DATEPART(part, date) to return the component as an integer, and DATENAME(part, date) to return it as text, for example the month name. If you are porting queries between engines, this is the line that usually needs to change. To reduce a date to a boundary rather than read a field, see DATE_FORMAT() and DATE_TRUNC().

Examples

Get the year from a date

-- ANSI standard, works in MySQL and PostgreSQL
SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders
WHERE id = 1;

order_year
----------
      2026

Group sales by month using EXTRACT

SELECT EXTRACT(YEAR FROM order_date)  AS yr,
       EXTRACT(MONTH FROM order_date) AS mon,
       SUM(total) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
         EXTRACT(MONTH FROM order_date)
ORDER BY yr, mon;

yr   | mon | revenue
-----+-----+--------
2026 |   1 |  41250
2026 |   2 |  38700
2026 |   3 |  52990

Day of week and day of year

-- PostgreSQL: DOW is 0=Sunday .. 6=Saturday
SELECT EXTRACT(DOW FROM order_date) AS weekday,
       EXTRACT(DOY FROM order_date) AS day_of_year
FROM orders
WHERE id = 1;

weekday | day_of_year
--------+------------
      5 |          64

Same query, three engines

-- MySQL and PostgreSQL (ANSI EXTRACT)
SELECT EXTRACT(YEAR FROM order_date) AS yr FROM orders;

-- MySQL shorthand
SELECT YEAR(order_date) AS yr FROM orders;

-- SQL Server has no EXTRACT: use DATEPART
SELECT DATEPART(YEAR, order_date) AS yr FROM orders;

yr
----
2026

Quarter and epoch seconds

-- QUARTER returns 1..4; EPOCH (PostgreSQL) is
-- seconds since 1970-01-01 00:00:00 UTC
SELECT EXTRACT(QUARTER FROM order_date) AS q,
       EXTRACT(EPOCH FROM created_at)   AS unix_seconds
FROM orders
WHERE id = 1;

q | unix_seconds
--+-------------
1 |  1772668800

Common mistakes

-- Wrapping the indexed column in EXTRACT means the
-- index on order_date cannot be used: full scan
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- Compare the raw column to a range instead, so the
-- index on order_date stays usable
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

-- Assuming DOW numbering is the same everywhere.
-- PostgreSQL: 0=Sunday. MySQL DAYOFWEEK: 1=Sunday.
-- SQL Server DATEPART(WEEKDAY): depends on DATEFIRST.
SELECT EXTRACT(DOW FROM order_date) FROM orders;

-- Pin down the engine's numbering before you
-- compare it. In PostgreSQL, Friday is 5:
SELECT CASE EXTRACT(DOW FROM order_date)
         WHEN 0 THEN 'Sun' WHEN 6 THEN 'Sat'
         ELSE 'weekday' END AS kind
FROM orders;

-- SQL Server does not have EXTRACT: this errors
SELECT EXTRACT(MONTH FROM order_date)
FROM orders;

-- Use DATEPART for a number, DATENAME for the name
SELECT DATEPART(MONTH, order_date)  AS mon_num,
       DATENAME(MONTH, order_date)  AS mon_name
FROM orders;

Performance

The cost of EXTRACT() itself is tiny; the trap is where you put it. Calling EXTRACT(YEAR FROM order_date) = 2026 in a WHERE clause forces the engine to compute the year for every row, which makes the predicate non-sargable and prevents any index on order_date from being used. Prefer a half-open range such as order_date >= '2026-01-01' AND order_date < '2027-01-01'.

In the SELECT list, GROUP BY or ORDER BY, EXTRACT() is cheap and perfectly fine, because it runs once per row that already survived filtering. Grouping sales by EXTRACT(MONTH FROM order_date) does not hurt an index, since no filtering depends on it.

If you filter on a derived date part very often, some engines let you build an index on the expression itself (an expression or function-based index) or persist it as a generated column, which restores index use without rewriting every query.

Interview questions

-- MySQL / PostgreSQL
SELECT EXTRACT(YEAR FROM order_date) FROM orders;
-- SQL Server
SELECT DATEPART(YEAR, order_date) FROM orders;