Dates, Times & Time Zones

Date and time functions are how SQL turns moments into calendar answers: days, hours, month buckets, elapsed time, and human-readable labels. The trap is that a calendar day, a wall-clock timestamp, and an exact instant are different facts, even when they look similar on screen.

This lesson uses fixed dates for every asserted grid. Postgres also has live clock values such as CURRENT_DATE and NOW(), but those change with the day you run them, so they belong in experiments, not in byte-for-byte lesson output.

The mental split is simple and worth keeping close. A DATE answers "which day?" A TIME answers "what clock time?" A TIMESTAMP answers "what local date and clock time?" A TIMESTAMPTZ answers "which exact instant?" Most date bugs start when a query quietly answers one of those questions while the human meant another.

Types first: day, time, wall clock, instant

The core temporal types from 3.1 now become working tools. The SET line pins the display zone for the TIMESTAMPTZ value so the grid is deterministic:

SET TIME ZONE 'UTC';
 
SELECT DATE '2026-04-10' AS sale_day,
       TIME '08:30:00' AS opens_at,
       TIMESTAMP '2026-04-10 08:30:00' AS wall_clock_time,
       TIMESTAMPTZ '2026-04-10 08:30:00+00' AS exact_moment;

SET
  sale_day  | opens_at |   wall_clock_time   |      exact_moment
------------+----------+---------------------+------------------------
 2026-04-10 | 08:30:00 | 2026-04-10 08:30:00 | 2026-04-10 08:30:00+00
(1 row)

Use DATE for a calendar day. Use TIME for a clock time without a day. Use TIMESTAMP for a local wall-clock value: "the workshop starts at 08:30 in the shop's schedule." Use TIMESTAMPTZ for an exact moment: "this payment happened at this instant."

CURRENT_DATE gives the current calendar date for the session. NOW() gives the current timestamp. They are useful in real queries, but they make lesson grids non-repeatable, so the examples below keep using literals such as DATE '2026-04-10'.

When you do use live clock functions, keep the same discipline you use everywhere else in SQL: know the type, know the comparison you are making, and do not format the value to text until the final display step. "Today's rows" is still a range question, not an equality question.

Intervals and differences

Date arithmetic uses intervals for durations and subtraction for differences:

SELECT TIMESTAMP '2026-04-10 12:00:00' - INTERVAL '30 days' AS thirty_days_ago,
       TIMESTAMP '2026-04-10 12:00:00' + INTERVAL '2 weeks' AS two_weeks_later,
       DATE '2026-04-10' - DATE '2026-04-01' AS days_between,
       TIMESTAMP '2026-04-10 10:15:00' - TIMESTAMP '2026-04-10 08:45:00' AS elapsed_time;

   thirty_days_ago   |   two_weeks_later   | days_between | elapsed_time
---------------------+---------------------+--------------+--------------
 2026-03-11 12:00:00 | 2026-04-24 12:00:00 |            9 | 01:30:00
(1 row)

Read the units carefully. INTERVAL '30 days' means a duration you can add or subtract. DATE - DATE returns a day count. TIMESTAMP - TIMESTAMP returns elapsed time.

This is why interval literals are clearer than mystery arithmetic. INTERVAL '2 weeks' tells the next reader the unit right where the math happens. You can write intervals in many shapes, but the important beginner habit is not the spelling variety; it is making time math explicit enough that a report sentence and a query sentence agree.

Buckets, parts, and display labels

Analysts spend a lot of time turning exact event times into buckets. DATE_TRUNC is the everyday tool: it snaps a timestamp down to a boundary such as the start of its day or hour.

SET TIME ZONE 'UTC';
 
SELECT id,
       happened_at,
       DATE_TRUNC('day', happened_at) AS event_day,
       DATE_TRUNC('hour', happened_at) AS event_hour,
       EXTRACT(DOW FROM happened_at) AS dow,
       TO_CHAR(happened_at, 'Dy HH24:MI') AS display_time
FROM cafe_events
ORDER BY id;

SET
 id |      happened_at       |       event_day        |       event_hour       | dow | display_time
----+------------------------+------------------------+------------------------+-----+--------------
  1 | 2026-04-10 00:00:00+00 | 2026-04-10 00:00:00+00 | 2026-04-10 00:00:00+00 |   5 | Fri 00:00
  2 | 2026-04-10 08:45:00+00 | 2026-04-10 00:00:00+00 | 2026-04-10 08:00:00+00 |   5 | Fri 08:45
  3 | 2026-04-10 23:50:00+00 | 2026-04-10 00:00:00+00 | 2026-04-10 23:00:00+00 |   5 | Fri 23:50
  4 | 2026-04-11 00:00:00+00 | 2026-04-11 00:00:00+00 | 2026-04-11 00:00:00+00 |   6 | Sat 00:00
  5 | 2026-04-11 09:15:00+00 | 2026-04-11 00:00:00+00 | 2026-04-11 09:00:00+00 |   6 | Sat 09:15
