Articles
SQL Date Functions: DATE_TRUNC, EXTRACT, Interval Math, and Time Zone Traps
Use PostgreSQL date functions safely across time zones, month buckets, ISO calendars, intervals, half-open windows, and dense rolling periods.
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
| event_id | user_id | event_ts | event_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 04:30:00+00 | purchase | 20.00 |
| 2 | 1 | 2025-03-01 05:30:00+00 | purchase | 30.00 |
| 3 | 2 | 2025-03-15 14:00:00+00 | click | NULL |
| 4 | 2 | 2025-04-01 03:30:00+00 | purchase | 40.00 |
| 5 | 3 | 2025-04-01 04:30:00+00 | purchase | 50.00 |
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
| event_id | utc_date | new_york_date |
|---|---|---|
| 1 | 2025-03-01 | 2025-02-28 |
| 2 | 2025-03-01 | 2025-03-01 |
| 3 | 2025-03-15 | 2025-03-15 |
| 4 | 2025-04-01 | 2025-03-31 |
| 5 | 2025-04-01 | 2025-04-01 |
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
| event_id | user_id | event_ts | event_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 04:30:00+00 | purchase | 20.00 |
| 2 | 1 | 2025-03-01 05:30:00+00 | purchase | 30.00 |
| 3 | 2 | 2025-03-15 14:00:00+00 | click | NULL |
| 4 | 2 | 2025-04-01 03:30:00+00 | purchase | 40.00 |
| 5 | 3 | 2025-04-01 04:30:00+00 | purchase | 50.00 |
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
| month_start | event_count | purchase_amount |
|---|---|---|
| 2025-02-01 | 1 | 20.00 |
| 2025-03-01 | 3 | 70.00 |
| 2025-04-01 | 1 | 50.00 |
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
| calendar_date |
|---|
| 2024-02-29 |
| 2025-01-01 |
| 2025-12-31 |
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
| calendar_date | iso_day_of_week | iso_year | week_start |
|---|---|---|---|
| 2024-02-29 | 4 | 2024 | 2024-02-26 |
| 2025-01-01 | 3 | 2025 | 2024-12-30 |
| 2025-12-31 | 3 | 2026 | 2025-12-29 |
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
| checkout_id | copy_id | checkout_date | return_date |
|---|---|---|---|
| 1 | 1 | 2025-01-05 | 2025-01-12 |
| 2 | 1 | 2025-02-01 | NULL |
| 3 | 2 | 2025-01-20 | 2025-02-03 |
| 4 | 3 | 2025-03-10 | 2025-03-11 |
SELECT
checkout_id,
checkout_date,
return_date,
return_date - checkout_date AS days_borrowed
FROM checkouts
ORDER BY checkout_id;
Output
| checkout_id | checkout_date | return_date | days_borrowed |
|---|---|---|---|
| 1 | 2025-01-05 | 2025-01-12 | 7 |
| 2 | 2025-02-01 | NULL | NULL |
| 3 | 2025-01-20 | 2025-02-03 | 14 |
| 4 | 2025-03-10 | 2025-03-11 | 1 |
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
| anchor_date |
|---|
| 2024-02-29 |
| 2025-01-31 |
| 2025-03-31 |
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
| anchor_date | plus_one_month | plus_thirty_days |
|---|---|---|
| 2024-02-29 | 2024-03-29 | 2024-03-30 |
| 2025-01-31 | 2025-02-28 | 2025-03-02 |
| 2025-03-31 | 2025-04-30 | 2025-04-30 |
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
| time_zone | start_date | end_date |
|---|---|---|
| America/New_York | 2025-03-01 | 2025-04-01 |
Input: events
| event_id | user_id | event_ts | event_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 04:30:00+00 | purchase | 20.00 |
| 2 | 1 | 2025-03-01 05:30:00+00 | purchase | 30.00 |
| 3 | 2 | 2025-03-15 14:00:00+00 | click | NULL |
| 4 | 2 | 2025-04-01 03:30:00+00 | purchase | 40.00 |
| 5 | 3 | 2025-04-01 04:30:00+00 | purchase | 50.00 |
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
| event_id | local_event_ts |
|---|---|
| 2 | 2025-03-01 00:30:00 |
| 3 | 2025-03-15 10:00:00 |
| 4 | 2025-03-31 23:30:00 |
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
| start_week | end_week |
|---|---|
| 2025-01-06 | 2025-02-03 |
Input: transactions
| transaction_id | user_id | transaction_ts | amount |
|---|---|---|---|
| 1 | 1 | 2025-01-06 10:00:00 | 20.00 |
| 2 | 1 | 2025-01-09 11:00:00 | 30.00 |
| 3 | 1 | 2025-01-14 09:00:00 | 50.00 |
| 4 | 1 | 2025-01-27 09:00:00 | 40.00 |
| 5 | 1 | 2025-02-03 09:00:00 | 10.00 |
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
| week_start | weekly_revenue | rolling_4_week_revenue |
|---|---|---|
| 2025-01-06 | 50.00 | 50.00 |
| 2025-01-13 | 50.00 | 100.00 |
| 2025-01-20 | 0.00 | 100.00 |
| 2025-01-27 | 40.00 | 140.00 |
| 2025-02-03 | 10.00 | 100.00 |
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.