41 Date Functions and COALESCE

PostgreSQL has rich date/time arithmetic. date_trunc rounds to a precision; extract pulls a single field; interval lets you add and subtract durations; date_part, age, to_char, to_date cover the rest. And COALESCE — not date-specific but worth pairing with date logic — is how you supply defaults for possibly-NULL values.

What you’ll learn

• current_date, current_timestamp, now(), clock_timestamp()
• date_trunc(unit, ts): truncate to year, month, day, hour, etc.
• extract(field FROM ts): pull year, month, day-of-week, …
• interval arithmetic and formatting
• COALESCE for NULL fallbacks

Example

-- "Now" functions
SELECT current_date AS today, current_timestamp AS now_tz, clock_timestamp() AS wall_clock;

-- date_trunc: truncate to a precision
SELECT date_trunc('month', current_timestamp) AS month_start,
       date_trunc('day',   current_timestamp) AS day_start,
       date_trunc('hour',  current_timestamp) AS hour_start;

-- Interval arithmetic
SELECT current_date + interval '7 days'    AS in_a_week,
       current_date - interval '1 month'   AS a_month_ago,
       current_timestamp + interval '90 minutes' AS in_90m;

-- Difference between two timestamps as an interval
SELECT age(timestamptz '2025-01-01', timestamptz '2024-03-15') AS gap;

-- extract: pull a field
SELECT extract(year      FROM current_date) AS year,
       extract(month     FROM current_date) AS month,
       extract(dow       FROM current_date) AS day_of_week,  -- 0=Sunday
       extract(isodow    FROM current_date) AS iso_dow,      -- 1=Monday
       extract(week      FROM current_date) AS iso_week;

-- Formatting and parsing
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS TZ') AS formatted,
       to_date('2024-01-15', 'YYYY-MM-DD')                    AS parsed;

-- COALESCE: first non-NULL
SELECT COALESCE(NULL, NULL, 'default') AS c1,
       COALESCE(NULL, 42)              AS c2,
       COALESCE(due_at, current_timestamp + interval '7 days') AS due_or_default
FROM tasks_example
ORDER BY id;

date_trunc('month', ts) is the standard way to “bin by month” in analytics. The result is a timestamp (or timestamptz) at midnight on the first of that month. Use extract(isodow FROM ts) to group by weekday, date_trunc('week', ts) for calendar weeks, or date_bin (PostgreSQL 14+) for fixed-width buckets.

extract returns a numeric (or double precision in older versions). The dow field uses Sunday=0; isodow uses Monday=1 (matching ISO 8601). When in doubt, isodow matches what most “Monday is the start of the week” assumptions expect.

age(end, start) returns an interval normalized into years/months/days, which is useful for displays (“3 years, 2 months”). For raw differences, subtract two timestamptz values to get an interval in days/hours/seconds.

COALESCE walks its arguments left-to-right and returns the first non-NULL. It is the canonical “default value” tool: COALESCE(user.display_name, user.email, 'Anonymous').

Run the example

To run:

$ psql -f source/date-functions-and-coalesce.sql postgres
    today    |          now_tz             |       wall_clock
-------------+-----------------------------+-----------------------------
 2026-xx-xx  | 2026-xx-xx xx:xx:xx.xxxxxx+00 | 2026-xx-xx xx:xx:xx.xxxxxx+00
(1 row)
 ...

Common pitfalls

• now() returns the transaction start time. Inside a long transaction, every call returns the same value. Use clock_timestamp() for true wall-clock.
• Mixing timestamp and timestamptz silently uses the session’s time zone. Be explicit: at time zone 'UTC' for cast/display, and store as timestamptz whenever possible.
• date_trunc returns a value of the same type — date_trunc('day', timestamptz) returns a timestamptz, not a date. Cast if you need date.
• extract(epoch FROM ts) returns numeric seconds since the Unix epoch in PostgreSQL 17; date_part('epoch', ts) returns double precision. Useful when interoperating with code that uses Unix time.
• COALESCE short-circuits, so side-effecting expressions in later arguments are not evaluated when an earlier one is non-NULL.

Downloads

SQL file: date-functions-and-coalesce.sql · All lesson files · Setup guide