Time and Time Zones in PostgreSQL — Why timestamptz and How to Work With It

← Back to the section

Time is one of the most common sources of silent bugs in databases. An order placed at 23:30 doesn't show up in the daily report. Events arrive "from the future". A cron job fires twice. In most of these cases, the culprit isn't the application code — it's the column type in PostgreSQL.

Let's look at how PostgreSQL works with time and why the right type choice prevents a whole class of problems.

The problem: timestamp without a time zone loses the meaning of the data

Imagine you store the string '2026-05-07 14:00:00' in the database. What is it? 14:00 in UTC? In Moscow time? In the application's zone? In the server's zone? PostgreSQL doesn't know — it will store these digits literally, without any context.

-- The timestamp type (without a time zone)
INSERT INTO orders (created_at) VALUES ('2026-05-07 14:00:00');
-- Stored literally as '2026-05-07 14:00:00'
-- What it means a year from now — nobody knows

When data comes from different servers or clients in different time zones, the values get mixed up: '2026-05-07 12:00:00' from a UTC server and from a Moscow client are different moments in time, but in the database they look identical. Comparing them is meaningless.

The rule is simple: use timestamptz for all business time.

timestamptz — what it is

timestamptz (full name — timestamp with time zone) works differently:

• On write: PostgreSQL takes the value, converts it to UTC using the current session's time zone, and stores it as the number of microseconds since the Unix epoch.
• On read: PostgreSQL takes the UTC value from storage, converts it to the current session's time zone, and returns the result.

An important consequence: timestamptz doesn't store a zone — it stores UTC. The zone is only used on input and output.

SET TIME ZONE 'Europe/Moscow';
INSERT INTO order_event (occurred_at) VALUES ('2026-05-07 14:00:00');
-- Stored in the database as: 2026-05-07 11:00:00+00 (UTC)

SET TIME ZONE 'UTC';
SELECT occurred_at FROM order_event;
-- Result: 2026-05-07 11:00:00+00

SET TIME ZONE 'America/New_York';
SELECT occurred_at FROM order_event;
-- Result: 2026-05-07 07:00:00-04

Three different representations — the same moment. This is what working with time correctly looks like.

In practice, the schema looks like this:

