Intermediate9 min read PostgreSQL

Date & Time Functions

Use EXTRACT, DATE_TRUNC, AGE, and interval arithmetic to group applications by month, calculate how long a student has been in their program, and detect late applicants.

EXTRACT
DATE_TRUNC
AGE
NOW
INTERVAL
date

Use this when you need to:

Bucket events by week, month, or fiscal quarter for a time-series chart
Calculate "days since last login" or "hours since last alert" with date arithmetic
Build a time-windowed alert — anything in the last 24 hours, errors in the last hour, signups this week
Align messy timestamps to a business calendar — fiscal year, term codes, weekday vs weekend

PostgreSQL has first-class date and time support. The four functions used most in institutional analytics are NOW() / CURRENT_DATE (get the current moment), EXTRACT (pull one field out of a date), DATE_TRUNC (snap a date to a period boundary), and AGE (compute elapsed time between two dates).

NOW() and CURRENT_DATE

SELECT CURRENT_DATE;             -- e.g. 2026-09-23  (date only)
SELECT NOW();                    -- e.g. 2026-09-23 14:32:00.123+00 (timestamp with time zone)
SELECT CURRENT_TIMESTAMP;        -- same as NOW()

EXTRACT — Pull Out One Field

EXTRACT returns a numeric value for the requested field. Supported fields include: year, month, day, hour, minute, second, quarter, week, dow (day of week, 0=Sunday), doy (day of year).

-- Count applications by the year and month they arrived
SELECT
    EXTRACT(YEAR  FROM appl_date)::INT AS appl_year,
    EXTRACT(MONTH FROM appl_date)::INT AS appl_month,
    COUNT(*)                            AS applications
FROM ods_applications
GROUP BY appl_year, appl_month
ORDER BY appl_year, appl_month;

DATE_TRUNC — Snap to Period Start

DATE_TRUNC snaps a timestamp down to the start of a period — all less significant fields are zeroed out. Available precisions: millennium, century, decade, year, quarter, month, week, day, hour, minute, second.

-- Group applications by month
SELECT
    DATE_TRUNC('month', appl_date) AS month_start,
    COUNT(*)                        AS applications
FROM ods_applications
GROUP BY DATE_TRUNC('month', appl_date)
ORDER BY month_start;

-- Coarser grouping: snap each date down to the start of its quarter
SELECT
    DATE_TRUNC('quarter', appl_date) AS quarter_start,
    COUNT(DISTINCT appl_applicant)    AS unique_applicants
FROM ods_applications
GROUP BY quarter_start
ORDER BY quarter_start;

AGE — Human-Readable Elapsed Time

-- How long has each active student been in their program?
SELECT
    p.first_name,
    p.last_name,
    sal.stpr_start_date,
    AGE(CURRENT_DATE, sal.stpr_start_date) AS in_program_for
FROM ods_stu_acad_levels sal
LEFT JOIN ods_person p ON p.id = sal.students_id
WHERE sal.stpr_status = 'A'          -- 'G' = graduated
ORDER BY sal.stpr_start_date, p.last_name;
-- in_program_for is an interval, shown as '6 years 1 mon 8 days'.
-- It grows every day you run the query, because CURRENT_DATE moves.

-- Active students more than 4 years into their program without graduating
SELECT p.first_name, p.last_name, sal.stpr_start_date
FROM ods_stu_acad_levels sal
LEFT JOIN ods_person p ON p.id = sal.students_id
WHERE sal.stpr_status = 'A'
  AND sal.stpr_start_date < CURRENT_DATE - INTERVAL '4 years';

Interval Arithmetic

-- Find late applicants: applied less than 120 days before their start term began
SELECT
    a.applications_id,
    t.term_desc,
    t.term_start_date                    AS term_start,
    a.appl_date                          AS applied_on,
    t.term_start_date - a.appl_date      AS days_before_start   -- date - date = whole days
FROM ods_applications a
LEFT JOIN ods_terms t ON t.terms_id = a.appl_start_term
WHERE a.appl_date > t.term_start_date - INTERVAL '120 days'     -- date - interval = timestamp
ORDER BY days_before_start;

Practice challenge

Write a query that counts new program starts per academic year, where an academic year starts on July 1. Return academic_year (integer, e.g. 2024 for the 2024-25 year) and new_students. Use ods_stu_acad_levels and its stpr_start_date column.

Solution

SELECT
    CASE
        WHEN EXTRACT(MONTH FROM stpr_start_date) >= 7
        THEN EXTRACT(YEAR FROM stpr_start_date)::INT
        ELSE EXTRACT(YEAR FROM stpr_start_date)::INT - 1
    END AS academic_year,
    COUNT(*) AS new_students
FROM ods_stu_acad_levels
GROUP BY academic_year
ORDER BY academic_year;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons