Date Functions

Dates and timestamps need more than simple comparisons. You often need to extract a year, round a timestamp to the nearest month, or compute the gap between two dates. SQL's date functions handle all of this directly in the query.

Extracting components with EXTRACT

EXTRACT pulls a single unit out of a date or timestamp:

SELECT EXTRACT(YEAR  FROM hire_date) AS hire_year  FROM employees;
SELECT EXTRACT(MONTH FROM created_at) AS month      FROM orders;
SELECT EXTRACT(DOW   FROM created_at) AS weekday    FROM orders; -- 0 = Sunday
SELECT EXTRACT(HOUR  FROM logged_at)  AS hour       FROM events;

Common units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of year), QUARTER.

Truncating with DATE_TRUNC

DATE_TRUNC rounds a timestamp down to a given precision. This is the go-to function for grouping by time period:

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

Every timestamp in March 2024 becomes 2024-03-01 00:00:00. The result is one row per calendar month.

Valid precision values: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'.

Date arithmetic

You can add or subtract intervals from any date or timestamp:

SELECT hire_date + INTERVAL '90 days'  AS probation_end FROM employees;
SELECT NOW()     - INTERVAL '30 days'  AS thirty_days_ago;
SELECT NOW()     + INTERVAL '1 year'   AS one_year_from_now;

Subtracting two timestamps returns an interval. Subtracting two dates returns an integer number of days:

SELECT (NOW() - hire_date) AS tenure    FROM employees; -- interval
SELECT (end_date - start_date) AS days  FROM projects;  -- integer

To convert an interval to a plain number, use EXTRACT:

SELECT EXTRACT(DAY FROM (NOW() - hire_date)) AS days_employed
FROM employees;

NOW, CURRENT_DATE, CURRENT_TIMESTAMP

NOW() and CURRENT_TIMESTAMP both return the current date and time with timezone. CURRENT_DATE returns only the date (no time component). NOW() is a function call; CURRENT_DATE and CURRENT_TIMESTAMP are keywords — no parentheses needed.

SELECT NOW();              -- 2024-03-15 14:32:00+00
SELECT CURRENT_DATE;       -- 2024-03-15
SELECT CURRENT_TIMESTAMP;  -- 2024-03-15 14:32:00+00

Portability note

DATE_TRUNC is PostgreSQL-specific. SQL Server uses DATETRUNC or FORMAT; MySQL uses DATE_FORMAT and DATE_ADD. The concepts are universal — only the syntax changes when you switch databases.