bwi

Posted on Jan 22

PostgreSQL – Storing Time Without Lying to Yourself

#database #postgres #sql #tutorial

Part 7 of 8 in the series Time in Software, Done Right

You've modeled time correctly in your application. You have LocalDateTime, TimeZoneId, and Instant. Now you need to persist it.

PostgreSQL has excellent time support — but its type names are misleading, and the default behaviors can surprise you. This article explains what PostgreSQL actually does, which types to use, and how to avoid the common traps.

The Two Timestamp Types

PostgreSQL has two timestamp types:

timestamp without time zone (or just timestamp)
timestamp with time zone (or timestamptz)

The names suggest one stores a timezone and one doesn't. That's not quite right.

timestamp without time zone

This stores exactly what you give it — a date and time with no timezone context.

INSERT INTO test (ts) VALUES ('2026-06-05 10:00:00');
SELECT ts FROM test;
-- Result: 2026-06-05 10:00:00

No conversion happens. No timezone is stored. It's just a calendar value.

Use for: LocalDateTime — when you're storing what the user said, not when it happened globally.

timestamp with time zone

This is where the name lies. PostgreSQL does not store a timezone. It converts the input to UTC and stores UTC internally. On retrieval, it converts back to the session's timezone.

SET timezone = 'Europe/Vienna';
INSERT INTO test (tstz) VALUES ('2026-06-05 10:00:00');

SET timezone = 'Europe/London';
SELECT tstz FROM test;
-- Result: 2026-06-05 04:00:00-04

Same row, different display — because PostgreSQL stored UTC internally and converted on output.

Use for: Instant — when you're storing a global moment.

What PostgreSQL Actually Stores

Let's be precise:

Type	What's Stored	What Happens on Insert	What Happens on Select
`timestamp`	Raw datetime	Nothing	Nothing
`timestamptz`	Instant (normalized to UTC)	Converts input to UTC	Converts UTC to session timezone

The critical insight: timestamptz stores UTC, not a timezone. The "with time zone" means "timezone-aware" — it participates in timezone conversions. It doesn't mean "includes a timezone."

The Session Timezone Trap

With timestamptz, PostgreSQL uses your session's timezone for conversions:

SET timezone = 'UTC';
INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00');
-- Stored as: 2026-06-05 10:00:00 UTC

SET timezone = 'Europe/Vienna';
INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00');
-- Stored as: 2026-06-05 08:00:00 UTC (Vienna is UTC+2 in summer)

Same literal, different stored value — because PostgreSQL assumed the input was in the session timezone.

Best practice: Always use explicit UTC or offsets when inserting into timestamptz:

INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00+00');  -- Explicit UTC
INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00Z');    -- Also UTC
INSERT INTO events (instant_utc) VALUES ('2026-06-05 12:00:00+02');  -- Explicit offset

Or set your application's session timezone to UTC and keep it there.

The Recommended Schema

For human-scheduled events (meetings, deadlines, appointments), use the pattern from earlier articles:

CREATE TABLE appointments (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    title           text NOT NULL,

    -- Source of truth: what the user chose
    local_start     timestamp NOT NULL,
    time_zone_id    text NOT NULL,

    -- Derived: for global queries and sorting
    instant_utc     timestamptz NOT NULL
);

Why three columns?

local_start — The user said "10:00". That's the intent.
time_zone_id — The user meant "Vienna". That's the context.
instant_utc — For queries like "what's happening now?" and for sorting.

If timezone rules change, you recalculate instant_utc from local_start + time_zone_id.

Choosing the Right Type for Each Concept

Concept	PostgreSQL Type	Example
Instant / UTC moment	`timestamptz`	Log timestamp, `created_at`
Local datetime (user intent)	`timestamp`	Meeting time, deadline
Date only	`date`	Birthday, holiday
Time only	`time`	Opening hours
IANA timezone ID	`text`	`'Europe/Vienna'`

Querying Patterns

Pattern A: "What's on my calendar on June 5th in Vienna?"

Query by local date + timezone:

SELECT *
FROM appointments
WHERE time_zone_id = 'Europe/Vienna'
  AND local_start >= '2026-06-05'
  AND local_start <  '2026-06-06';

This finds all appointments that display as June 5th in Vienna, regardless of the global instant.

Pattern B: "What's happening globally in the next hour?"

Query by instant:

SELECT *
FROM appointments
WHERE instant_utc >= NOW()
  AND instant_utc <  NOW() + INTERVAL '1 hour';

This finds all appointments happening in the next hour, regardless of their local calendars.

Pattern C: "What's happening at 10:00 in any timezone?"

Query by local time (rare but sometimes needed):

SELECT *
FROM appointments
WHERE local_start::time = '10:00:00';

Indexing Strategies

For instant queries (most common)

CREATE INDEX idx_appointments_instant ON appointments (instant_utc);

This covers "what's happening now" and range queries across timezones.

For local calendar queries

CREATE INDEX idx_appointments_local ON appointments (time_zone_id, local_start);

This covers "what's on the calendar for this timezone" queries. The timezone comes first because you'll almost always filter by it.

For both

If you query both ways heavily, create both indexes. The storage cost is usually worth it.

Common Mistakes

Mistake 1: Using timestamptz for user intent

-- DON'T: Storing a meeting time as timestamptz
INSERT INTO meetings (starts_at) VALUES ('2026-06-05 10:00:00');

