From 97957fdbaa429c7c582d4753b108cb1e23e1b28a Mon Sep 17 00:00:00 2001
From: Michael Paquier
Date: Fri, 13 Oct 2023 13:01:37 +0900
Subject: Add support for AT LOCAL
When converting a timestamp to/from with/without time zone, the SQL
Standard specifies an AT LOCAL variant of AT TIME ZONE which uses the
session's time zone. This includes three system functions able to do
the work in the same way as the existing flavors for AT TIME ZONE,
except that these need to be marked as stable as they depend on the
session's TimeZone GUC.
Bump catalog version.
Author: Vik Fearing
Reviewed-by: Laurenz Albe, Cary Huang, Michael Paquier
Discussion: https://postgr.es/m/8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org
---
doc/src/sgml/func.sgml | 97 ++++++++++++++++++++++++++++++++++++++++++++++++--
1 file changed, 94 insertions(+), 3 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0769824e46b..affd1254bb7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10611,7 +10611,7 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
- <literal>AT TIME ZONE</literal>
+ <literal>AT TIME ZONE and AT LOCAL</literal>
time zone
@@ -10622,6 +10622,10 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
AT TIME ZONE
+
+ AT LOCAL
+
+
The AT TIME ZONE operator converts time
stamp without time zone to/from
@@ -10632,7 +10636,7 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
- <literal>AT TIME ZONE</literal> Variants
+ <literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants
@@ -10665,6 +10669,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
+
+
+ timestamp without time zone AT LOCAL
+ timestamp with time zone
+
+
+ Converts given time stamp without time zone to
+ time stamp with the session's
+ TimeZone value as time zone.
+
+
+ timestamp '2001-02-16 20:38:40' at local
+ 2001-02-17 03:38:40+00
+
+
+
timestamp with time zone AT TIME ZONE zone
@@ -10681,6 +10701,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
+
+
+ timestamp with time zone AT LOCAL
+ timestamp without time zone
+
+
+ Converts given time stamp with time zone to
+ time stamp without time zone, as the time would
+ appear with the session's TimeZone value as time zone.
+
+
+ timestamp with time zone '2001-02-16 20:38:40-05' at local
+ 2001-02-16 18:38:40
+
+
+
time with time zone AT TIME ZONE zone
@@ -10696,6 +10732,25 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
10:34:17+00
+
+
+
+ time with time zone AT LOCAL
+ time with time zone
+
+
+ Converts given time with time zone to a new time
+ zone. Since no date is supplied, this uses the currently active UTC
+ offset for the session's TimeZone value.
+
+
+ Assuming the session's TimeZone is set to UTC:
+
+
+ time with time zone '05:34:17-05' at local
+ 10:34:17+00
+
+
@@ -10710,6 +10765,13 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
UTC, so it is not very common in practice.
+
+ The syntax AT LOCAL may be used as shorthand for
+ AT TIME ZONE local, where
+ local is the session's
+ TimeZone value.
+
+
Examples (assuming the current setting
is America/Los_Angeles):
@@ -10722,6 +10784,12 @@ SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/D
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40
+
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
+Result: 2001-02-16 17:38:40
+
+SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
+Result: 17:38:40
The first example adds a time zone to a value that lacks it, and
displays the value using the current TimeZone
@@ -10729,7 +10797,18 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'A
to the specified time zone, and returns the value without a time zone.
This allows storage and display of values different from the current
TimeZone setting. The third example converts
- Tokyo time to Chicago time.
+ Tokyo time to Chicago time. The fourth example shifts the time stamp
+ with time zone value to the time zone currently specified by the
+ TimeZone setting and returns the value without a
+ time zone.
+
+
+
+ The fifth example is a cautionary tale. Due to the fact that there is no
+ date associated with the input value, the conversion is made using the
+ current date of the session. Therefore, this static example may show a wrong
+ result depending on the time of the year it is viewed because
+ 'America/Los_Angeles' observes Daylight Savings Time.
@@ -10745,6 +10824,18 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'A
time AT TIME ZONE
zone.
+
+
+ The function timezone(timestamp)
+ is equivalent to the SQL-conforming construct timestamp
+ AT LOCAL.
+
+
+
+ The function timezone(time)
+ is equivalent to the SQL-conforming construct time
+ AT LOCAL.
+
--
cgit v1.2.3