Date Functions

Dates look simple until you actually need to work with them. Extracting a year is easy enough - but what about grouping all orders from March into one bucket, or finding every user who signed up in the last 30 days, or computing how many days someone has been employed? SQL's date functions handle all of this without leaving the query.

Extracting components with EXTRACT

EXTRACT pulls one piece out of a date or timestamp. You tell it what unit you want, and it gives you back a number:

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 the precision you choose. This is how you group by month or quarter - you truncate every timestamp to the same anchor point so GROUP BY can collapse them:

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. Every timestamp in April becomes 2024-04-01 00:00:00. GROUP BY sees those as equal, so you get one row per calendar month.

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

Date arithmetic

You can add or subtract intervals directly from dates and timestamps. The INTERVAL keyword takes a quoted string - SQL does the calendar math:

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;

The type of result depends on the types you subtract. Timestamp minus timestamp gives an interval. Date minus date gives a plain integer - the number of days between them:

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

If you need a plain number from a timestamp difference, wrap the interval in EXTRACT:

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

NOW, CURRENT_DATE, CURRENT_TIMESTAMP

NOW() and CURRENT_TIMESTAMP are effectively interchangeable - both return the current date and time with timezone. CURRENT_DATE gives you only the date, no time. One syntax note: NOW() is a function call with parentheses, but CURRENT_DATE and CURRENT_TIMESTAMP are SQL keywords - no parentheses.

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. If you move to SQL Server, look for DATETRUNC or FORMAT. MySQL uses DATE_FORMAT and DATE_ADD. The logic is always the same - truncate to a precision, extract a component, add an interval. Only the function names differ. When you switch databases, date functions are the first place to check.