SQL Date Functions: DATE_TRUNC, EXTRACT, Interval Math, and Time Zone Traps

Date logic starts with types and boundaries, not function names. A date has calendar meaning, a timestamp is a wall-clock value without a zone, and a timestamptz is an instant displayed through a chosen zone.

For every analysis, pin the reporting zone, interval endpoints, and output grain. Then choose DATE_TRUNC, EXTRACT, interval arithmetic, or a date spine to implement that contract.

Convert instants into the reporting zone first

These event timestamps are stored as timestamptz instants. Converting each instant to a New York wall-clock timestamp before taking the date moves event 1 into February 28 and event 4 into March 31.

Input: events

SELECT
  event_id,
  (event_ts AT TIME ZONE 'UTC')::date AS utc_date,
  (event_ts AT TIME ZONE 'America/New_York')::date AS new_york_date
FROM events
ORDER BY event_id;

Output

Fixed offsets are not substitutes for named zones when daylight-saving rules matter. A named zone resolves the applicable offset for each instant.

DATE_TRUNC defines the reporting grain

DATE_TRUNC returns the beginning of a requested unit. Convert to the reporting zone first, then truncate, so events near a UTC boundary land in the intended local month.

Input: events

SELECT
  DATE_TRUNC(
    'month',
    event_ts AT TIME ZONE 'America/New_York'
  )::date AS month_start,
  COUNT(*) AS event_count,
  SUM(amount) AS purchase_amount
FROM events
GROUP BY month_start
ORDER BY month_start;

Output

Use the bucket value as the grouping key and format labels only at the presentation edge. Date or timestamp keys preserve chronological sorting. See SQL GROUP BY for aggregate and NULL behavior.

EXTRACT reads calendar components

EXTRACT returns one component; it does not create a full period key. ISO day-of-week uses Monday as 1 and Sunday as 7. ISO year can differ from calendar year near New Year, as December 31, 2025 demonstrates.

Input: calendar_days

SELECT
  calendar_date,
  EXTRACT(ISODOW FROM calendar_date)::integer AS iso_day_of_week,
  EXTRACT(ISOYEAR FROM calendar_date)::integer AS iso_year,
  DATE_TRUNC('week', calendar_date)::date AS week_start
FROM calendar_days
ORDER BY calendar_date;

Output

Interval arithmetic needs explicit units and NULL policy

Subtracting one PostgreSQL date from another returns an integer day count. An open checkout has no return date, so its duration remains NULL rather than being treated as zero or as of today.

Input: checkouts

SELECT
  checkout_id,
  checkout_date,
  return_date,
  return_date - checkout_date AS days_borrowed
FROM checkouts
ORDER BY checkout_id;

Output

Calendar months are not fixed-day durations. Adding one month can clamp the day to the end of the target month, while adding 30 days follows elapsed calendar days.

Input: date_anchors

SELECT
  anchor_date,
  (anchor_date + INTERVAL '1 month')::date AS plus_one_month,
  (anchor_date + INTERVAL '30 days')::date AS plus_thirty_days
FROM date_anchors
ORDER BY anchor_date;

Output

Use half-open windows and dense period spines

A local March reporting window begins at New York midnight on March 1 and ends just before New York midnight on April 1. Converting those two boundaries to instants gives a complete, non-overlapping filter for timestamptz events.

Input: report_window

Input: events

SELECT
  e.event_id,
  e.event_ts AT TIME ZONE r.time_zone AS local_event_ts
FROM events AS e
CROSS JOIN report_window AS r
WHERE e.event_ts >= r.start_date::timestamp AT TIME ZONE r.time_zone
  AND e.event_ts <  r.end_date::timestamp AT TIME ZONE r.time_zone
ORDER BY e.event_ts, e.event_id;

Output

The same half-open idea applies to dates and timestamps without zones; SQL BETWEEN compares the boundary choices directly.

Window frames count rows unless the ordering frame says otherwise. Build a dense weekly spine first so a four-row frame represents four consecutive report weeks, including the quiet week of January 20.

Input: report_weeks

Input: transactions

WITH spine AS (
  SELECT
    GENERATE_SERIES(
      start_week,
      end_week,
      INTERVAL '7 days'
    )::date AS week_start
  FROM report_weeks
),
weekly AS (
  SELECT
    DATE_TRUNC('week', transaction_ts)::date AS week_start,
    SUM(amount) AS weekly_revenue
  FROM transactions
  GROUP BY week_start
),
dense AS (
  SELECT
    s.week_start,
    COALESCE(w.weekly_revenue, 0)::numeric(12,2) AS weekly_revenue
  FROM spine AS s
  LEFT JOIN weekly AS w
    ON w.week_start = s.week_start
)
SELECT
  week_start,
  weekly_revenue,
  SUM(weekly_revenue) OVER (
    ORDER BY week_start
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS rolling_4_week_revenue
FROM dense
ORDER BY week_start;

Output

The SQL CTE guide shows the same spine idea with recursion. For run detection rather than fixed windows, use SQL gaps and islands; window-frame mechanics are covered in the window-functions guide.

FAQ

What does DATE_TRUNC return?

It returns the input value reduced to the start of the requested unit, with type behavior determined by the input. For reporting on timestamptz, decide the reporting zone before forming local calendar buckets.

What is the difference between EXTRACT(DOW) and EXTRACT(ISODOW)?

In PostgreSQL, DOW numbers Sunday as 0 through Saturday as 6. ISODOW numbers Monday as 1 through Sunday as 7. Match the function to the calendar definition.

Why use a half-open date or timestamp range?

An inclusive start and exclusive next boundary covers every instant in the intended period without inventing a final timestamp. Adjacent periods can share the same boundary without overlap.

Is one month the same as 30 days?

No. A calendar month varies in length and month-end addition can clamp the day. Use the unit named by the business rule.

Why build a date spine?

Source tables usually omit periods with no activity. A spine supplies those report keys so zeroes become explicit and row-based windows operate over consecutive periods.