EXTRACT, DATE_TRUNC, AGE
Pull out parts of a date with EXTRACT, snap timestamps to a boundary with DATE_TRUNC, and compute human-readable diffs with AGE.
EXTRACT: Pull Out a Date Part
EXTRACT(part FROM ts) returns a single numeric component:
SELECT EXTRACT(YEAR FROM created_at) AS yr,
EXTRACT(MONTH FROM created_at) AS mo,
EXTRACT(DAY FROM created_at) AS d,
EXTRACT(DOW FROM created_at) AS day_of_week
FROM orders;Common EXTRACT Parts
Useful parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (0=Sun), DOY (1–366), WEEK, QUARTER, EPOCH.
SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) AS seconds_ago
FROM events;