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.

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

Imagine you store the string '2026-05-07 14:00:00'. What is it — 14:00 in UTC, in Moscow, in the application's zone, in the server's zone? PostgreSQL doesn't know: it stores these digits literally, with no 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 arrives from servers and clients in different zones, the values get mixed up: '2026-05-07 12:00:00' from a UTC server and from a Moscow client are different moments, yet 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 converts the value to UTC by the session's time zone and stores it as microseconds from a fixed point (inside PostgreSQL that is midnight on 1 January 2000, not the Unix epoch).
• On read: PostgreSQL takes the UTC value and converts it to the session's time zone.

An important consequence: timestamptz doesn't store a zone — it stores UTC; the zone is used only 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.

In practice:

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 convert an existing column

If a timestamp column already holds data, say explicitly which zone the digits were written in — otherwise PostgreSQL takes the session zone and shifts the data:

ALTER TABLE "order"
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'Europe/Moscow';

If the server moved zones, split the rows by the move date and convert each part with its own zone. On a large table ALTER … TYPE rewrites the data under a lock — add a new column and move rows in batches.

How to read timestamptz in the application

The driver hands a timestamptz to the application in UTC; the application's job is to keep it in a type that understands zones, not as "local" time.

A typical mistake: the code puts that UTC value into a type without a zone. On a server with TZ=UTC it works; 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) {}

The cost shows up without a database: an order at 12:00 in Moscow and one at 12:00 in UTC are different moments, yet look identical as LocalDateTime:

live example

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;

public class InstantVsLocal {
    public static void main(String[] args) {
        Instant fromMoscowClient = Instant.parse("2026-05-07T09:00:00Z");
        Instant fromUtcServer = Instant.parse("2026-05-07T12:00:00Z");

        LocalDateTime a = LocalDateTime.ofInstant(fromMoscowClient, ZoneId.of("Europe/Moscow"));
        LocalDateTime b = LocalDateTime.ofInstant(fromUtcServer, ZoneId.of("UTC"));

        System.out.println("as LocalDateTime: " + a + " and " + b);
        System.out.println("look equal:  " + a.isEqual(b));
        System.out.println("same moment: " + fromMoscowClient.equals(fromUtcServer));
    }
}

With the right configuration, jOOQ maps timestamptz to Instant.

Go

// pgx v5: timestamptz → time.Time — the moment from the database
type OrderEventRow struct {
    ID         int64
    OccurredAt time.Time
}

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() — print in UTC, not in the machine zone

Node.js

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

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

In the pg driver date comes back as a Date at local midnight, so in another zone the calendar date shifts by a day; when you need the date itself, register a parser for type 1082 that keeps it a string.

Python

# psycopg v3: timestamptz → datetime with tzinfo
from datetime import datetime
import psycopg

with psycopg.connect(dsn) as conn:
    row = conn.execute(
        "SELECT id, occurred_at FROM order_event WHERE id = %s", (row_id,)
    ).fetchone()
    occurred_at: datetime = row[1]  # aware datetime

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

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":

• 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 lives in a separate column and the application converts when needed. For everything else — timestamptz.

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

PostgreSQL has several functions for the current time, and they differ:

For created_at and updated_at, use now(): all rows inserted in one transaction get the same timestamp, which shows they appeared together.

created_at timestamptz NOT NULL DEFAULT now()

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

INTERVAL for time offsets

For 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 handles daylight saving time and the varying length of months. Leap seconds PostgreSQL doesn't know at all: in its calendar a day always has exactly 86,400 seconds.

+infinity for open-ended records

PostgreSQL has 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 you have to handle separately.
• infinity states the intent and works with arithmetic and indexes.

Testability: don't call the clock directly

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

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

Java

live example

import java.time.Instant;

public class FrozenClock {
    interface DateTimeService {
        Instant now();
    }

    static boolean isActive(Instant expiresAt, DateTimeService time) {
        return expiresAt.isAfter(time.now());
    }

    public static void main(String[] args) {
        DateTimeService system = Instant::now;
        DateTimeService frozen = () -> Instant.parse("2026-05-07T12:00:00Z");

        Instant expiresAt = Instant.parse("2026-05-07T18:00:00Z");
        System.out.println("on a frozen clock: " + isActive(expiresAt, frozen));
        System.out.println("on a system clock: " + isActive(expiresAt, system));
    }
}

On the frozen clock the answer is the same on every run; on the system clock it depends on the day the test ran. In the application it is a plain bean; the test puts a fixed clock in its place.

Go

live example

package main

import (
	"fmt"
	"time"
)

type ClockService interface {
	Now() time.Time
}

type SystemClock struct{}

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

type FixedClock struct{ t time.Time }

func (f FixedClock) Now() time.Time { return f.t }

func main() {
	expiresAt := time.Date(2026, 5, 7, 18, 0, 0, 0, time.UTC)
	fixed := FixedClock{t: time.Date(2026, 5, 7, 12, 0, 0, 0, time.UTC)}

	fmt.Println("on a frozen clock:", expiresAt.After(fixed.Now()))
	fmt.Println("on a system clock:", expiresAt.After(SystemClock{}.Now()))
}

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

live example

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)

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

expires_at = datetime(2026, 5, 7, 18, 0, tzinfo=timezone.utc)
print("on a frozen clock:", expires_at > FixedClock().now())
print("on a system clock:", expires_at > SystemClock().now())

In short

• For business time — always timestamptz; timestamp without a zone stores digits with no meaning.
• timestamptz doesn't store a zone — it stores a moment in UTC; the zone is only used on input and output.
• timestamp without a zone is justified only for "local time not tied to a moment" (schedules), and the zone then lives in a separate column.
• In the application the moment goes into a type with a zone: Instant (Java), time.Time (Go), Date (Node), an aware datetime (Python); LocalDateTime and a naive datetime lose the meaning.
• now() is the transaction start time (for created_at), clock_timestamp() is the actual moment of the call (for measurements); offsets go through INTERVAL.
• Open-ended means 'infinity'::timestamptz, not NULL and not 9999-12-31; the current time in code goes through a ClockService, otherwise the test depends on the day it runs.

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.