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

From 03273214f0320e347a0b012763dc82cd91ae6775 Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
Date: Wed, 4 Oct 2023 15:46:38 +0100
Subject: [PATCH v4] 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                 |  7 ++++
 src/backend/utils/adt/ruleutils.c         |  9 +++++
 src/backend/utils/adt/timestamp.c         | 20 ++++++++++
 src/include/catalog/pg_proc.dat           |  6 +++
 src/test/regress/expected/timestamptz.out | 47 +++++++++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 21 ++++++++++
 7 files changed, 123 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1ad64c3d6..6bc61705a9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10619,12 +10619,16 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
    </indexterm>
 
    <indexterm>
     <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
     time stamp <emphasis>with</emphasis> time zone, and
     <type>time with time zone</type> values to different time
     zones. <xref linkend="functions-datetime-zoneconvert-table"/> shows its
@@ -10707,24 +10711,33 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     In the text case, a time zone name can be specified in any of the ways
     described in <xref linkend="datatype-timezones"/>.
     The interval case is only useful for zones that have fixed offsets from
     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>):
 <screen>
 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
 
 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
 
 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>
     setting.  The second example shifts the time stamp with time zone value
     to the specified time zone, and returns the value without a time zone.
     This allows storage and display of values different from the current
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e56cbe77cb..50ed504e5a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -14505,12 +14505,19 @@ a_expr:		c_expr									{ $$ = $1; }
 				{
 					$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
 											   list_make2($5, $1),
 											   COERCE_SQL_SYNTAX,
 											   @2);
 				}
+			| a_expr AT LOCAL						%prec AT
+				{
+					$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
+											   list_make1($1),
+											   COERCE_SQL_SYNTAX,
+											   -1);
+				}
 		/*
 		 * These operators must be called out explicitly in order to make use
 		 * of bison's automatic operator-precedence handling.  All other
 		 * operator names are handled by the generic productions using "Op",
 		 * below; and all those operators will have the same precedence.
 		 *
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 442205382e..9d1b0b13b1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10344,12 +10344,21 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
 			appendStringInfoString(buf, " AT TIME ZONE ");
 			get_rule_expr_paren((Node *) linitial(expr->args), context, false,
 								(Node *) expr);
 			appendStringInfoChar(buf, ')');
 			return true;
 
+		case F_TIMEZONE_TIMESTAMP:
+		case F_TIMEZONE_TIMESTAMPTZ:
+			/* AT LOCAL */
+			appendStringInfoChar(buf, '(');
+			get_rule_expr_paren((Node *) linitial(expr->args), context, false,
+								(Node *) expr);
+			appendStringInfoString(buf, " AT LOCAL)");
+			return true;
+
 		case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
 		case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
 		case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:
 		case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ:
 		case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_INTERVAL:
 		case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_TIMESTAMP:
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0e50aaec5a..e172e90614 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5918,6 +5918,26 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
 
 Datum
 generate_series_timestamptz_at_zone(PG_FUNCTION_ARGS)
 {
 	return generate_series_timestamptz_internal(fcinfo);
 }
+
+/* timestamp_at_local()
+ * timestamptz_at_local()
+ *
+ * The regression tests do not like two functions with the same proargs and
+ * prosrc but different proname, but the grammar for AT LOCAL needs an
+ * overloaded name to handle both types of timestamp, so we make simple
+ * wrappers for it.
+ */
+Datum
+timestamp_at_local(PG_FUNCTION_ARGS)
+{
+	return timestamp_timestamptz(fcinfo);
+}
+
+Datum
+timestamptz_at_local(PG_FUNCTION_ARGS)
+{
+	return timestamptz_timestamp(fcinfo);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f0b7b9cbd8..21645b63a4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2316,12 +2316,18 @@
 { oid => '1158', descr => 'convert UNIX epoch to timestamptz',
   proname => 'to_timestamp', prorettype => 'timestamptz',
   proargtypes => 'float8', prosrc => 'float8_timestamptz' },
 { oid => '1159', descr => 'adjust timestamp to new time zone',
   proname => 'timezone', prorettype => 'timestamp',
   proargtypes => 'text timestamptz', prosrc => 'timestamptz_zone' },
+{ oid => '9159', descr => 'adjust timestamp to local time zone',
+  proname => 'timezone', prorettype => 'timestamp',
+  proargtypes => 'timestamptz', prosrc => 'timestamptz_at_local' },
+{ oid => '9160', descr => 'adjust timestamp to local time zone',
+  proname => 'timezone', prorettype => 'timestamptz',
+  proargtypes => 'timestamp', prosrc => 'timestamp_at_local' },
 
 { oid => '1160', descr => 'I/O',
   proname => 'interval_in', provolatile => 's', prorettype => 'interval',
   proargtypes => 'cstring oid int4', prosrc => 'interval_in' },
 { oid => '1161', descr => 'I/O',
   proname => 'interval_out', provolatile => 's', prorettype => 'cstring',
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..64fdc64a14 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -3132,12 +3132,59 @@ 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';
          timezone         
 --------------------------
  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,
+            timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)),
+            TIMESTAMP '1978-07-07 19:38' AT LOCAL,
+            timezone(TIMESTAMP '1978-07-07 19:38'));
+\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),timezone('Fri Jul 07 16:38:00 1978 PDT'::timestamp with time zone),('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL),timezone('Fri Jul 07 19:38:00 1978'::timestamp without time zone))
+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)
 --
 create temp table tmptz (f1 timestamptz primary key);
 insert into tmptz values ('2017-01-18 00:00+00');
 explain (costs off)
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 69b36d0420..53b5878e21 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -608,12 +608,33 @@ SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 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,
+            timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)),
+            TIMESTAMP '1978-07-07 19:38' AT LOCAL,
+            timezone(TIMESTAMP '1978-07-07 19:38'));
+\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)
 --
 create temp table tmptz (f1 timestamptz primary key);
 insert into tmptz values ('2017-01-18 00:00+00');
 explain (costs off)

base-commit: ffb69b23115a1ca1d81f7e273d50b75154ae7b0b
-- 
2.34.1