Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~4 min readSQL
#SQL#Date and Time#Best Practices
3D illustration of a clock and calendar grid linked by interval arrows crossing time zone meridians with a DST marker

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

sql
-- 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:

sql
-- 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

TypeStoresTime zone awareTypical use
timestamptzAbsolute instant (UTC internally)Yes, renders per sessionEvent timestamps
timestampWall-clock readingNoLegacy data, local-only schedules
dateCalendar dayNoBirthdays, invoice dates, holidays
timeTime of dayNoOpening hours
intervalDuration with months/days/microsN/ARetention windows, offsets
int epoch secondsAbsolute instantYes, by conventionForeign systems, compact rows
TaskPostgreSQLMySQL 8.0SQL Server
Truncate to hourdate_trunc('hour',x)DATE_FORMAT / CASTDATETRUNC (2022+)
Add intervalx + interval '1 day'DATE_ADD(x, INTERVAL 1 DAY)DATEADD(day,1,x)
Differencex - y (interval)TIMESTAMPDIFF(SECOND,x,y)DATEDIFF(day,x,y)
Convert zonex AT TIME ZONE 'Asia/Seoul'CONVERT_TZ(x,'UTC','Asia/Seoul')AT TIME ZONE
Extract fieldEXTRACT(YEAR FROM x)EXTRACT(YEAR FROM x)DATEPART(year,x)
PredicateIndex usableReason
created_at >= a AND created_at < bYesRange on the bare column
created_at::date = '2026-11-14'NoFunction on the indexed column
date_trunc('day', created_at) = ...NoFunction on the indexed column
created_at >= a AND < b with timestamptzYesStore and compare in the same type
EXTRACT(YEAR FROM created_at) = 2026NoRewrite as a range

Practical edge cases and deep dives

1. "Today" for a user in another time zone

sql
-- 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.

sql
-- 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

sql
-- 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'

sql
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 month

For 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

sql
-- 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

sql
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

sql
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

SymptomCauseFix
Timestamps off by 9 hourstimestamp compared with timestamptzUse one type; bind offsets explicitly
Slow queries on created_atFunction applied to the indexed columnRewrite as a half-open range
Missing or duplicated rows at midnightInclusive BETWEEN on a timestamp>= start AND < end
22008 date/time field value out of range02:30 in a DST gap, or 2026-02-30Validate before insert, normalise local times
Monthly report totals driftinterval '1 month' assumed to be 30 daysUse days for durations, months for cycles
now() frozen across a long transactionnow() is transaction start timeUse clock_timestamp() when you need the wall clock
Zero-count days missing from chartsGrouping the fact table onlygenerate_series calendar join
current_date differs from the user's todaySession zoneSet TimeZone per connection or convert explicitly
Insert rejected: time zone displacement out of rangeMixed offset formats in one literalStandardise on ISO 8601 with offset
Rounding in EPOCH comparisonsFractional seconds truncatedKeep numeric, cast at the boundary
Index unused on range predicateWrong column type causing cast in predicatetimestamptz column vs timestamptz parameter

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.