Lessons Learned: Handling DateTime Across Time Zones in Postgres with SQLAlchemy and Alembic

Working with date and time in a global application is tricky. When you throw in multiple time zones, daylight saving time (DST), and different database behaviors, things can get messy quickly.

Here’s what I learned while working with Python, SQLAlchemy, Alembic, and PostgreSQL.

Lesson 1 — Always Use TIMESTAMP WITH TIME ZONE in Postgres

In PostgreSQL, there are two main timestamp types:

  • TIMESTAMP WITHOUT TIME ZONE (timestamp)
  • TIMESTAMP WITH TIME ZONE (timestamptz)

Rule: Use timestamptz for anything that needs to represent a real-world moment in time.

Why?

  • Postgres will store it in UTC internally.
  • When you query, Postgres will convert it to your session’s time zone automatically.
  • You never lose the actual point in time — you can always display it in the user’s local time.
from sqlalchemy import DateTime
from sqlalchemy.orm import Mapped, mapped_column

class Meeting(Base):
    __tablename__ = "meetings"

    id: Mapped[int] = mapped_column(primary_key=True)
    topic: Mapped[str]
    start_time: Mapped[pendulum.DateTime] = mapped_column(
        DateTime(timezone=True),  # Maps to timestamptz
        nullable=False
    )

Alembic migration

def upgrade():
    op.create_table(
        "meetings",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("topic", sa.Text),
        sa.Column("start_time", sa.DateTime(timezone=True), nullable=False)
    )

Lesson 2 — Store All Times in UTC, Convert on Display

Even if your app deals with local times (e.g., Sydney time), store them in UTC.
With timestamptz, Postgres handles the conversion for you.

import pendulum
from sqlalchemy.orm import Session

start_time = pendulum.datetime(2025, 8, 13, 9, 0, tz="Australia/Sydney")

with Session(engine) as session:
    session.add(Meeting(topic="Daily Standup", start_time=start_time))
    session.commit()

Postgres will store it as 2025-08-12 23:00:00+00 internally but can display it in any time zone:

SET TIME ZONE 'UTC';
SELECT start_time FROM meetings;
-- 2025-08-12 23:00:00+00

SET TIME ZONE 'Australia/Sydney';
SELECT start_time FROM meetings;
-- 2025-08-13 09:00:00+10

Lesson 3 — Be Careful with default vs server_default

When defining a timestamp column, you might be tempted to do:

created_at: Mapped[pendulum.DateTime] = mapped_column(
    DateTime(timezone=True),
    default=pendulum.now
)

This works, but note:

  • default runs in Python — the timestamp is generated in your application process.
  • It uses your app server’s local time zone unless you explicitly use UTC or a specific zone.

If you want the timestamp to be generated by Postgres, use server_default:


from sqlalchemy import text

created_at: Mapped[pendulum.DateTime] = mapped_column(
    DateTime(timezone=True),
    server_default=text("CURRENT_TIMESTAMP")
)

Why choose server_default?

  • The time is generated inside Postgres, using its configured time zone.
  • Works even if you insert data from outside your Python app (e.g., via psql or another service).
  • Keeps a consistent source of truth for default timestamps.

Alembic example

def upgrade():
    op.add_column(
        "meetings",
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False
        )
    )

Lesson 4 — Always Use Time Zone-Aware Objects in Python

If you try to insert a naive datetime (no time zone info) into a timestamptz column, SQLAlchemy may interpret it using your system’s local time zone, which can cause confusion.

# ❌ Naive datetime — dangerous
meeting.start_time = datetime(2025, 8, 13, 9, 0)

# ✅ Time zone aware datetime
meeting.start_time = pendulum.datetime(2025, 8, 13, 9, 0, tz="Australia/Sydney")

With libraries like Pendulum or zoneinfo, you can make sure your datetime always carries the correct time zone.

Final Thoughts

When working with Postgres + SQLAlchemy + Alembic:

  1. Always use timestamptz (DateTime(timezone=True) in SQLAlchemy).
  2. Store in UTC, display in local time.
  3. Use server_default for database-driven timestamps.
  4. Make all Python datetimes time zone aware.
  5. Let Postgres do the time zone conversions.

This approach has saved me from DST bugs, data inconsistency, and “why is my meeting one hour off?” headaches.

Share: