v2-0001-Add-support-for-AT-LOCAL.patch

From 176c59bec6c6e91c3491a05e33a2203e25e0ce3e Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
Date: Mon, 5 Jun 2023 19:42:42 -0400
Subject: [PATCH v2] 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.
---
 doc/src/sgml/func.sgml                    | 13 ++++++
 src/backend/parser/gram.y                 | 12 +++++
 src/backend/utils/adt/ruleutils.c         | 56 +++++++++++++++++++----
 src/test/regress/expected/timestamptz.out | 47 +++++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 21 +++++++++
 5 files changed, 140 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..6d07f063e0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10557,6 +10557,10 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     <primary>AT TIME ZONE</primary>
    </indexterm>
 
+   <indexterm>
+    <primary>AT LOCAL</primary>
+   </indexterm>
+
    <para>
     The <literal>AT TIME ZONE</literal> operator converts time
     stamp <emphasis>without</emphasis> time zone to/from
@@ -10645,6 +10649,12 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     UTC, so it is not very common in practice.
    </para>
 
+   <para>
+    The syntax <literal>AT LOCAL</literal> may be used as shorthand for <literal>AT TIME ZONE
+    <replaceable>local</replaceable></literal>, where <replaceable>local</replaceable> is the
+    session's <varname>TimeZone</varname> value.
+   </para>
+
    <para>
     Examples (assuming the current <xref linkend="guc-timezone"/> setting
     is <literal>America/Los_Angeles</literal>):
@@ -10657,6 +10667,9 @@ 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';
 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
+
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>
 </screen>
     The first example adds a time zone to a value that lacks it, and
     displays the value using the current <varname>TimeZone</varname>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 39ab7eac0d..2b27904970 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -14418,6 +14418,18 @@ a_expr:		c_expr									{ $$ = $1; }
 											   COERCE_SQL_SYNTAX,
 											   @2);
 				}
+			| a_expr AT LOCAL						%prec AT
+				{
+					/* Use the value of the session's time zone */
+					FuncCall *tz = makeFuncCall(SystemFuncName("current_setting"),
+												list_make1(makeStringConst("TimeZone", -1)),
+												COERCE_SQL_SYNTAX,
+												-1);
+					$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
+											   list_make2(tz, $1),
+											   COERCE_SQL_SYNTAX,
+											   @2);
+				}
 		/*
 		 * These operators must be called out explicitly in order to make use
 		 * of bison's automatic operator-precedence handling.  All other
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d3a973d86b..5cfd23fc7d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10316,15 +10316,53 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
 		case F_TIMEZONE_TEXT_TIMESTAMP:
 		case F_TIMEZONE_TEXT_TIMESTAMPTZ:
 		case F_TIMEZONE_TEXT_TIMETZ:
-			/* AT TIME ZONE ... note reversed argument order */
-			appendStringInfoChar(buf, '(');
-			get_rule_expr_paren((Node *) lsecond(expr->args), context, false,
-								(Node *) expr);
-			appendStringInfoString(buf, " AT TIME ZONE ");
-			get_rule_expr_paren((Node *) linitial(expr->args), context, false,
-								(Node *) expr);
-			appendStringInfoChar(buf, ')');
-			return true;
+			{
+				/* AT TIME ZONE ... note reversed argument order */
+				Node   *ts = (Node *) lsecond(expr->args);
+				Node   *zone = (Node *) linitial(expr->args);
+
+				/*
+				 * If the time zone is a function call, look to see if this is
+				 * literally current_setting('TimeZone') and that we should
+				 * coerce it to SQL, in which case we need to use "AT LOCAL".
+				 */
+				bool	islocal = false;
+
+				/* Is it a function? */
+				if (IsA(zone, FuncExpr))
+				{
+					FuncExpr *func = castNode(FuncExpr, zone);
+
+					/*
+					 * Is it current_setting() with a constant argument that
+					 * should be coerced to SQL?
+					 */
+					if (func->funcid == F_CURRENT_SETTING_TEXT &&
+						func->funcformat == COERCE_SQL_SYNTAX &&
+						IsA(linitial(func->args), Const))
+					{
+						Const   *con = castNode(Const, linitial(func->args));
+
+						Assert(con->consttype == TEXTOID && !con->constisnull);
+
+						/* Is that argument TimeZone? */
+						if (pg_strcasecmp(TextDatumGetCString(con->constvalue), "TimeZone") == 0)
+							islocal = true;
+					}
+				}
+
+				appendStringInfoChar(buf, '(');
+				get_rule_expr_paren(ts, context, false, (Node *) expr);
+				if (islocal)
+					appendStringInfoString(buf, " AT LOCAL");
+				else
+				{
+					appendStringInfoString(buf, " AT TIME ZONE ");
+					get_rule_expr_paren(zone, context, false, (Node *) expr);
+				}
+				appendStringInfoChar(buf, ')');
+				return true;
+			}
 
 		case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
 		case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..ae55e63077 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -3135,6 +3135,53 @@ SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
  Sun Oct 26 02:00:00 2014
 (1 row)
 
