Dates, Times & CASE

PostgreSQL has rich date and time support. current_date is today, now() is the current moment, and you can add or subtract intervals such as interval '30 days'. Subtracting two dates gives the number of days between them; age() gives years, months and days.

extract(year FROM d) (or date_part) pulls out one part of a date; date_trunc('month', ts) rounds a timestamp down to the start of its month — perfect for grouping by month. to_char formats dates for display, e.g. to_char(d, 'DD Mon YYYY').

CASE adds if/else logic inside a query: CASE WHEN condition THEN value ... ELSE value END. Use it to label rows (age groups, fee status) or to count conditionally inside aggregates.

dates.sqlSQL

-- Date arithmetic with fixed dates (so results are predictable)
SELECT date '2026-03-14' + 30               AS plus_30_days,
       date '2026-12-01' - date '2026-03-14' AS days_between,
       date '2026-03-14' + interval '2 months' AS plus_2_months,
       age(date '2026-03-14', date '2008-03-14') AS age_on_date;

-- Parts of a date, truncation and formatting
SELECT full_name,
       birth_date,
       extract(year FROM birth_date)::int        AS birth_year,
       date_trunc('month', birth_date)::date     AS birth_month,
       to_char(birth_date, 'DD Mon YYYY')         AS pretty
FROM students
WHERE birth_date IS NOT NULL
ORDER BY birth_date;

-- CASE: label each student
SELECT full_name,
       fee_balance,
       CASE
           WHEN fee_balance = 0        THEN 'cleared'
           WHEN fee_balance <= 100000  THEN 'small balance'
           ELSE 'large balance'
       END AS fee_status,
       CASE WHEN form >= 3 THEN 'senior' ELSE 'junior' END AS section
FROM students
ORDER BY fee_balance DESC;

-- CASE inside an aggregate: count per category in one row
SELECT count(*) FILTER (WHERE gender = 'F')                 AS girls,
       count(*) FILTER (WHERE gender = 'M')                 AS boys,
       sum(CASE WHEN fee_balance > 0 THEN 1 ELSE 0 END)     AS owing
FROM students;

-- A calendar of school days with generate_series
SELECT d::date AS day, to_char(d, 'Dy') AS weekday
FROM generate_series(date '2026-03-02', date '2026-03-08', interval '1 day') AS d
WHERE extract(isodow FROM d) < 6;

Runs in your browser · PostgreSQL

Key points

Add or subtract intervals; subtracting dates gives days; age() gives a readable span.
extract pulls out parts; date_trunc rounds down for grouping; to_char formats.
CASE WHEN ... THEN ... ELSE ... END labels rows and powers conditional counts.

Exercise

Show each student's name and age in whole years on 1 January 2027 (date_part('year', age(date '2027-01-01', birth_date))). Then label every student 'Form 1-2' or 'Form 3-4' with CASE and count how many students are in each label.

Show solution

Try the exercise yourself first — then compare your approach with this one.

age(date '2027-01-01', birth_date) gives the span up to that day, and date_part('year', ...) keeps the whole years. The second query computes the label in a subquery, then groups by it.

dates-solution.sqlSQL

SELECT full_name,
       date_part('year', age(date '2027-01-01', birth_date))::int AS age_on_1_jan_2027
FROM students
WHERE birth_date IS NOT NULL
ORDER BY birth_date;

SELECT band, count(*) AS students
FROM (
    SELECT CASE WHEN form <= 2 THEN 'Form 1-2' ELSE 'Form 3-4' END AS band
    FROM students
) AS labelled
GROUP BY band
ORDER BY band;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding