Working with Date and Time
1. Getting Current Date and Time
| Function | Returns |
|---|
| now() / current_timestamp | timestamptz at TX start |
| statement_timestamp() | At statement start |
| clock_timestamp() | Real wall clock |
| current_date | date |
| current_time | timetz |
2. Using LOCALTIMESTAMP and LOCALTIME
SELECT localtimestamp, -- timestamp (no TZ)
localtime; -- time (no TZ)
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS TZ');
SELECT to_char(now(), 'FMDay, DD FMMonth YYYY');
| Pattern | Output |
|---|
| YYYY MM DD | Year/month/day |
| HH24 MI SS | 24h time |
| Day / Month | Name (padded) |
| FM | Suppress padding |
4. Parsing Date Strings
SELECT to_date('2026-05-20', 'YYYY-MM-DD');
SELECT to_timestamp('2026-05-20 14:30', 'YYYY-MM-DD HH24:MI');
SELECT '2026-05-20'::date, '2026-05-20T14:30Z'::timestamptz;
SELECT extract(year FROM now()),
extract(dow FROM now()), -- 0=Sun
extract(epoch FROM now()); -- unix seconds
| Field | Note |
|---|
| year/month/day/hour/minute/second | Standard |
| dow / isodow | 0-6 (Sun=0) / 1-7 (Mon=1) |
| week / isoyear | ISO 8601 |
| quarter | 1-4 |
| epoch | Unix seconds (numeric) |
6. Truncating Dates
SELECT date_trunc('day', now()),
date_trunc('month', now()),
date_trunc('week', now());
7. Using Date Arithmetic
SELECT now() + interval '7 days';
SELECT current_date - 30; -- 30 days ago
SELECT '2026-05-20'::date - '2026-01-01'::date; -- integer days
8. Calculating Age Between Dates
SELECT age(timestamp '2026-05-20', timestamp '1990-01-15');
SELECT age(birthdate) FROM users; -- since now
| Return | Type |
|---|
| age(end, start) | interval (years/months/days) |
9. Calculating Date Differences
SELECT extract(epoch FROM (end_at - start_at))/3600 AS hours FROM jobs;
-- PG 17+
SELECT date_diff('day', start_at, end_at) FROM jobs;
10. Using Time Zones
SHOW timezone;
SET timezone = 'America/New_York';
SELECT now() AT TIME ZONE 'UTC';
11. Converting Between Time Zones
SELECT timestamptz '2026-05-20 14:00 UTC' AT TIME ZONE 'America/Los_Angeles';
SELECT timezone('Asia/Tokyo', now());
Note: Always store as timestamptz (UTC internally); convert for display only.
12. Using OVERLAPS Operator
SELECT (date '2026-05-01', date '2026-05-10')
OVERLAPS (date '2026-05-08', date '2026-05-20'); -- true
13. Using ISFINITE Function
SELECT isfinite('infinity'::timestamp); -- false
14. Creating INTERVAL Values
SELECT interval '1 year 2 months';
SELECT interval '3' day, interval '90' minute;
SELECT make_interval(years => 1, months => 2, days => 3);
15. Using make_date and make_timestamp Functions
SELECT make_date(2026, 5, 20);
SELECT make_time(14, 30, 0);
SELECT make_timestamptz(2026, 5, 20, 14, 30, 0, 'UTC');