Date & Time Types

PostgreSQL has a dedicated type for every shape of temporal data you might need: a calendar date on its own, a time of day on its own, a combined date-and-time with or without timezone awareness, and a duration between two points in time.

Type	Stores	Example
`DATE`	A calendar date, no time	`2026-07-09`
`TIME`	A time of day, no date	`14:30:00`
`TIMESTAMP`	Date + time, no timezone awareness	`2026-07-09 14:30:00`
`TIMESTAMPTZ`	Date + time, timezone-aware (stored as UTC)	`2026-07-09 14:30:00+00`
`INTERVAL`	A span of time — a duration	`3 days 04:00:00`

TIMESTAMP vs TIMESTAMPTZ: the distinction that matters most

This is the single most important thing to understand about PostgreSQL's date/time types, and it trips up a lot of developers coming from other systems.

Warning

`TIMESTAMP` (also written `TIMESTAMP WITHOUT TIME ZONE`) stores a naive date and time with **no timezone information at all** — it means whatever the application decides it means. `TIMESTAMPTZ` (`TIMESTAMP WITH TIME ZONE`) is timezone-**aware**: PostgreSQL converts whatever you insert into UTC for storage, and converts it back to the session's configured timezone whenever you read it. Mixing the two, or assuming `TIMESTAMP` carries timezone context it doesn't have, is one of the most common sources of subtle date/time bugs in real applications.

The same literal, two different types

SQL

SET timezone = 'UTC';

CREATE TABLE demo (
    ts_naive TIMESTAMP,
    ts_aware TIMESTAMPTZ
);

INSERT INTO demo VALUES ('2026-07-09 14:30:00-05', '2026-07-09 14:30:00-05');

SELECT ts_naive, ts_aware FROM demo;
-- ts_naive: 2026-07-09 14:30:00        (the -05 offset was simply discarded)
-- ts_aware: 2026-07-09 19:30:00+00     (converted to UTC for storage)

SET timezone = 'America/New_York';
SELECT ts_naive, ts_aware FROM demo;
-- ts_naive: 2026-07-09 14:30:00        (unchanged — it never had timezone info)
-- ts_aware: 2026-07-09 15:30:00-04     (re-rendered for the new session timezone)

Tip

As a default rule: use `TIMESTAMPTZ` for anything user-facing — created-at columns, event timestamps, appointment times. It removes an entire class of “whose timezone is this?” bugs, since PostgreSQL handles the UTC conversion consistently regardless of where the inserting client or the reading client happens to be. Reach for plain `TIMESTAMP` only when you genuinely want a timezone-independent, wall-clock value — for example, “this clinic opens at 09:00 local time, wherever local happens to be.”

DATE and TIME on their own

DATE and TIME columns

SQL

CREATE TABLE events (
    event_date DATE NOT NULL,
    start_time TIME NOT NULL
);

INSERT INTO events VALUES ('2026-08-15', '09:00:00');

INTERVAL: durations

Working with intervals

SQL

SELECT INTERVAL '1 day 4 hours';
SELECT now() + INTERVAL '30 minutes' AS half_hour_from_now;
SELECT now() - INTERVAL '7 days' AS one_week_ago;

-- The difference between two timestamps is itself an interval
SELECT '2026-08-15 09:00:00'::timestamptz - now() AS time_until_event;

Getting the current time

NOW() and CURRENT_TIMESTAMP

SQL

SELECT now();               -- returns TIMESTAMPTZ
SELECT CURRENT_TIMESTAMP;   -- SQL-standard equivalent, also TIMESTAMPTZ
SELECT CURRENT_DATE;        -- just the date
SELECT CURRENT_TIME;        -- just the time

Note

`now()` and `CURRENT_TIMESTAMP` both return a `TIMESTAMPTZ` and are interchangeable — `now()` is PostgreSQL's own function syntax, while `CURRENT_TIMESTAMP` is the SQL-standard spelling.

DATE, TIME, TIMESTAMP, and TIMESTAMPTZ each cover a different combination of date, time, and timezone awareness.
TIMESTAMP is naive — it has no idea what timezone it represents.
TIMESTAMPTZ stores everything as UTC internally and converts on display based on the session timezone.
Default to TIMESTAMPTZ for user-facing timestamps to avoid an entire class of timezone bugs.
INTERVAL represents a duration and is what you get when you subtract one timestamp from another.