(5 rows)

DATE_TRUNC('day', happened_at) keeps the type as a timestamp-like value, but moves every event to the start of its day. That makes it a clean grouping key later: one bucket per day, one bucket per hour, one bucket per month. EXTRACT pulls one part out of the value; here DOW is the day-of-week number. TO_CHAR formats a typed value as display text.

Keep that distinction clean: EXTRACT gives you values that are still useful for logic, sorting, or grouping. TO_CHAR gives you text for humans. Once you format a timestamp as 'Fri 08:45', it is a label, not a moment.

That distinction saves a lot of reports. If you group by TO_CHAR(happened_at, 'Dy'), you are grouping by a display label. If you group by DATE_TRUNC('day', happened_at), you are grouping by a typed bucket the database can still compare and sort as time. Format late; bucket early.

TIMESTAMP vs TIMESTAMPTZ

The difference is easiest to see by changing the session time zone. The TIMESTAMP column prints the same wall-clock value in both sessions; the TIMESTAMPTZ column prints the same instant in the chosen zone.

SET TIME ZONE 'UTC';
 
SELECT id,
       local_note,
       happened_at
FROM cafe_events
WHERE id = 2;
 
SET TIME ZONE 'America/New_York';
 
SELECT id,
       local_note,
       happened_at
FROM cafe_events
WHERE id = 2;

SET
 id |     local_note      |      happened_at
----+---------------------+------------------------
  2 | 2026-04-10 08:45:00 | 2026-04-10 08:45:00+00
(1 row)
SET
 id |     local_note      |      happened_at
----+---------------------+------------------------
  2 | 2026-04-10 08:45:00 | 2026-04-10 04:45:00-04
(1 row)

That is why the common rule is: store exact events as TIMESTAMPTZ, keep them UTC-minded, and convert at the edge for the user or report. Use plain TIMESTAMP when the fact is genuinely a local schedule, not an instant.

Think about the café opening at 08:00 every morning. That schedule is a wall-clock promise, so a plain timestamp or time can be reasonable. Think about a card payment, an audit log entry, or a shipped order. Those happened once in the world, so you want an exact instant and a deliberate display zone. The same text on screen can represent either idea; the column type is the promise.

The midnight bug

Now the classic wrong result. A human says "events on April 10." The query compares an exact timestamp to a date:

SET TIME ZONE 'UTC';
 
SELECT id, happened_at, amount
FROM cafe_events
WHERE happened_at = DATE '2026-04-10'
ORDER BY id;

SET
 id |      happened_at       | amount
----+------------------------+--------
  1 | 2026-04-10 00:00:00+00 |   6.20
(1 row)

One row. No error. The query quietly treated the date as midnight, so it found the event exactly at midnight and missed the rest of the day.

The fix is a half-open range: start included, next start excluded.

SET TIME ZONE 'UTC';
 
SELECT id, happened_at, amount
FROM cafe_events
WHERE happened_at >= TIMESTAMPTZ '2026-04-10 00:00:00+00'
  AND happened_at < TIMESTAMPTZ '2026-04-11 00:00:00+00'
ORDER BY id;

SET
 id |      happened_at       | amount
----+------------------------+--------
  1 | 2026-04-10 00:00:00+00 |   6.20
  2 | 2026-04-10 08:45:00+00 |  14.50
  3 | 2026-04-10 23:50:00+00 |   9.80
(3 rows)

This habit also retires the BETWEEN end-point surprise. BETWEEN is inclusive, so a range ending at the next midnight can pull in the first instant of the next day. >= start AND < next_start says the boundary rule out loud and works for days, hours, months, and years.

Avoid the tempting fake ending like 23:59:59. Real data can have fractions of a second, and different systems store different precision. The next bucket's start is always a clean boundary, so the half-open pattern scales from "one day" to "one month" without inventing a last possible instant.

It also makes review easier because the boundary is visible.

That precision is worth the extra typing.

Dates and times are now shaped into useful buckets, labels, ranges, and instants. Next, the same function mindset turns to numbers: rounding, money, ratios, and the division traps that make dashboards lie.