Postgres AT TIME ZONE 'UTC' does NOT do what you think it does

If you use `AT TIME ZONE 'UTC'` on timestamps without explicit zone information, your data may be incorrectly shifted by your server's local time offset.

The PostgreSQL database system's `AT TIME ZONE 'UTC'` function may not behave as developers intuitively expect when handling timestamps without explicit time zone information. When a timestamp literal is provided without an associated time zone, PostgreSQL defaults to treating it as if it were in the server's local time zone. Consequently, applying `AT TIME ZONE 'UTC'` to such a timestamp converts it from the server's local time zone to UTC, rather than interpreting it as a UTC timestamp and leaving it unchanged. This can lead to unexpected data transformations and potential errors if developers assume it preserves UTC values directly.

This subtle behavior in PostgreSQL highlights a common pitfall in timestamp handling, particularly for applications that operate across different geographical regions or require strict adherence to UTC. Developers often assume that `AT TIME ZONE 'UTC'` acts as a simple interpreter, treating the input as UTC and outputting it as UTC. However, PostgreSQL's default assumption of the server's local time zone for untagged timestamps means this function performs a conversion, potentially introducing an offset if the server is not in UTC.

The implication for developers is the need for explicit timestamp qualification. To ensure timestamps are correctly interpreted as UTC, they should be cast to the `timestamptz` (timestamp with time zone) type with an explicit UTC designation, or the `SET TIME ZONE 'UTC'` session command should be used before processing such values. Understanding this distinction is critical for maintaining data integrity and preventing subtle bugs in time-sensitive applications or global systems.