Current date and time
SELECT now(); -- timestamptz, fixed for the whole transaction
SELECT current_date, current_time, localtimestamp;
SELECT clock_timestamp(); -- real wall clock, changes within a statement
SELECT statement_timestamp();
now() is the start of the transaction, which is usually what you want for consistent timestamps across a multi-statement write.
Extracting parts
SELECT extract(year FROM now()), extract(month FROM now()), extract(dow FROM now());
SELECT date_part('hour', now());
SELECT to_char(now(), 'Day'), to_char(now(), 'Month');
SELECT date_trunc('month', now()); -- 2026-09-01 00:00:00+06
SELECT date_trunc('hour', created_at) FROM events;
date_trunc is the workhorse of time-series reporting — it rounds a timestamp down to a unit so you can group by it.
Intervals and arithmetic
SELECT now() + interval '7 days';
SELECT now() - interval '3 months';
SELECT current_date + 30; -- integer days on a date
SELECT '2026-12-31'::date - '2026-09-17'::date; -- 105 (integer days)
SELECT age('2026-09-17', '2000-05-10'); -- 26 years 4 mons 7 days
SELECT extract(year FROM age(dob)) AS years_old FROM students;
SELECT justify_interval(interval '400 days');
Interval units: microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century.
Formatting and parsing
SELECT to_char(now(), 'DD Mon YYYY'); -- 17 Sep 2026
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_date('17/09/2026', 'DD/MM/YYYY');
SELECT to_timestamp('2026-09-17 14:30', 'YYYY-MM-DD HH24:MI');
| Pattern | Meaning | Pattern | Meaning |
|---|---|---|---|
YYYY | 4-digit year | HH24 | Hour 00–23 |
MM | Month 01–12 | MI | Minutes |
Mon / Month | Month name | SS | Seconds |
DD | Day 01–31 | AM | Meridiem |
Day | Weekday name | TZ | Time zone |
Time zones
SHOW timezone;
SET timezone = 'Asia/Dhaka';
SELECT now() AT TIME ZONE 'UTC';
SELECT '2026-09-17 10:00'::timestamp AT TIME ZONE 'Asia/Dhaka';
SELECT * FROM pg_timezone_names LIMIT 5;
Store timestamptz, keep the server in UTC, and let each client set its own timezone. AT TIME ZONE converts in both directions depending on whether the input has a zone.
Generating time series
SELECT generate_series(
date_trunc('day', now() - interval '6 days'),
date_trunc('day', now()),
interval '1 day'
) AS day;
Combined with a LEFT JOIN, this gives a report with no missing days:
SELECT d.day::date, count(s.id) AS signups
FROM generate_series(current_date - 29, current_date, interval '1 day') AS d(day)
LEFT JOIN students s ON date_trunc('day', s.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;
Practical examples
SELECT * FROM students WHERE admitted_on >= current_date - interval '30 days';
SELECT to_char(date_trunc('month', created_at), 'YYYY-MM') AS month, count(*)
FROM students GROUP BY 1 ORDER BY 1;
SELECT * FROM bookings WHERE during @> now(); -- range types and timestamps