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

links#