PostgreSQL 18 reference · Date and Time Functions
PostgreSQL EXTRACT() Function
Get one field from a date, time or interval, such as the year, month, day of week or epoch seconds.
PostgreSQL 18
Syntax
EXTRACT(field FROM source) | date_part('field', source)
Parameters
| Parameter | Description |
|---|---|
field |
year, quarter, month, week, day, dow (0 = Sunday), isodow, doy, hour, minute, epoch… |
source |
A date, timestamp, timestamptz or interval. |
Return value
A numeric value.
Examples
SELECT EXTRACT(year FROM DATE '2026-03-09') AS y,
EXTRACT(dow FROM DATE '2026-03-09') AS weekday,
EXTRACT(epoch FROM TIMESTAMPTZ '2026-01-01 00:00+00') AS unix_seconds;
Output
y | weekday | unix_seconds ------+---------+------------------- 2026 | 1 | 1767225600.000000 (1 row)
Run this on your computer — see “Get Started” for setup.
SELECT EXTRACT(month FROM order_date) AS month, count(*) AS orders
FROM orders
GROUP BY month
ORDER BY month
LIMIT 3;
Output
month | orders
-------+--------
1 | 2
2 | 3
3 | 2
(3 rows)Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 14 | Returns numeric instead of double precision. |
| PostgreSQL 18 | Supports the WEEK field for intervals. |
In other languages
STRFTIME()· SQLite
Related
DATE_TRUNC()· Truncate a timestamp or interval to a unit such as 'month', 'week' or 'hour', for grouping by period.TO_CHAR()· Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.AGE()· Subtract dates and return a 'symbolic' interval in years, months and days. With one argument it counts from today.- SQL Date Functions · tutorial