Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 90 ยท Data Types

Dates, Times and Time Zones

Learn PostgreSQL date, time, timestamp, timestamptz and interval types, AT TIME ZONE, date_trunc, extract, age and generate_series, with live ISO-8601 practice.

Dates and times look simple until a customer in another country sees a meeting an hour off, or a daylight saving change makes a daily report skip a day. PostgreSQL has one of the best date and time systems of any database: real types for dates, times, timestamps with and without time zones, and intervals, plus functions to truncate, extract and shift them.

This lesson covers the types and the one choice that matters most (timestamptz), then the everyday functions. PostgreSQL's own date types and functions are shown in read-only blocks with the real results. SQLite stores dates as ISO-8601 text, so the live examples practice the same ideas, such as date arithmetic, month boundaries and generated calendars, with SQLite's functions. For a deeper SQLite view, see Date and Time Functions.

The date and time types

date A calendar day, no time.
time A time of day, no date.
timestamptz An exact moment in time.
interval A length of time, like 3 days.

timestamp without time zone is a wall-clock reading, not a moment.

1 / 2

The types

Type Example Notes
date 2025-03-15 4 bytes, no time part
time 14:30:00 Time of day without a date
timestamp 2025-03-15 14:30:00 Date and time, no time zone
timestamptz 2025-03-15 14:30:00+00 An exact instant
interval 3 days 04:00:00 A duration

There is also timetz (time with a zone), which the PostgreSQL documentation discourages because a zone without a date is meaningless during daylight saving changes.

The time type in detail

A time value is a time of day with microsecond resolution (8 bytes). PostgreSQL accepts many input layouts and always prints HH:MM:SS with fractional digits only when they are not zero. These results come from PostgreSQL 18:

SQL
SELECT '14:30'::time;              -- 14:30:00
SELECT '143015'::time;             -- 14:30:15        (no colons)
SELECT '2:05 PM'::time;            -- 14:05:00        (12-hour clock)
SELECT '14:30:15.123456'::time;    -- 14:30:15.123456 (up to 6 fractional digits)
SELECT '04:05:06.789'::time(0);    -- 04:05:07        time(p) rounds to p fractional digits
SELECT '04:05:06.789'::time(1);    -- 04:05:06.8
SELECT '24:00:00'::time;           -- 24:00:00        allowed: the end of the day
SELECT '25:00:00'::time;           -- ERROR:  date/time field value out of range

24:00:00 is the one value outside the normal range, and it is accepted so that "closing time" can be written as midnight at the end of a day. Compare times with the ordinary operators, and add or subtract intervals. Arithmetic wraps around midnight, because a time has no date to carry into:

SQL
SELECT '14:30'::time + interval '90 minutes';     -- 16:00:00
SELECT '23:30'::time + interval '90 minutes';     -- 01:00:00   wrapped past midnight
SELECT '08:00'::time - '06:15'::time;             -- 01:45:00   (an interval)
SELECT '14:30'::time BETWEEN '09:00' AND '17:00'; -- true
SELECT extract(hour FROM '14:30:15'::time);       -- 14
SELECT extract(epoch FROM '01:00:00'::time);      -- 3600.000000  seconds since 00:00
SELECT date '2025-03-15' + time '14:30';          -- 2025-03-15 14:30:00  (a timestamp)
SELECT to_char('14:30:15'::time, 'HH12:MI PM');   -- 02:30 PM
SELECT make_time(14, 30, 15.5);                   -- 14:30:15.5

localtime and current_time return the time of day now; localtime is a plain time while current_time is a timetz that carries your session's zone. Use time for things like opening hours or a daily alarm. If the thing you store happened at a specific moment, use timestamptz instead; a bare time cannot tell you which day.

timestamp vs timestamptz

The name timestamptz ("timestamp with time zone") is misleading: it does not store a zone. It stores an instant, internally as UTC. On input, any zone offset in the text is used to convert to UTC and then discarded. On output, PostgreSQL converts the instant to the session's TimeZone setting.

