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
timestamptz 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
timestamp GUC when it connects, it can just assume that everything is always UTC, and should always send updates as UTC.