Dates, Times and Time Zones in PostgreSQL

Dates look like the easy part of SQL until two people read the same report and get different numbers. One of them is in Manila and one is in New York, the data holds timestamps, and "September 30" ends at a different moment for each. The cause is almost always the same: the column type did not say which clock the value was read on.

This lesson covers the types, the three tools you will use all the time (date_trunc, intervals and AT TIME ZONE), and the one decision that matters: where the day boundary is. The behaviour described is PostgreSQL's. Other databases name the types differently and handle time zones differently.

The types

The name timestamptz is misleading. It does not store a zone. PostgreSQL converts the value to UTC when it is stored, and converts it to the session's TimeZone setting when it is shown. Same moment, different display.

Date arithmetic

Subtracting two dates gives a whole number of days. Adding an interval gives a timestamp.

SELECT DATE '2026-10-01' - DATE '2026-09-01' AS days,
       DATE '2026-01-31' + INTERVAL '1 month' AS plus_one_month;

 days |   plus_one_month
------+---------------------
   30 | 2026-02-28 00:00:00
(1 row)

Adding one month to 31 January gives 28 February, not an error. PostgreSQL moves to the last day of the shorter month. Check month-end dates on purpose, because that is where billing and due-date bugs live.

Truncate to a period

date_trunc rounds down to the start of a month, week or day. It is how you group by month.

SELECT date_trunc('month', order_date)::date AS month, count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

   month    | orders
------------+--------
 2026-08-01 |      6
 2026-09-01 |      6
(2 rows)

extract pulls out one part, such as the ISO weekday (Monday is 1).

SELECT extract(isodow FROM DATE '2026-10-15') AS isodow, to_char(DATE '2026-10-15', 'Dy, DD Mon YYYY') AS formatted;

 isodow |    formatted
--------+------------------
      4 | Thu, 15 Oct 2026
(1 row)

When you want a row for every month, including the ones with no orders, generate the months and join the data to them.

SELECT m::date AS month, count(o.order_id) AS orders
FROM generate_series(TIMESTAMP '2026-07-01', TIMESTAMP '2026-10-01', INTERVAL '1 month') AS m
LEFT JOIN orders AS o ON o.order_date >= m AND o.order_date < m + INTERVAL '1 month'
GROUP BY m
ORDER BY m;

   month    | orders
------------+--------
 2026-07-01 |      0
 2026-08-01 |      6
 2026-09-01 |      6
 2026-10-01 |      0
(4 rows)

July and October have zero orders and still show up. A report with missing months looks like a report with missing data.

Ranges: half-open beats BETWEEN

Make a table of moments and see why BETWEEN is risky for timestamps.

SET TIME ZONE 'UTC';
CREATE TABLE visits (visited_at timestamptz NOT NULL);
INSERT INTO visits VALUES
  ('2026-09-05 08:00+00'),
  ('2026-09-30 09:00+00'),
  ('2026-09-30 17:30+00'),
  ('2026-10-01 00:00+00');
SELECT count(*) AS between_version FROM visits
WHERE visited_at BETWEEN '2026-09-01' AND '2026-09-30';
SELECT count(*) AS half_open FROM visits
WHERE visited_at >= '2026-09-01' AND visited_at < '2026-10-01';

SET
CREATE TABLE
INSERT 0 4
 between_version
-----------------
               1
(1 row)

 half_open
-----------
         3
(1 row)

'2026-09-30' means midnight at the start of that day, so BETWEEN dropped the two visits later that day. The half-open range, from the first day up to but not including the next one, is right for dates and timestamps alike.

One moment, many displays

The same stored value changes when the session time zone changes.

SET TIME ZONE 'UTC';
SELECT visited_at FROM visits WHERE visited_at = '2026-09-30 17:30+00';
SET TIME ZONE 'Asia/Manila';
SELECT visited_at FROM visits WHERE visited_at = '2026-09-30 17:30+00';

SET
       visited_at
------------------------
 2026-09-30 17:30:00+00
(1 row)

SET
       visited_at
------------------------
 2026-10-01 01:30:00+08
(1 row)

Same row. The first display is 17:30 UTC and the second is 01:30 on 1 October in Manila. Nothing was converted in the table. Only the display changed, and the date changed with it.

AT TIME ZONE is the explicit version. On a timestamptz it returns the wall-clock timestamp in that zone. On a plain timestamp it does the reverse, and says "this wall-clock time is in that zone".

SELECT TIMESTAMPTZ '2026-09-30 17:30+00' AT TIME ZONE 'Asia/Manila' AS manila_wall_clock,
       TIMESTAMP '2026-09-30 17:30' AT TIME ZONE 'Asia/Manila' AS read_as_manila;

  manila_wall_clock  |     read_as_manila
---------------------+------------------------
 2026-10-01 01:30:00 | 2026-09-30 17:30:00+08
(1 row)

The session is still on Asia/Manila from the last example, so the second value is shown with +08. That moment is 17:30 on a Manila clock, which is 09:30 UTC, a different instant from the first row's.

Group by day: whose day?

Count visits per day, first by the UTC day and then by the Manila day.

SELECT (visited_at AT TIME ZONE 'UTC')::date AS utc_day, count(*) AS visits
FROM visits GROUP BY 1 ORDER BY 1;
SELECT (visited_at AT TIME ZONE 'Asia/Manila')::date AS manila_day, count(*) AS visits
FROM visits GROUP BY 1 ORDER BY 1;

  utc_day   | visits
------------+--------
 2026-09-05 |      1
 2026-09-30 |      2
 2026-10-01 |      1
(3 rows)

 manila_day | visits
------------+--------
 2026-09-05 |      1
 2026-09-30 |      1
 2026-10-01 |      2
(3 rows)

The 30 September 17:30 visit belongs to 30 September in UTC and to 1 October in Manila. Neither is wrong. A daily report needs one agreed zone, written in the query, and not whatever the connection happened to use.

Days are not 24 hours

An interval of one day means a calendar day, and an interval of 24 hours means 24 hours. They differ across a daylight saving change.

SET TIME ZONE 'America/New_York';
SELECT TIMESTAMPTZ '2026-03-07 12:00' + INTERVAL '1 day'   AS plus_1_day,
       TIMESTAMPTZ '2026-03-07 12:00' + INTERVAL '24 hours' AS plus_24_hours;

SET
       plus_1_day       |     plus_24_hours
------------------------+------------------------
 2026-03-08 12:00:00-04 | 2026-03-08 13:00:00-04
(1 row)

Clocks in New York moved forward on 8 March 2026, so the day was 23 hours long. Adding one day lands on noon. Adding 24 hours lands on 1 pm.

Common mistakes

• Using BETWEEN for a range of timestamps.
• Storing a moment as timestamp without a zone.
• Grouping by day without saying which zone's day.
• Assuming a day is 24 hours.
• Testing month-end dates with only the 15th.

If you skip this, your daily totals will move by a few rows when someone runs the report from another office, and nobody will know which version is correct. Which zone does your "today" belong to?