timestamp (without time zone) stores exactly the digits you gave it, with no conversion. It is the right type only for things that really are wall-clock readings, such as "the store opens at 09:00 local time wherever it is", or a recurring schedule.

SQL
-- Read-only: PostgreSQL
SET TimeZone = 'UTC';
SELECT '2025-01-15 12:00:00+00'::timestamptz;      -- 2025-01-15 12:00:00+00

SET TimeZone = 'Asia/Tokyo';
SELECT '2025-01-15 12:00:00+00'::timestamptz;      -- 2025-01-15 21:00:00+09
-- Same instant, shown on a different clock.

SELECT '2025-01-15 12:00:00+00'::timestamp;        -- 2025-01-15 12:00:00
-- The "+00" was ignored: this is just digits.

Default rule: use timestamptz for any event that happened or will happen at a point in time (created, updated, paid, scheduled). It always means the same instant to every client. Use timestamp rarely, and date for calendar facts such as birthdays or invoice dates.

Avoid storing "local time" in a plain timestamp and hoping everyone agrees on the zone. Around daylight saving changes, one local time can occur twice or not at all, and the data cannot tell you which one you meant.

Getting the current time

SQL
-- Read-only: PostgreSQL
SELECT now();                -- start of the current transaction, timestamptz
SELECT current_date;         -- date
SELECT clock_timestamp();    -- actual wall-clock time, changes within a transaction

now() returns the same value for the whole transaction, which is usually what you want for consistent created_at values. Use clock_timestamp() when you want the real time at each call.

Intervals and date arithmetic

An interval is a length of time. It keeps months, days and microseconds as separate parts, because a month has no fixed number of days:

SQL
-- Read-only: PostgreSQL
SELECT interval '1 day 2 hours';                     -- 1 day 02:00:00
SELECT make_interval(days => 10, hours => 3);        -- 10 days 03:00:00

SELECT date '2025-03-15' + 10;                       -- 2025-03-25 (date + integer = date)
SELECT date '2025-03-15' - date '2025-01-01';        -- 73 (date - date = integer days)
SELECT date '2025-01-31' + interval '1 month';       -- 2025-02-28 00:00:00 (clamped to month end)
SELECT timestamp '2025-03-15 18:00' - timestamp '2025-03-15 09:30';   -- 08:30:00

Note the types: date + interval gives a timestamp, date - date gives a plain number of days, and timestamp - timestamp gives an interval. Adding one month to January 31 gives February 28, because PostgreSQL clamps to the last day that exists.

age() produces a readable difference using years, months and days:

SQL
-- Read-only: PostgreSQL
SELECT age(date '2025-03-15', date '2000-03-01');    -- 25 years 14 days
SELECT age(date '2000-03-01');                       -- one argument: age as of today

Truncating, extracting and constructing

date_trunc() rounds down to a unit, which is the standard way to group by day, week or month. extract() pulls out one field. make_date() builds a date from parts:

SQL
-- Read-only: PostgreSQL
SELECT date_trunc('month', timestamp '2025-03-15 14:30:00');   -- 2025-03-01 00:00:00
SELECT date_trunc('week',  timestamp '2025-03-15 14:30:00');   -- 2025-03-10 00:00:00 (Monday)

SELECT extract(year  FROM date '2025-03-15');                  -- 2025
SELECT extract(dow   FROM date '2025-03-15');                  -- 6   (0 = Sunday)
SELECT extract(isodow FROM date '2025-03-15');                 -- 6   (7 = Sunday)
SELECT extract(epoch FROM timestamptz '2025-01-15 12:00+00');  -- 1736942400

SELECT make_date(2025, 3, 15);                                 -- 2025-03-15
SELECT to_char(date '2025-03-15', 'Dy DD Mon YYYY');           -- Sat 15 Mar 2025

A monthly report is usually GROUP BY date_trunc('month', created_at). Since PostgreSQL 14, extract() returns numeric, so a result like 2025 prints without decimals.

