My recommendations for sane time zone management in PostgreSQL:
timezone = 'UTC'in
postgresq.conf. This makes UTC the default time zone for all connections.
timestamp with time zone(aka
time with time zone(aka
timetz). They store values as UTC, but convert them on selection to whatever your time zone setting is.
timestamp without time zone(aka
time without time zone(aka
time). These columns do not know the time zone of a value, so different apps can insert values in different zones no one would ever know.
- Always specify a time zone when inserting into a
timetzcolumn. Unless the zone is UTC. But even then, append a “Z” to your value: it’s more explicit, and will keep you sane.
- If you need to get
timetzvalues in a zone other than UTC, use the
AT TIME ZONEexpression in your query. But be aware that the returned value will be a
timevalue, with no more time zone. Good for reporting and queries, bad for storage.
- If your app always needs data in some other time zone, have it
SET timezone = 'UTC'on connection. All values then retrieved from the database will be in the configured time zone. The app should still include the time zone in values sent to the database.
The one exception to the rule preferring
timetz is a special
case: partitioning. When partitioning data on timestamps, you must not use
timestamptz. Why? Because almost no expression involving
comparison is immutable. Use one in a
WHERE clause, and constraint exclusion
may well be ignored and all partitions scanned. This is usually something you
want to avoid.
So in this one case and only in this one case, use a
timestamp without time zone column, but always insert data in UTC. This will
keep things consistent with the
timestamptz columns you have everywhere else
in your database. Unless your app changes the value of the
GUC when it connects, it can just assume that
everything is always UTC, and should always send updates as UTC.
Looking for the comments? Try the old layout.