From ad9d3ab17bbd76c641d8beeadd3247eb00f08a0c Mon Sep 17 00:00:00 2001 From: Aleksander Alekseev Date: Thu, 1 Aug 2024 12:45:53 +0300 Subject: [PATCH v1] Clarify the behavior of AT TIME ZONE AT TIME ZONE applies either ISO or POSIX rules depending on whether named time zone or fixed offset from UTC is specified. Make sure the documentation is clear about this. Aleksander Alekseev, reported by Chris BSomething Discussion: https://postgr.es/m/CADrHaBFMWsMxGoPBPqY570HSDQZeJ3BtJHhSbUg5LFdErZX6ug%40mail.gmail.com --- doc/src/sgml/func.sgml | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b39f97dc8d..4a804f77bd 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10914,6 +10914,28 @@ SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL; is equivalent to the SQL-conforming construct time AT LOCAL. + + + + When a time zone with fixed offset from UTC is passed to AT TIME ZONE + it is treated according to the POSIX rules instead of ISO ones, i.e. positive offset means + west of UTC. Examples: + +-- fixed offset, POSIX rules are applied +SELECT TIMESTAMPTZ '2001-02-16 20:38:40+3' AT TIME ZONE '+03'; +Result: 2001-02-16 14:38:40 + +-- named time zone, ISO rules are applied; Europe/Moscow = UTC+3 +SELECT TIMESTAMPTZ '2001-02-16 20:38:40+3' AT TIME ZONE 'Europe/Moscow' +Result: 2001-02-16 20:38:40 + + If ISO rules are desired one can pass an INTERVAL, for instance: + +SELECT TIMESTAMPTZ '2001-02-16 20:38:40+3' AT TIME ZONE INTERVAL '+3h' +Result: 2001-02-16 20:38:40 + + + -- 2.45.2