Converting between time zones

AT TIME ZONE is the operator that confuses most people because it flips the type:

  • timestamptz AT TIME ZONE 'zone' returns a timestamp: the wall clock in that zone.
  • timestamp AT TIME ZONE 'zone' returns a timestamptz: it says "this wall-clock reading was in that zone".
SQL
-- Read-only: PostgreSQL
SELECT '2025-01-15 12:00:00+00'::timestamptz AT TIME ZONE 'America/New_York';
-- 2025-01-15 07:00:00        (timestamp)

SELECT '2025-01-15 09:00:00'::timestamp AT TIME ZONE 'Asia/Tokyo';
-- 2025-01-15 00:00:00+00     (timestamptz, shown in a UTC session)

Prefer full zone names such as America/New_York or Europe/Berlin over abbreviations like EST, because names carry the daylight saving rules. SELECT name FROM pg_timezone_names; lists them all.

Calendars with generate_series

generate_series() can produce a row for every day, which is the basis of reports that must show days with no data:

SQL
-- Read-only: PostgreSQL
SELECT d::date
FROM generate_series(date '2025-03-01', date '2025-03-05', interval '1 day') AS d;
-- 2025-03-01 .. 2025-03-05

LEFT JOIN your data onto that list and the empty days appear as zeros. generate_series and Set-Returning Functions covers this pattern properly.

Practicing the same ideas live

SQLite has no date type: it stores ISO-8601 text such as 2025-03-15 14:30:00, which sorts correctly as plain text. Its date() function with modifiers performs the same arithmetic.

SQL Date arithmetic with ISO-8601 text

Press Run (or Ctrl + Enter) to see the result here.

The days between January 1 and March 15 come out as 73, the same answer PostgreSQL gives for date - date. Adding one month to January 31 does something different: SQLite normalizes 2025-02-31 forward to 2025-03-03, while PostgreSQL clamps to February 28. The two engines disagree here, so never assume month arithmetic is portable.

A daily calendar can be built with a recursive CTE, the SQLite counterpart of generate_series, and joined to event data so empty days show up as zero:

SQL A calendar table and half-open ranges

Press Run (or Ctrl + Enter) to see the result here.

The join condition is a half-open range: >= start AND < next day. It captures 23:59:59 correctly and never relies on BETWEEN, which would miss times later on the last day. The same rule applies in PostgreSQL: filter timestamps with >= '2025-03-01' AND < '2025-04-01', not with BETWEEN.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a timestamptz column actually store?

    timestamptz stores an instant. The zone in the input is used to convert to UTC and is then discarded; output is converted to the session TimeZone.
  2. 2. Which type is the best default for a created_at column?

    created_at records a moment in time, so timestamptz keeps it unambiguous for every client.
  3. 3. What type does '2025-01-15 12:00:00+00'::timestamptz AT TIME ZONE 'Asia/Tokyo' return?

    Applying AT TIME ZONE to a timestamptz returns a plain timestamp showing the wall clock in that zone. Applying it to a timestamp returns a timestamptz.
  4. 4. Why is WHERE ts BETWEEN '2025-03-01' AND '2025-03-31' a poor way to select March?

    The upper bound is midnight at the start of March 31, so later timestamps that day are left out. Use ts >= '2025-03-01' AND ts < '2025-04-01'.

Summary

  • Use timestamptz for moments in time, date for calendar days, and timestamp only for true wall-clock readings.
  • timestamptz stores UTC and displays in the session TimeZone; store UTC, convert for display.
  • interval holds months, days and time separately; date - date gives days, and month arithmetic clamps to month end.
  • date_trunc(), extract(), age(), make_date() and to_char() cover most date work.
  • AT TIME ZONE flips between timestamp and timestamptz; prefer zone names like Europe/Berlin to abbreviations.
  • Filter ranges with >= start AND < next_start, and build calendars with generate_series (a recursive CTE in SQLite).

Next: UUIDs, Serials and Identity Columns.