You've lost the "10:00" intent. If timezone rules change, you can't recover it.

Mistake 2: Using timestamp for log timestamps

-- DON'T: Storing a log timestamp without timezone
INSERT INTO logs (occurred_at) VALUES ('2026-06-05 10:00:00');

Is that UTC? Server time? You don't know. Use timestamptz and be explicit.

Mistake 3: Trusting session timezone

-- DON'T: Assuming session timezone is what you expect
INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00');

What timezone was the session in? Be explicit: '2026-06-05 10:00:00+00'.

Mistake 4: Storing timezone names in timestamptz

-- DON'T: Thinking this stores "Vienna"
INSERT INTO events (instant_utc) VALUES ('2026-06-05 10:00:00 Europe/Vienna');

PostgreSQL converts to UTC immediately. The string 'Europe/Vienna' is gone. If you need the timezone, store it separately.

AT TIME ZONE: The Conversion Operator

PostgreSQL's AT TIME ZONE converts between timestamps and timezones:

-- timestamptz → timestamp in a specific zone
SELECT instant_utc AT TIME ZONE 'Europe/Vienna' AS local_vienna
FROM appointments;

-- timestamp → timestamptz (interpreting as a specific zone)
SELECT local_start AT TIME ZONE time_zone_id AS instant
FROM appointments;

This is useful for display and for reconstructing the instant from stored local + timezone.

Gotcha: The behavior differs based on the input type:

timestamptz AT TIME ZONE 'X' → returns timestamp (strips timezone, shows in X)
timestamp AT TIME ZONE 'X' → returns timestamptz (interprets as X, converts to UTC)

Handling Timezone Rule Changes

When IANA updates timezone rules:

Past events: Nothing to do. PostgreSQL's timestamptz already stores UTC. Historical conversions use historical rules (if your system's tzdata is updated).
Future events: Recalculate instant_utc from local_start + time_zone_id.

-- Recalculate instant_utc for future Vienna appointments
UPDATE appointments
SET instant_utc = local_start AT TIME ZONE time_zone_id
WHERE time_zone_id = 'Europe/Vienna'
  AND instant_utc > NOW();

This is why storing local_start + time_zone_id matters — you have everything needed to recalculate.

Working with EF Core and Npgsql

If you're using .NET with Npgsql, the mapping is straightforward:

// In your DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql(connectionString, o => o.UseNodaTime());
}

With UseNodaTime():

Instant ↔ timestamptz
LocalDateTime ↔ timestamp
LocalDate ↔ date
LocalTime ↔ time

The types align naturally with our model.

Full Example: Creating and Querying Appointments

-- gen_random_uuid() requires this extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create table
CREATE TABLE appointments (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    title           text NOT NULL,
    local_start     timestamp NOT NULL,
    time_zone_id    text NOT NULL,
    instant_utc     timestamptz NOT NULL
);

-- Create indexes
CREATE INDEX idx_appointments_instant ON appointments (instant_utc);
CREATE INDEX idx_appointments_local ON appointments (time_zone_id, local_start);

-- Insert an appointment (10:00 Vienna = 08:00 UTC in summer)
INSERT INTO appointments (title, local_start, time_zone_id, instant_utc)
VALUES (
    'Team Standup',
    '2026-06-05 10:00:00',
    'Europe/Vienna',
    '2026-06-05 10:00:00' AT TIME ZONE 'Europe/Vienna'
);

-- Query: What's on the Vienna calendar for June 5th?
SELECT title, local_start
FROM appointments
WHERE time_zone_id = 'Europe/Vienna'
  AND local_start >= '2026-06-05'
  AND local_start <  '2026-06-06';

-- Query: What's happening globally in the next 2 hours?
SELECT title, instant_utc, time_zone_id
FROM appointments
WHERE instant_utc >= NOW()
  AND instant_utc <  NOW() + INTERVAL '2 hours';

-- Display in viewer's timezone
SELECT 
    title,
    instant_utc AT TIME ZONE 'Europe/London' AS starts_at_london
FROM appointments;

A Note on ORMs, Query Builders, and Event Stores

The PostgreSQL model described here — storing local_start, time_zone_id, and a derived instant_utc — is independent of how you access the database.

EF Core / Npgsql: Works well with explicit mappings (see Article 6 for full NodaTime integration).
Dapper: Maps naturally to simple columns; you compute instants in application code.
Marten / Event Sourcing: Events typically store an Instant (occurred_at) plus domain-specific local values when needed.
Raw SQL: The same rules apply — PostgreSQL doesn't care how the data got there.

The key idea is not the ORM — it's the data model.

If you store human intent (local + timezone) separately from physical moments (instant), the approach works across tools, frameworks, and architectural styles.

Key Takeaways

timestamp stores raw datetime — use for LocalDateTime (user intent)
timestamptz converts to/from UTC — use for Instant (global moments)
timestamptz does not store a timezone — it stores UTC and converts on read
For human-scheduled events: store local_start + time_zone_id + instant_utc
Be explicit with timezones on insert — don't trust session settings
Index instant_utc for global queries, index (time_zone_id, local_start) for calendar queries
When rules change: recalculate instant_utc from the stored local + timezone

Next up: Frontend – Temporal, APIs, and DateTimePickers That Don't Lie — bringing it all together in the browser.

DEV Community