SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.

Quick Summary
Two decisions decide almost every date bug you will ever debug. The first is the column type: timestamptz stores an absolute instant and renders it in the session time zone, while timestamp stores a wall-clock reading with no location attached. The second is the predicate shape: a range on the raw column (created_at >= '2026-11-01' AND created_at < '2026-12-01') can use a B-tree index, while WHERE date_trunc('month', created_at) = '2026-11-01' cannot and scans.
timestamptz is the correct default for anything that records when something happened — an order, a login, a job start. Use date for calendar days that have no clock (a holiday, an invoice date) and timestamp only for a future local wall-clock appointment whose meaning is tied to a place, which you should usually store as timestamptz plus an IANA zone name instead.
Interval arithmetic hides two traps. interval '1 month' has no fixed length — 31 days in January, 28 in February — while interval '30 days' does, and mixing them in a loop drifts. Day-length boundaries also move timestamptz arithmetic across daylight saving transitions, so "midnight local" is 23 or 25 hours away from "midnight local" the day before, depending on the zone and the date.
Basic syntax and query signatures
-- Types
created_at timestamptz NOT NULL DEFAULT now(),
birthday date,
local_slot timestamp -- a wall-clock time with no zone
-- Current values
SELECT now(), -- timestamptz, constant within the transaction
clock_timestamp(), -- timestamptz, advances per call
current_date, -- date in the session zone
current_timestamp;
-- Construction and parsing
SELECT timestamptz '2026-11-14 07:00:00+09',
'2026-11-14 07:00'::timestamp AT TIME ZONE 'Asia/Seoul',
to_timestamp(1763071200), -- from epoch seconds
make_date(2026, 11, 14),
make_interval(days => 7);
-- Truncation and extraction
SELECT date_trunc('hour', created_at), -- returns timestamptz
date_trunc('month', created_at AT TIME ZONE 'Asia/Seoul'), -- local month
EXTRACT(YEAR FROM created_at),
EXTRACT(EPOCH FROM created_at),
date_part('dow', created_at);
-- Arithmetic
SELECT created_at + interval '7 days',
created_at - interval '90 minutes',
deadline - now() AS remaining, -- interval
CURRENT_DATE - birthday AS days_old, -- integer in PostgreSQL
age(now(), birthday); -- interval with years/months
-- Formatting and parsing
SELECT to_char(created_at AT TIME ZONE 'Asia/Seoul', 'YYYY-MM-DD HH24:MI');
SELECT to_date('14/11/2026', 'DD/MM/YYYY');Range predicates — the index-friendly form:
-- Half-open range: includes all of November, excludes December
SELECT count(*) FROM orders
WHERE created_at >= '2026-11-01 00:00:00+09'
AND created_at < '2026-12-01 00:00:00+09';Key comparison table
| Type | Stores | Time zone aware | Typical use |
|---|---|---|---|
timestamptz | Absolute instant (UTC internally) | Yes, renders per session | Event timestamps |
timestamp | Wall-clock reading | No | Legacy data, local-only schedules |
date | Calendar day | No | Birthdays, invoice dates, holidays |
time | Time of day | No | Opening hours |
interval | Duration with months/days/micros | N/A | Retention windows, offsets |
int epoch seconds | Absolute instant | Yes, by convention | Foreign systems, compact rows |
| Task | PostgreSQL | MySQL 8.0 | SQL Server |
|---|---|---|---|
| Truncate to hour | date_trunc('hour',x) | DATE_FORMAT / CAST | DATETRUNC (2022+) |
| Add interval | x + interval '1 day' | DATE_ADD(x, INTERVAL 1 DAY) | DATEADD(day,1,x) |
| Difference | x - y (interval) | TIMESTAMPDIFF(SECOND,x,y) | DATEDIFF(day,x,y) |
| Convert zone | x AT TIME ZONE 'Asia/Seoul' | CONVERT_TZ(x,'UTC','Asia/Seoul') | AT TIME ZONE |
| Extract field | EXTRACT(YEAR FROM x) | EXTRACT(YEAR FROM x) | DATEPART(year,x) |
| Predicate | Index usable | Reason |
|---|---|---|
created_at >= a AND created_at < b | Yes | Range on the bare column |
created_at::date = '2026-11-14' | No | Function on the indexed column |
date_trunc('day', created_at) = ... | No | Function on the indexed column |
created_at >= a AND < b with timestamptz | Yes | Store and compare in the same type |
EXTRACT(YEAR FROM created_at) = 2026 | No | Rewrite as a range |
Practical edge cases and deep dives
1. "Today" for a user in another time zone
-- Wrong: uses the server session zone
SELECT * FROM orders WHERE created_at::date = current_date;
-- Right: define the day in the user's zone, then use a range
SET TIME ZONE 'Asia/Seoul';
SELECT * FROM orders
WHERE created_at >= date_trunc('day', now())
AND created_at < date_trunc('day', now()) + interval '1 day';
-- Per-row zone (multi-region users)
SELECT count(*) FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= date_trunc('day', now() AT TIME ZONE u.tz) AT TIME ZONE u.tz
AND o.created_at < (date_trunc('day', now() AT TIME ZONE u.tz) + interval '1 day') AT TIME ZONE u.tz;date_trunc('day', timestamptz) is evaluated in the session zone and yields a timestamptz, which is what you want for a range. created_at::date throws away the time but not the cost: it also destroys sargability.
2. The type you insert is not always the type you meant
'2026-11-14 07:00'::timestamp has no zone. Comparing it to timestamptz forces a cast using the session zone, so the same literal means 07:00 Seoul in one connection and 07:00 UTC in another.
-- Ambiguous: depends on the session zone
WHERE created_at >= '2026-11-14 07:00';
-- Explicit
WHERE created_at >= '2026-11-14 07:00+09';Beware the middle layer too: JDBC's setTimestamp uses java.sql.Timestamp bound to a calendar, and a driver parameter typed as timestamp will silently drop the offset. Set options=-c timezone=UTC on the connection and convert at the edges.
3. Daylight saving: missing and duplicated local times
-- 2026-03-08 02:30 does not exist in America/New_York
SELECT '2026-03-08 02:30'::timestamp AT TIME ZONE 'America/New_York';
-- 2026-03-08 07:30+00 (normalised forward, no error)
-- 2026-11-01 01:30 occurs twice
SELECT '2026-11-01 01:30'::timestamp AT TIME ZONE 'America/New_York'; -- 05:30+00 (first)For a scheduler that stores local wall-clock times, a spring-forward gap must produce an explicit business decision (skip, shift forward, or run at the next valid instant), and a fall-back overlap must not run the job twice. Storing timestamptz plus a rule ("09:00 local each day") and resolving the instant at run time is the design that survives both.
4. interval '1 month' is not interval '30 days'
SELECT '2026-01-31'::date + interval '1 month'; -- 2026-02-28
SELECT '2026-01-31'::date + interval '30 days'; -- 2026-03-02
SELECT now() + interval '1 month' * 12; -- drifts by calendar monthFor retention ("delete rows older than 90 days") use an interval in days. For billing cycles ("same day next month") use months and accept clipping at month ends. Adding a month to the 31st clamps to the last valid day instead of overflowing, which surprises code that then re-adds a month and finds itself a day short.
5. Grouping by month without breaking the index
-- Aggregate by local month, keep the range predicate on the raw column
SELECT date_trunc('month', created_at AT TIME ZONE 'Asia/Seoul') AS mon,
count(*)
FROM orders
WHERE created_at >= now() - interval '12 months'
GROUP BY 1
ORDER BY 1;The predicate filters on created_at; the date_trunc only shapes the output. That distinction — function in SELECT/GROUP BY, never in the WHERE filter — is the single habit that keeps date reports fast.
6. Calendar gaps need generate_series
WITH days AS (
SELECT generate_series('2026-11-01'::date, '2026-11-30'::date, interval '1 day')::date AS d
)
SELECT d, count(o.id) AS orders
FROM days
LEFT JOIN orders o
ON o.created_at >= d::timestamptz
AND o.created_at < (d + 1)::timestamptz
GROUP BY d ORDER BY d;Joining a generated calendar against the fact table keeps zero-order days in the result. GROUP BY created_at::date silently omits them, and a chart without those days is misleading rather than wrong.
7. Epoch conversion and precision
SELECT EXTRACT(EPOCH FROM created_at)::bigint AS seconds,
EXTRACT(EPOCH FROM (deadline - now())) AS secs_left,
to_timestamp(1763071200) AS instant,
now() AT TIME ZONE 'UTC' AS utc_wallclock;EXTRACT(EPOCH FROM interval) returns fractional seconds as numeric; casting to bigint truncates rather than rounds. When handing timestamps to an external system, choose milliseconds or microseconds once and document it — mixing the two produces errors that are invisible for months and then shift by a factor of a thousand.
Common mistakes and runtime errors
| Symptom | Cause | Fix |
|---|---|---|
| Timestamps off by 9 hours | timestamp compared with timestamptz | Use one type; bind offsets explicitly |
Slow queries on created_at | Function applied to the indexed column | Rewrite as a half-open range |
| Missing or duplicated rows at midnight | Inclusive BETWEEN on a timestamp | >= start AND < end |
22008 date/time field value out of range | 02:30 in a DST gap, or 2026-02-30 | Validate before insert, normalise local times |
| Monthly report totals drift | interval '1 month' assumed to be 30 days | Use days for durations, months for cycles |
now() frozen across a long transaction | now() is transaction start time | Use clock_timestamp() when you need the wall clock |
| Zero-count days missing from charts | Grouping the fact table only | generate_series calendar join |
current_date differs from the user's today | Session zone | Set TimeZone per connection or convert explicitly |
Insert rejected: time zone displacement out of range | Mixed offset formats in one literal | Standardise on ISO 8601 with offset |
| Rounding in EPOCH comparisons | Fractional seconds truncated | Keep numeric, cast at the boundary |
| Index unused on range predicate | Wrong column type causing cast in predicate | timestamptz column vs timestamptz parameter |
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
SQL JSON and JSONB: Functions, Operators, and Indexing Strategies
Why jsonb is the default for queryable documents, when containment can use a GIN index, and the expression-index fixes for scalar lookups.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.