v1-0001-Clarify-the-behavior-of-AT-TIME-ZONE.patch
application/octet-stream
Filename: v1-0001-Clarify-the-behavior-of-AT-TIME-ZONE.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v1-0001
Subject: Clarify the behavior of AT TIME ZONE
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 22 | 0 |
From ad9d3ab17bbd76c641d8beeadd3247eb00f08a0c Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
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 <literal><replaceable>time</replaceable>
AT LOCAL</literal>.
</para>
+
+ <warning>
+ <para>
+ When a time zone with fixed offset from UTC is passed to <literal>AT TIME ZONE</literal>
+ it is treated according to the POSIX rules instead of ISO ones, i.e. positive offset means
+ west of UTC. Examples:
+<screen>
+-- fixed offset, POSIX rules are applied
+SELECT TIMESTAMPTZ '2001-02-16 20:38:40+3' AT TIME ZONE '+03';
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 14:38:40</computeroutput>
+
+-- 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'
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:38:40</computeroutput>
+</screen>
+ If ISO rules are desired one can pass an <literal>INTERVAL</literal>, for instance:
+<screen>
+SELECT TIMESTAMPTZ '2001-02-16 20:38:40+3' AT TIME ZONE INTERVAL '+3h'
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:38:40</computeroutput>
+</screen>
+ </para>
+ </warning>
</sect2>
<sect2 id="functions-datetime-current">
--
2.45.2