prefer-timestamptz

problem#

When Postgres stores a datetime in a timestamp field, Postgres drops the UTC offset. This means 2019-10-11 21:11:24+02 and 2019-10-11 21:11:24-06 will both be stored as 2019-10-11 21:11:24 in the database, even though they are eight hours apart in time.

solution#

Use a timestamptz field to ensure Postgres returns your timestamp with the correct UTC offset.

create table#

Instead of:

create table app.users
(
    created_ts   timestamp,
    modified_ts  timestamp without time zone
)

Use:

create table app.users
(
    created_ts   timestamptz,
    modified_ts  timestamptz
)

alter table#

Instead of:

alter table app.users
    alter column created_ts type timestamp,
    alter column modified_ts type timestamp without time zone

Use:

alter table app.users
    alter column created_ts type timestamptz,
    alter column modified_ts type timestamptz

solution for alembic and sqlalchemy#

Instead of:

# models.py
import sqlalchemy as sa

class User(BaseModel):
    created_ts = sa.Column(sa.DateTime)
    modified_ts = sa.Column(sa.DateTime)

Use:

# models.py
import sqlalchemy as sa

class User(BaseModel):
    created_ts = sa.Column(sa.DateTime(timezone=True))
    modified_ts = sa.Column(sa.DateTime(timezone=True))

problem#

solution#

create table#

alter table#

solution for alembic and sqlalchemy#

links#