Footguns with Postgres "at time zone 'UTC'"
And why you may need to repeat AT TIME ZONE 'UTC' twice.
SUMMARY
* AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp (example).
* Now you are inadvertently using the timestamp without time zone (aka timestamp) data type, which is markedly discouraged.
* There are tons of footguns with timestamp.
* For example, the equality of timestamp and timestamptz will always be false.
* Adding a month with + INTERVAL '1 months' is timezone-dependent. If your product
SUMMARY
* AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp (example).
* Now you are inadvertently using the timestamp without time zone (aka timestamp) data type, which is markedly discouraged.
* There are tons of footguns with timestamp.
* For example, the equality of timestamp and timestamptz will always be false.
* Adding a month with + INTERVAL '1 months' is timezone-dependent. If your product
Summary
Using the AT TIME ZONE ‘UTC’ command in Postgres can lead to unexpected behavior, particularly when it converts timestamps in ways that may not be immediately clear. This piece highlights common pitfalls and explains why you might need to apply the command twice to avoid complications with data types.
Comments (92 of ) ⌛