extract()

Get timestamp subfield; see Section 9.9.1

Signature

extract ( field from timestamp ) → numeric
extract ( field from interval ) → numeric

Examples

SELECT extract(hour from timestamp '2001-02-16 20:38:40');

SELECT extract(month from interval '2 years 3 months');

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	extract
MySQL	EXTRACT(YEAR FROM x)
SQL Server	DATEPART(year, x)
Oracle	EXTRACT(YEAR FROM x)
SQLite	CAST(strftime('%Y', x) AS INTEGER)

SQLite has no date type at all — dates are text, numbers or Julian days, and every date function is really a string function. That is the single biggest surprise when moving a schema to SQLite.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.