PostgreSQL TIMESTAMP WITH TIME ZONE: A Practical Guide for Global Services

When building a global service, time handling is not optional — it’s foundational.
A single schema decision (timestamp vs timestamptz) can silently introduce bugs that only surface in production.

This post explains why TIMESTAMP WITH TIME ZONE should be the default choice in PostgreSQL, especially when running on GCP (Cloud Run + Cloud SQL), and how to migrate safely.

TL;DR

• ✅ Always use TIMESTAMP WITH TIME ZONE (timestamptz)
• ❌ Avoid TIMESTAMP WITHOUT TIME ZONE
• Store everything in UTC
• Convert to local time only at the frontend
• Be careful when migrating existing data

PostgreSQL Timestamp Types (What Actually Changes)

PostgreSQL supports two timestamp types:

-- ❌ TIMESTAMP WITHOUT TIME ZONE
created_at TIMESTAMP WITHOUT TIME ZONE
-- Stored as: "2025-12-01 14:00:00"
-- Problem: which timezone is this?

-- ✅ TIMESTAMP WITH TIME ZONE (Recommended)
created_at TIMESTAMP WITH TIME ZONE
-- Internally normalized to UTC
-- Timezone-aware and unambiguous

Why WITHOUT TIME ZONE Is a Footgun

TIMESTAMP WITHOUT TIME ZONE stores a clock value, not a moment in time.

• 2025-12-01 14:00
• Is this New York?
• Los Angeles?
• Seoul?

The database does not know.

TIMESTAMP WITH TIME ZONE always represents an absolute point in time, stored in UTC and safely converted on read.

End-to-End Time Flow on GCP

Scenario

A user in New York (ET, UTC-5) creates a profile at 2:00 PM on Dec 1, 2025.

Browser → Cloud Run → Cloud SQL → Browser

(1) Frontend (Browser)

// User is in New York (UTC-5)
const now = new Date();
// "2025-12-01T14:00:00-05:00"

fetch('/api/v1/profiles', {
  body: JSON.stringify({
    created_at: now.toISOString()
    // "2025-12-01T19:00:00.000Z"
  })
});

Browsers automatically convert local time to UTC when using ISO 8601.

(2) Backend (Cloud Run / Python)

from datetime import datetime, timezone

# Always generate timezone-aware UTC datetimes
now = datetime.now(timezone.utc)

profile = UserProfile(
    created_at=now,
    updated_at=now
)

Rule of thumb:
👉 Backend code should only operate in UTC.

In larger systems, this call is often wrapped in a small utility function
to enforce consistency and simplify testing, but the underlying principle
remains the same.

(3) PostgreSQL (Cloud SQL)

-- Stored internally as UTC
2025-12-01 19:00:00+00

PostgreSQL guarantees consistent storage regardless of client timezone.

(4) Reading with Timezone Conversion

SELECT created_at AT TIME ZONE 'America/New_York';
-- 2025-12-01 14:00:00

SELECT created_at AT TIME ZONE 'America/Los_Angeles';
-- 2025-12-01 11:00:00

No data duplication. No ambiguity.

A Common Production Error

days_since_update = (
    datetime.now(timezone.utc) - profile.updated_at
).days

TypeError: can't subtract offset-naive and offset-aware datetimes

Why This Happens

• datetime.now(timezone.utc) → timezone-aware
• profile.updated_at → timezone-naive
• Database column: TIMESTAMP WITHOUT TIME ZONE

created_at | timestamp without time zone ❌
updated_at | timestamp without time zone ❌

This is not a Python bug — it’s a schema design issue.

References

• PostgreSQL Documentation – Date/Time Types
• PostgreSQL Wiki – Don’t Use TIMESTAMP WITHOUT TIME ZONE
• Python datetime – Aware vs Naive Objects