CREATE TABLE order_event (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at timestamptz NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

How to read timestamptz in the application

When the driver hands a timestamptz to the application, it returns the value in UTC. The application's job is to store it in a type that understands the time zone, not as "local" time without context.

A typical mistake: the driver returns UTC, but the code puts it into a type without a zone. On a server with TZ=UTC everything works; on a developer's machine with TZ=Europe/Moscow it doesn't.

Java

// Correct: Instant is a UTC moment
record OrderEventRow(long id, Instant occurredAt) {}

// Wrong: LocalDateTime — no zone, lost on conversion
record OrderEventRow(long id, LocalDateTime occurredAt) {}

PG column	Java type	Correct
timestamptz	Instant	yes, recommended
timestamptz	OffsetDateTime	yes
timestamptz	ZonedDateTime	yes, but redundant
timestamptz	LocalDateTime	no — the zone is lost
timestamp (without TZ)	LocalDateTime	yes (but the type itself is undesirable)
date	LocalDate	yes
time	LocalTime	yes

With the right configuration, jOOQ generates Instant for timestamptz.

Go

// pgx v5: timestamptz → time.Time (always UTC)
type OrderEventRow struct {
    ID         int64
    OccurredAt time.Time // timestamptz → time.Time{UTC}
}

var row OrderEventRow
err := pool.QueryRow(ctx,
    "SELECT id, occurred_at FROM order_event WHERE id = $1", id,
).Scan(&row.ID, &row.OccurredAt)

// row.OccurredAt.UTC() — guaranteed to be UTC

PG column	Go type	Correct
timestamptz	time.Time	yes (pgx v5 sets UTC)
timestamp (without TZ)	time.Time	yes (no zone in the DB)
date	pgtype.Date / time.Time	yes

Node.js

// node-postgres (pg): timestamptz → Date (UTC inside)
interface OrderEventRow {
  id: number;
  occurred_at: Date;
}

const result = await pool.query<OrderEventRow>(
  'SELECT id, occurred_at FROM order_event WHERE id = $1',
  [id]
);
// result.rows[0].occurred_at.toISOString() — UTC string

PG column	Node type	Correct
timestamptz	Date	yes (pg converts to UTC)
timestamp (without TZ)	Date	careful: pg interprets it as the local TZ
date	string (ISO)	yes

Python

# psycopg v3: timestamptz → datetime(tzinfo=UTC)
from datetime import datetime
import psycopg

async with await psycopg.AsyncConnection.connect(dsn) as conn:
    async with conn.cursor() as cur:
        await cur.execute(
            "SELECT id, occurred_at FROM order_event WHERE id = %s", (row_id,)
        )
        row = await cur.fetchone()
        occurred_at: datetime = row[1]  # datetime(tzinfo=timezone.utc)

# Wrong: a naive datetime without tzinfo — the zone is lost

PG column	Python type	Correct
timestamptz	datetime(tzinfo=UTC)	yes (psycopg v3)
timestamptz	datetime without tzinfo	no — the zone is lost
date	date	yes

When timestamp without a zone is actually needed

There's a rare case where timestamp (without a zone) is justified: "local time not tied to a specific moment".

Examples:

• a store's schedule — "opens at 9 a.m. local time";
• a flight's scheduled departure time in airport local time;
• a holiday date in a local zone.

-- Store schedule: the zone is stored separately
shop_opens_at   time NOT NULL,           -- 09:00
shop_closes_at  time NOT NULL,           -- 18:00
holiday_date    date NOT NULL,           -- 2026-01-01
timezone        text NOT NULL            -- 'Europe/Moscow'

The zone is stored in a separate column, and the application converts when needed. For everything else — timestamptz.

now() and clock_timestamp() — what's the difference

PostgreSQL offers several functions for getting the current time, and they behave differently:

Function	What it returns
now() / transaction_timestamp()	The start of the current transaction. The same throughout the whole transaction.
statement_timestamp()	The start of the current SQL statement.
clock_timestamp()	The actual moment of the call. Every call returns a new value.

For created_at and updated_at, use now(). All rows inserted in a single transaction get the same timestamp — handy for debugging and auditing: a single value shows that the rows appeared together.

created_at timestamptz NOT NULL DEFAULT now()

clock_timestamp() is needed for performance measurements inside a transaction — for example, to see how long a loop inserting 10,000 rows took.

INTERVAL for time offsets

When you need to select records from the last N minutes, days, or months, use INTERVAL:

-- Correct: readable, accounts for calendar quirks
SELECT * FROM session WHERE last_seen_at < now() - interval '15 minutes';
SELECT * FROM report  WHERE period_start > now() - interval '1 month';

-- Wrong: unreadable
SELECT * FROM session WHERE last_seen_at < now() - 900 * interval '1 second';

INTERVAL correctly handles daylight saving time, leap seconds, and the varying length of months.

+infinity for open-ended records

PostgreSQL supports the special values infinity and -infinity for timestamptz:

-- Perpetual subscription
INSERT INTO subscription (expires_at) VALUES ('infinity');

-- Finds active subscriptions, including perpetual ones
SELECT * FROM subscription WHERE expires_at > now();

This is better than NULL or 9999-12-31:

• NULL is ambiguous: "unknown" or "never"?
• 9999-12-31 is a magic constant that you'll have to handle separately.
• infinity clearly expresses the intent and is supported by arithmetic and indexes.

Testability: don't call the clock directly

If a service calls Instant.now() (or time.Now(), new Date(), datetime.now()) directly in the code, tests become flaky: the values in the database and in computations diverge by microseconds, making them hard to compare.

The solution pattern is a ClockService abstraction that can be swapped out in tests:

Java

public interface DateTimeService {
    Instant now();
}

@Component
@Profile("!integration-test")
public class SystemDateTimeService implements DateTimeService {
    @Override
    public Instant now() {
        return Instant.now();
    }
}

// In a test — a mock:
@MockitoBean
DateTimeService dateTimeService;

@BeforeEach
void freezeTime() {
    when(dateTimeService.now()).thenReturn(Instant.parse("2026-05-07T12:00:00Z"));
}

Go

type ClockService interface {
    Now() time.Time
}

type SystemClock struct{}
func (SystemClock) Now() time.Time { return time.Now().UTC() }

// In a test:
type FixedClock struct{ t time.Time }
func (f FixedClock) Now() time.Time { return f.t }

fixed := FixedClock{t: time.Date(2026, 5, 7, 12, 0, 0, 0, time.UTC)}
svc := NewOrderService(pool, fixed)

Node.js

interface ClockService {
  now(): Date;
}

class SystemClock implements ClockService {
  now(): Date { return new Date(); }
}

// In a test (Jest):
const mockClock: ClockService = {
  now: jest.fn().mockReturnValue(new Date('2026-05-07T12:00:00Z')),
};
const service = new OrderService(pool, mockClock);

Python

from typing import Protocol
from datetime import datetime, timezone

class ClockService(Protocol):
    def now(self) -> datetime: ...

class SystemClock:
    def now(self) -> datetime:
        return datetime.now(tz=timezone.utc)

# In a test (pytest):
class FixedClock:
    def now(self) -> datetime:
        return datetime(2026, 5, 7, 12, 0, 0, tzinfo=timezone.utc)

service = OrderService(pool, FixedClock())

In short

• For business time — always timestamptz, never timestamp without a zone.
• timestamptz doesn't store a zone — it stores UTC; the zone is only used on input and output.
• timestamp without a zone is acceptable only for "local time not tied to a moment" (schedules), and then the zone is stored in a separate column.
• In the application, read timestamptz into a UTC type: Instant (Java), time.Time (Go), Date (Node), datetime(tzinfo=UTC) (Python).
• now() returns the transaction start time — use it for created_at; clock_timestamp() — for measurements.
• INTERVAL for offsets: now() - interval '15 minutes', not now() - 900 * interval '1 second'.
• For open-ended records — 'infinity'::timestamptz, not NULL and not 9999-12-31.
• The current time in service code — through a ClockService abstraction, not directly.

What to read next

• Numbers and precision in PostgreSQL — bigint, numeric, money.
• String types — text by default instead of varchar.
• UUID and identifiers — time-sortable UUID v7.
• Type antipatterns — common mistakes when designing a schema.