PostgreSQL dates, times and intervals

Key types: date (a day), time, timestamp (date and time, no zone), timestamptz (an absolute moment, shown in your session time zone; the usual choice for “when did it happen”), and interval (a duration like '3 days').

Arithmetic just works: date + 7 adds days, timestamp + interval '2 hours' adds time, and subtracting two dates gives the number of days between them.

Handy functions: now() / current_date, date_trunc('month', ts) (round down to the start of a unit), extract(year FROM d), age(a, b), to_char(d, 'format') for display, make_date, and generate_series for building calendars.

Syntax

date_trunc('month', order_date)
extract(dow FROM order_date)      -- 0 = Sunday
to_char(order_date, 'DD Mon YYYY')
order_date + interval '30 days'

Examples

Edit the code and run it. Output is printed the way psql prints it.

12.1Today and date arithmetic

SELECT current_date                         AS today,
       current_date + 30                    AS in_30_days,
       now() - interval '90 minutes'        AS ninety_min_ago,
       date '2024-12-25' - date '2024-10-01' AS days_between;

Adding an integer to a date adds days. date '...' is a typed literal. Subtracting dates returns an integer number of days.

12.2Orders per month

SELECT date_trunc('month', order_date)::date AS month,
       count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

date_trunc('month', ...) turns 2024-03-15 into 2024-03-01, so all orders of a month share one value and can be grouped. GROUP BY 1 means “group by the first select column”.

12.3Extracting parts and formatting

SELECT id, order_date,
       extract(year FROM order_date)    AS yr,
       extract(quarter FROM order_date) AS qtr,
       to_char(order_date, 'Dy, DD Mon YYYY') AS pretty,
       to_char(order_date, 'FMDay')     AS weekday
FROM orders
LIMIT 6;

extract returns numbers. to_char returns text formatted with patterns: Dy short day name, Mon short month, FM removes padding.

12.4Tenure with age()

SELECT first_name, hire_date,
       age(date '2024-12-31', hire_date) AS tenure,
       extract(year FROM age(date '2024-12-31', hire_date)) AS full_years
FROM employees
ORDER BY hire_date;

age(a, b) returns an interval in years, months and days. A fixed end date keeps the example’s output stable.

12.5A calendar with generate_series

SELECT m::date AS month,
       count(o.id) AS orders
FROM generate_series('2024-01-01'::date, '2024-12-01'::date, interval '1 month') AS m
LEFT JOIN orders o ON date_trunc('month', o.order_date) = m
GROUP BY m
ORDER BY m;

generate_series produces one row per month, including November and December when nothing was ordered. Left-joining real data onto a generated calendar fills gaps with zero, which is what reports and charts need.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 12.1

Count orders per quarter of 2024: columns quarter (1–4) and orders, ordered by quarter.

Hint

extract(quarter FROM order_date)

Solution

SELECT extract(quarter FROM order_date) AS quarter, count(*) AS orders FROM orders GROUP BY 1 ORDER BY 1;

Exercise 12.2

List customers (name, signup_date) who signed up in the second half of 2023 (July to December).

Hint

signup_date >= '2023-07-01' AND signup_date < '2024-01-01'

Solution

SELECT name, signup_date FROM customers WHERE signup_date >= '2023-07-01' AND signup_date < '2024-01-01';