+--
+-- Test LOCAL time zone
+--
+BEGIN;
+SET LOCAL TIME ZONE 'Europe/Paris';
+VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
+         column1          
+--------------------------
+ Sat Jul 08 01:38:00 1978
+(1 row)
+
+VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
+            column1            
+-------------------------------
+ Fri Jul 07 19:38:00 1978 CEST
+(1 row)
+
+SET LOCAL TIME ZONE 'Australia/Sydney';
+VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
+         column1          
+--------------------------
+ Sat Jul 08 09:38:00 1978
+(1 row)
+
+VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
+            column1            
+-------------------------------
+ Fri Jul 07 19:38:00 1978 AEST
+(1 row)
+
+RESET TIME ZONE;
+CREATE VIEW local_time_zone AS
+    VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL,
+            CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE current_setting('TimeZone'),
+            TIMESTAMP '1978-07-07 19:38' AT LOCAL,
+            TIMESTAMP '1978-07-07 19:38' AT TIME ZONE current_setting('TimeZone'));
+\sv local_time_zone
+CREATE OR REPLACE VIEW public.local_time_zone AS
+ VALUES (('Fri Jul 07 16:38:00 1978 PDT'::timestamp with time zone AT LOCAL),('Fri Jul 07 16:38:00 1978 PDT'::timestamp with time zone AT TIME ZONE current_setting('TimeZone'::text)),('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL),('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT TIME ZONE current_setting('TimeZone'::text)))
+TABLE local_time_zone;
+         column1          |         column2          |           column3            |           column4            
+--------------------------+--------------------------+------------------------------+------------------------------
+ Fri Jul 07 16:38:00 1978 | Fri Jul 07 16:38:00 1978 | Fri Jul 07 19:38:00 1978 PDT | Fri Jul 07 19:38:00 1978 PDT
+(1 row)
+
+DROP VIEW local_time_zone;
+COMMIT;
 --
 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
 --
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 69b36d0420..11d9f05b64 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -611,6 +611,27 @@ SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 
+--
+-- Test LOCAL time zone
+--
+BEGIN;
+SET LOCAL TIME ZONE 'Europe/Paris';
+VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
+VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
+SET LOCAL TIME ZONE 'Australia/Sydney';
+VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
+VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
+RESET TIME ZONE;
+CREATE VIEW local_time_zone AS
+    VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL,
+            CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE current_setting('TimeZone'),
+            TIMESTAMP '1978-07-07 19:38' AT LOCAL,
+            TIMESTAMP '1978-07-07 19:38' AT TIME ZONE current_setting('TimeZone'));
+\sv local_time_zone
+TABLE local_time_zone;
+DROP VIEW local_time_zone;
+COMMIT;
+
 --
 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
 --
-- 
2.32.1 (Apple Git-133)