date-time
EXTRACT / DATE_PART
Pulls a numeric component (year, month, day, hour, minute, dow, epoch) out of a date or timestamp. EXTRACT is SQL-standard and works in Postgres and MySQL; DATE_PART is a Postgres synonym.
EXTRACT(field FROM timestamp) | DATE_PART('field', timestamp)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| YEAR / MONTH / DAY | Common calendar components |
| DOW / ISODOW | Day of week — DOW is 0=Sunday, ISODOW 1=Monday (Postgres) |
| EPOCH | Unix seconds since 1970 (Postgres); MySQL uses UNIX_TIMESTAMP() |
| QUARTER | Calendar quarter number 1-4 |
Examples
SELECT EXTRACT(YEAR FROM NOW());Current year as a number
SELECT EXTRACT(DOW FROM order_date), COUNT(*) FROM orders GROUP BY 1;Order counts per day of week
SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) FROM users;Postgres: account age in seconds
SELECT DATE_PART('hour', ts) FROM logs;Postgres shorthand equivalent to EXTRACT(HOUR FROM ts)
Dialect notes / Gotcha
MySQL supports EXTRACT but not DATE_PART. Postgres 14+ returns numeric (older versions returned double precision). Cast with ::int if you need an integer bucket.
Related functions
DATE_TRUNC
Rounds a timestamp down to the beginning of the specified unit (year, month, week, day, hour, etc.). Native to Postgres; MySQL uses DATE_FORMAT or manual arithmetic.
NOW / CURRENT_TIMESTAMP
Returns the current transaction (or statement) timestamp. Both spellings work in Postgres and MySQL; SQLite prefers CURRENT_TIMESTAMP or datetime('now').
INTERVAL arithmetic
Adds or subtracts a duration to a date/timestamp. Postgres uses the SQL-standard INTERVAL literal directly; MySQL wraps it in DATE_ADD/DATE_SUB; SQLite uses date modifiers.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet