The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Typed date | DATE '2026-08-12' | View examples |
| Local timestamp | TIMESTAMP '2026-08-12 09:30:00' | View examples |
| Instant with offset | TIMESTAMPTZ '2026-08-12 09:30:00-03:00' | View examples |
| Transaction time | CURRENT_TIMESTAMP | View examples |
| Wall-clock time | clock_timestamp() | View examples |
| Typed duration | INTERVAL '2 days 3 hours' | View examples |
| Add a duration | started_at + INTERVAL '30 minutes' | View examples |
| Render in a zone | occurred_at AT TIME ZONE 'America/Sao_Paulo' | View examples |
| Truncate to a unit | date_trunc('month', occurred_at, 'UTC') | View examples |
| Extract a field | EXTRACT(ISODOW FROM occurred_at) | View examples |
| Half-open time window | WHERE occurred_at >= $1 AND occurred_at < $2 | View examples |
| Format for display | to_char(occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') | View examples |
Use date for calendar dates, timestamp with time zone for real instants, timestamp without time zone for local wall-clock values whose zone is supplied separately, and interval for durations. Prefer typed ISO literals and define business time-zone and boundary rules explicitly.
Step by step
Detailed examples
Choose a type from the meaning
date stores a calendar day. timestamptz stores an instant and displays it in the session TimeZone; it does not preserve the input zone name. timestamp stores fields without a zone. Avoid time with time zone for most application models because a time-zone offset without a date cannot capture seasonal rules.
SELECT DATE '2026-08-12' AS day,
TIMESTAMP '2026-08-12 09:30:00' AS local_time,
TIMESTAMPTZ '2026-08-12 09:30:00-03:00' AS instant;Know which clock a function represents
CURRENT_TIMESTAMP and now() return the transaction start; statement_timestamp() returns statement start; clock_timestamp() changes during execution. Transaction-stable time supports consistent auditing, while elapsed-time measurement needs an actual clock.
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, statement_timestamp(), clock_timestamp();Distinguish calendar arithmetic from fixed seconds
Intervals can contain months, days, and microseconds. A day added across daylight-saving transitions may not equal 24 elapsed hours in a local zone. Define whether a requirement means calendar recurrence or an exact duration and test month-end behavior.
WITH jobs(started_at) AS (VALUES (TIMESTAMPTZ '2026-08-12 12:00:00+00'))
SELECT started_at, started_at + INTERVAL '30 minutes' AS expires_at FROM jobs;Convert at explicit system boundaries
AT TIME ZONE converts timestamptz to local timestamp, or interprets a timestamp as local in a named zone and returns an instant. Use IANA zone names for civil-time rules rather than fixed abbreviations. Store instants and retain a separate zone identifier when future local scheduling must preserve regional rules.
WITH event(instant) AS (VALUES (TIMESTAMPTZ '2026-08-12 12:00:00+00'))
SELECT instant AT TIME ZONE 'America/Sao_Paulo' AS sao_paulo,
instant AT TIME ZONE 'Asia/Tokyo' AS tokyo
FROM event;Bucket in the business zone
date_trunc sets less-significant fields to zero and can accept a zone for timestamptz. EXTRACT returns numeric fields; ISO day and ISO year rules differ around calendar-year edges. Ensure indexes and queries use compatible expressions when bucketing large tables.
SELECT date_trunc('day', occurred_at, 'UTC') AS utc_day, COUNT(*)
FROM events
GROUP BY utc_day
ORDER BY utc_day;Use half-open ranges and format last
Half-open [start,end) predicates chain adjacent windows without double counting and avoid guessing timestamp precision. Keep temporal types through filtering and sorting; to_char returns text for a presentation boundary and should not replace typed comparisons.
SELECT event_id, occurred_at
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-08-12 00:00:00+00'
AND occurred_at < TIMESTAMPTZ '2026-08-13 00:00:00+00'
ORDER BY occurred_at;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.