Add support for AT LOCAL

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)

From e68305dafd2d4f9439cbd341e8f04745d8a945ed Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
Date: Wed, 4 Oct 2023 15:46:38 +0100
Subject: [PATCH v3] 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..ae55e63077 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,
+            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)
 --
 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..11d9f05b64 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,
+            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)
 --
 create temp table tmptz (f1 timestamptz primary key);
 insert into tmptz values ('2017-01-18 00:00+00');
 explain (costs off)

base-commit: af2324fabf0020e464b0268be9ef03e8f46ed84b
-- 
2.34.1

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

From e136d38967a863452762e1ee6e28d9ab40056adf Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 10 Oct 2023 13:28:46 +0900
Subject: [PATCH v5] Add support for AT LOCAL with timestamps

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.

Note: bump catalog version
---
 src/include/catalog/pg_proc.dat           |  6 +++
 src/backend/parser/gram.y                 |  7 +++
 src/backend/utils/adt/ruleutils.c         |  9 ++++
 src/backend/utils/adt/timestamp.c         | 20 ++++++++
 src/test/regress/expected/timestamptz.out | 47 +++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 21 ++++++++
 doc/src/sgml/func.sgml                    | 62 +++++++++++++++++++++--
 7 files changed, 169 insertions(+), 3 deletions(-)

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
@@ -2319,6 +2319,12 @@
 { 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',
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
@@ -14508,6 +14508,13 @@ a_expr:		c_expr									{ $$ = $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
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
@@ -10347,6 +10347,15 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
 			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:
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0e50aaec5a..2378ad7269 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5921,3 +5921,23 @@ generate_series_timestamptz_at_zone(PG_FUNCTION_ARGS)
 {
 	return generate_series_timestamptz_internal(fcinfo);
 }
+
+/* timestamp_at_local()
+ * timestamptz_at_local()
+ *
+ * These are necessary to pass the sanity check in opr_sanity, which checks
+ * that all built-in functions that share the implementing C function take
+ * the same number of arguments, and the grammar for AT LOCAL needs an
+ * overloaded name to handle both types of timestamp.
+ */
+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/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..780de18cf9 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 AT LOCAL
+--
+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)
 --
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 69b36d0420..86b83adf68 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 AT LOCAL
+--
+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)
 --
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1ad64c3d6..8047918d0e 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
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
-   <title><literal>AT TIME ZONE</literal></title>
+   <title><literal>AT TIME ZONE and AT LOCAL</literal></title>
 
    <indexterm>
     <primary>time zone</primary>
@@ -10622,6 +10622,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
@@ -10632,7 +10636,7 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
    </para>
 
     <table id="functions-datetime-zoneconvert-table">
-     <title><literal>AT TIME ZONE</literal> Variants</title>
+     <title><literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants</title>
      <tgroup cols="1">
       <thead>
        <row>
@@ -10665,6 +10669,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <type>timestamp without time zone</type> <literal>AT LOCAL</literal>
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Converts given time stamp <emphasis>without</emphasis> time zone to
+         time stamp <emphasis>with</emphasis> the session's
+         <varname>TimeZone</varname> value as time zone.
+        </para>
+        <para>
+         <literal>timestamp '2001-02-16 20:38:40' at local</literal>
+         <returnvalue>2001-02-17 03:38:40+00</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
@@ -10681,6 +10701,23 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <type>timestamp with time zone</type> <literal>AT LOCAL</literal>
+         <returnvalue>timestamp without time zone</returnvalue>
+        </para>
+        <para>
+         Converts given time stamp <emphasis>with</emphasis> time zone to
+         time stamp <emphasis>without</emphasis> time zone, as the time would
+         appear <emphasis>with</emphasis> the session's <varname>TimeZone</varname>
+         value as time zone.
+        </para>
+        <para>
+         <literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>
+         <returnvalue>2001-02-16 18:38:40</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
@@ -10710,6 +10747,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.
    </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>):
@@ -10722,6 +10766,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>
@@ -10729,7 +10776,10 @@ 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
     <varname>TimeZone</varname> 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
+    <varname>TimeZone</varname> setting and returns the value without a
+    time zone.
    </para>
 
    <para>
@@ -10738,6 +10788,12 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'A
     <literal><replaceable>timestamp</replaceable> AT TIME ZONE
     <replaceable>zone</replaceable></literal>.
    </para>
+
+   <para>
+    The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>
+    is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>
+    AT LOCAL</literal>.
+   </para>
   </sect2>
 
   <sect2 id="functions-datetime-current">
-- 
2.42.0

From 042ce9b581ca3b17afbf229d209ca59addb6c9a2 Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
Date: Wed, 4 Oct 2023 15:46:38 +0100
Subject: [PATCH v6] 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                    | 103 +++++++++++++++++++++-
 src/backend/parser/gram.y                 |   7 ++
 src/backend/utils/adt/date.c              |  14 +++
 src/backend/utils/adt/ruleutils.c         |  10 +++
 src/backend/utils/adt/timestamp.c         |  20 +++++
 src/include/catalog/pg_proc.dat           |   9 ++
 src/test/regress/expected/timestamptz.out |  47 ++++++++++
 src/test/regress/expected/timetz.out      |  39 ++++++++
 src/test/regress/sql/timestamptz.sql      |  21 +++++
 src/test/regress/sql/timetz.sql           |  17 ++++
 10 files changed, 284 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1ad64c3d6..ce62cb37b5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10604,42 +10604,46 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     that <function>date_bin</function> can truncate to an arbitrary interval.
    </para>
 
    <para>
     The <parameter>stride</parameter> interval must be greater than zero and
     cannot contain units of month or larger.
    </para>
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
-   <title><literal>AT TIME ZONE</literal></title>
+   <title><literal>AT TIME ZONE and AT LOCAL</literal></title>
 
    <indexterm>
     <primary>time zone</primary>
     <secondary>conversion</secondary>
    </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
     variants.
    </para>
 
     <table id="functions-datetime-zoneconvert-table">
-     <title><literal>AT TIME ZONE</literal> Variants</title>
+     <title><literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants</title>
      <tgroup cols="1">
       <thead>
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          Operator
         </para>
         <para>
          Description
         </para>
         <para>
@@ -10658,93 +10662,186 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
          Converts given time stamp <emphasis>without</emphasis> time zone to
          time stamp <emphasis>with</emphasis> time zone, assuming the given
          value is in the named time zone.
         </para>
         <para>
          <literal>timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</literal>
          <returnvalue>2001-02-17 03:38:40+00</returnvalue>
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <type>timestamp without time zone</type> <literal>AT LOCAL</literal>
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Converts given time stamp <emphasis>without</emphasis> time zone to
+         time stamp <emphasis>with</emphasis> the session's
+         <varname>TimeZone</varname> value as time zone.
+        </para>
+        <para>
+         <literal>timestamp '2001-02-16 20:38:40' at local</literal>
+         <returnvalue>2001-02-17 03:38:40+00</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
          <returnvalue>timestamp without time zone</returnvalue>
         </para>
         <para>
          Converts given time stamp <emphasis>with</emphasis> time zone to
          time stamp <emphasis>without</emphasis> time zone, as the time would
          appear in that zone.
         </para>
         <para>
          <literal>timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</literal>
          <returnvalue>2001-02-16 18:38:40</returnvalue>
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <type>timestamp with time zone</type> <literal>AT LOCAL</literal>
+         <returnvalue>timestamp without time zone</returnvalue>
+        </para>
+        <para>
+         Converts given time stamp <emphasis>with</emphasis> time zone to
+         time stamp <emphasis>without</emphasis> time zone, as the time would
+         appear with the session's <varname>TimeZone</varname> value as time zone.
+        </para>
+        <para>
+         <literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>
+         <returnvalue>2001-02-16 18:38:40</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
          <returnvalue>time with time zone</returnvalue>
         </para>
         <para>
          Converts given time <emphasis>with</emphasis> time zone to a new time
          zone.  Since no date is supplied, this uses the currently active UTC
          offset for the named destination zone.
         </para>
         <para>
          <literal>time with time zone '05:34:17-05' at time zone 'UTC'</literal>
          <returnvalue>10:34:17+00</returnvalue>
         </para></entry>
        </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <type>time with time zone</type> <literal>AT LOCAL</literal>
+         <returnvalue>time with time zone</returnvalue>
+        </para>
+        <para>
+         Converts given time <emphasis>with</emphasis> time zone to a new time
+         zone.  Since no date is supplied, this uses the currently active UTC
+         offset for the session's <varname>TimeZone</varname> value.
+        </para>
+        <para>
+         Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:
+        </para>
+        <para>
+         <literal>time with time zone '05:34:17-05' at local</literal>
+         <returnvalue>10:34:17+00</returnvalue>
+        </para></entry>
+       </row>
       </tbody>
      </tgroup>
     </table>
 
    <para>
     In these expressions, the desired time zone <replaceable>zone</replaceable> can be
     specified either as a text value (e.g., <literal>'America/Los_Angeles'</literal>)
     or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
     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>
+
+SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
+<lineannotation>Result: </lineannotation><computeroutput>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
     <varname>TimeZone</varname> 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
+    <varname>TimeZone</varname> setting and returns the value without a
+    time zone.
+   </para>
+
+   <para>
+    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
+    <literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.
    </para>
 
    <para>
     The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
     <replaceable>timestamp</replaceable>)</literal> is equivalent to the SQL-conforming construct
     <literal><replaceable>timestamp</replaceable> AT TIME ZONE
     <replaceable>zone</replaceable></literal>.
    </para>
+
+   <para>
+    The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>
+    is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>
+    AT LOCAL</literal>.
+   </para>
+
+   <para>
+    The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
+    <replaceable>time</replaceable>)</literal> is equivalent to the SQL-conforming construct
+    <literal><replaceable>time</replaceable> AT TIME ZONE <replaceable>zone</replaceable></literal>.
+   </para>
+
+   <para>
+    The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>
+    is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>
+    AT LOCAL</literal>.
+   </para>
   </sect2>
 
   <sect2 id="functions-datetime-current">
    <title>Current Date/Time</title>
 
    <indexterm>
     <primary>date</primary>
     <secondary>current</secondary>
    </indexterm>
 
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
@@ -14501,20 +14501,27 @@ a_expr:		c_expr									{ $$ = $1; }
 					n->location = @2;
 					$$ = (Node *) n;
 				}
 			| a_expr AT TIME ZONE a_expr			%prec AT
 				{
 					$$ = (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.
 		 *
 		 * If you add more explicitly-known operators, be sure to add them
 		 * also to b_expr and to the MathOp list below.
 		 */
 			| '+' a_expr					%prec UMINUS
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index ae0f24de2c..e4d21098cb 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -3118,10 +3118,24 @@ timetz_izone(PG_FUNCTION_ARGS)
 	result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
 	while (result->time < INT64CONST(0))
 		result->time += USECS_PER_DAY;
 	while (result->time >= USECS_PER_DAY)
 		result->time -= USECS_PER_DAY;
 
 	result->zone = tz;
 
 	PG_RETURN_TIMETZADT_P(result);
 }
+
+/* timetz_at_local()
+ *
+ * Unlike for timestamp[tz]_at_local, the type for timetz does not flip between
+ * time with/without time zone, so we cannot just call the conversion function.
+ */
+Datum
+timetz_at_local(PG_FUNCTION_ARGS)
+{
+	Datum	time = PG_GETARG_DATUM(0);
+	Datum	zone = PointerGetDatum(cstring_to_text(pg_get_timezone_name(session_timezone)));
+
+	return DirectFunctionCall2(timetz_zone, zone, time);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 442205382e..ed7f40f053 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10340,20 +10340,30 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
 			/* 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;
 
+		case F_TIMEZONE_TIMESTAMP:
+		case F_TIMEZONE_TIMESTAMPTZ:
+		case F_TIMEZONE_TIMETZ:
+			/* 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:
 		case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_INTERVAL:
 		case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_TIMESTAMP:
 		case F_OVERLAPS_TIMETZ_TIMETZ_TIMETZ_TIMETZ:
 		case F_OVERLAPS_TIME_INTERVAL_TIME_INTERVAL:
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
@@ -5914,10 +5914,30 @@ Datum
 generate_series_timestamptz(PG_FUNCTION_ARGS)
 {
 	return generate_series_timestamptz_internal(fcinfo);
 }
 
 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..b33fc94502 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2312,20 +2312,26 @@
   proargtypes => 'timestamptz timestamptz', prosrc => 'timestamp_ge' },
 { oid => '1157',
   proname => 'timestamptz_gt', proleakproof => 't', prorettype => 'bool',
   proargtypes => 'timestamptz timestamptz', prosrc => 'timestamp_gt' },
 { 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', provolatile => 's', prorettype => 'timestamp',
+  proargtypes => 'timestamptz', prosrc => 'timestamptz_at_local' },
+{ oid => '9160', descr => 'adjust timestamp to local time zone',
+  proname => 'timezone', provolatile => 's', 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',
   proargtypes => 'interval', prosrc => 'interval_out' },
 { oid => '2903', descr => 'I/O typmod',
   proname => 'intervaltypmodin', prorettype => 'int4',
   proargtypes => '_cstring', prosrc => 'intervaltypmodin' },
@@ -6088,20 +6094,23 @@
   proargtypes => 'timestamp timestamp', prosrc => 'timestamp_smaller' },
 { oid => '2036', descr => 'larger of two',
   proname => 'timestamp_larger', prorettype => 'timestamp',
   proargtypes => 'timestamp timestamp', prosrc => 'timestamp_larger' },
 { oid => '2037', descr => 'adjust time with time zone to new zone',
   proname => 'timezone', provolatile => 's', prorettype => 'timetz',
   proargtypes => 'text timetz', prosrc => 'timetz_zone' },
 { oid => '2038', descr => 'adjust time with time zone to new zone',
   proname => 'timezone', prorettype => 'timetz',
   proargtypes => 'interval timetz', prosrc => 'timetz_izone' },
+{ oid => '9161', descr => 'adjust time to local time zone',
+  proname => 'timezone', provolatile => 's', prorettype => 'timetz',
+  proargtypes => 'timetz', prosrc => 'timetz_at_local' },
 { oid => '2039', descr => 'hash',
   proname => 'timestamp_hash', prorettype => 'int4', proargtypes => 'timestamp',
   prosrc => 'timestamp_hash' },
 { oid => '3411', descr => 'hash',
   proname => 'timestamp_hash_extended', prorettype => 'int8',
   proargtypes => 'timestamp int8', prosrc => 'timestamp_hash_extended' },
 { oid => '2041', descr => 'intervals overlap?',
   proname => 'overlaps', proisstrict => 'f', prorettype => 'bool',
   proargtypes => 'timestamp timestamp timestamp timestamp',
   prosrc => 'overlaps_timestamp' },
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
@@ -3128,20 +3128,67 @@ SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 --------------------------
  Sun Oct 26 01:00:01 2014
 (1 row)
 
 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)
 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
                                              QUERY PLAN                                              
 -----------------------------------------------------------------------------------------------------
  Seq Scan on tmptz
diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out
index be49588b6d..a9a3a49ebb 100644
--- a/src/test/regress/expected/timetz.out
+++ b/src/test/regress/expected/timetz.out
@@ -255,10 +255,49 @@ SELECT date_part('second',      TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
 -----------
  25.575401
 (1 row)
 
 SELECT date_part('epoch',       TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
   date_part   
 --------------
  63025.575401
 (1 row)
 
+--
+-- test LOCAL time zone
+--
+BEGIN;
+SET LOCAL TimeZone TO 'UTC';
+CREATE VIEW time_local AS
+SELECT f1,
+       f1 AT LOCAL AS f2,
+       timezone(f1) AS f3,
+       f1 AT TIME ZONE current_setting('TimeZone') AS f4
+FROM TIMETZ_TBL
+ORDER BY f1;
+\sv time_local
+CREATE OR REPLACE VIEW public.time_local AS
+ SELECT f1,
+    (f1 AT LOCAL) AS f2,
+    timezone(f1) AS f3,
+    (f1 AT TIME ZONE current_setting('TimeZone'::text)) AS f4
+   FROM timetz_tbl
+  ORDER BY f1
+TABLE time_local;
+       f1       |       f2       |       f3       |       f4       
+----------------+----------------+----------------+----------------
+ 00:01:00-07    | 07:01:00+00    | 07:01:00+00    | 07:01:00+00
+ 01:00:00-07    | 08:00:00+00    | 08:00:00+00    | 08:00:00+00
+ 02:03:00-07    | 09:03:00+00    | 09:03:00+00    | 09:03:00+00
+ 08:08:00-04    | 12:08:00+00    | 12:08:00+00    | 12:08:00+00
+ 07:07:00-08    | 15:07:00+00    | 15:07:00+00    | 15:07:00+00
+ 11:59:00-07    | 18:59:00+00    | 18:59:00+00    | 18:59:00+00
+ 12:00:00-07    | 19:00:00+00    | 19:00:00+00    | 19:00:00+00
+ 12:01:00-07    | 19:01:00+00    | 19:01:00+00    | 19:01:00+00
+ 15:36:39-04    | 19:36:39+00    | 19:36:39+00    | 19:36:39+00
+ 15:36:39-05    | 20:36:39+00    | 20:36:39+00    | 20:36:39+00
+ 23:59:00-07    | 06:59:00+00    | 06:59:00+00    | 06:59:00+00
+ 23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00
+(12 rows)
+
+DROP VIEW time_local;
+COMMIT;
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
@@ -604,18 +604,39 @@ SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 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)
 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql
index 93c7bb1428..a274ce7423 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -77,10 +77,27 @@ SELECT EXTRACT(TIMEZONE    FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
 SELECT EXTRACT(TIMEZONE_HOUR   FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30');
 SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30');
 SELECT EXTRACT(EPOCH       FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
 
 -- date_part implementation is mostly the same as extract, so only
 -- test a few cases for additional coverage.
 SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
 SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
 SELECT date_part('second',      TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
 SELECT date_part('epoch',       TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
+
+--
+-- test LOCAL time zone
+--
+BEGIN;
+SET LOCAL TimeZone TO 'UTC';
+CREATE VIEW time_local AS
+SELECT f1,
+       f1 AT LOCAL AS f2,
+       timezone(f1) AS f3,
+       f1 AT TIME ZONE current_setting('TimeZone') AS f4
+FROM TIMETZ_TBL
+ORDER BY f1;
+\sv time_local
+TABLE time_local;
+DROP VIEW time_local;
+COMMIT;

base-commit: 82a7132f531b8b12e77d17476e9bfd599c3c30cf
-- 
2.34.1

diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index c4da10d47a..56c7746c11 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -3083,10 +3083,11 @@ timetz_zone(PG_FUNCTION_ARGS)
 	result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
 
 	result->time = t->time + (t->zone - tz) * USECS_PER_SEC;
+	/* C99 modulo has the wrong sign convention for negative input */
 	while (result->time < INT64CONST(0))
 		result->time += USECS_PER_DAY;
-	while (result->time >= USECS_PER_DAY)
-		result->time -= USECS_PER_DAY;
+	if (result->time >= USECS_PER_DAY)
+		result->time %= USECS_PER_DAY;
 
 	result->zone = tz;
 
@@ -3116,10 +3117,11 @@ timetz_izone(PG_FUNCTION_ARGS)
 	result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
 
 	result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
+	/* C99 modulo has the wrong sign convention for negative input */
 	while (result->time < INT64CONST(0))
 		result->time += USECS_PER_DAY;
-	while (result->time >= USECS_PER_DAY)
-		result->time -= USECS_PER_DAY;
+	if (result->time >= USECS_PER_DAY)
+		result->time %= USECS_PER_DAY;
 
 	result->zone = tz;
 
diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out
index 3f8e005ce7..cbab6cfe5d 100644
--- a/src/test/regress/expected/timetz.out
+++ b/src/test/regress/expected/timetz.out
@@ -304,4 +304,25 @@ TABLE timetz_local_view;
  23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00
 (12 rows)
 
+SELECT f1 AS dat,
+       f1 AT TIME ZONE 'UTC+10' AS dat_at_tz,
+       f1 AT TIME ZONE INTERVAL '-10:00' AS dat_at_int
+  FROM TIMETZ_TBL
+  ORDER BY f1;
+      dat       |   dat_at_tz    |   dat_at_int   
+----------------+----------------+----------------
+ 00:01:00-07    | 21:01:00-10    | 21:01:00-10
+ 01:00:00-07    | 22:00:00-10    | 22:00:00-10
+ 02:03:00-07    | 23:03:00-10    | 23:03:00-10
+ 08:08:00-04    | 02:08:00-10    | 02:08:00-10
+ 07:07:00-08    | 05:07:00-10    | 05:07:00-10
+ 11:59:00-07    | 08:59:00-10    | 08:59:00-10
+ 12:00:00-07    | 09:00:00-10    | 09:00:00-10
+ 12:01:00-07    | 09:01:00-10    | 09:01:00-10
+ 15:36:39-04    | 09:36:39-10    | 09:36:39-10
+ 15:36:39-05    | 10:36:39-10    | 10:36:39-10
+ 23:59:00-07    | 20:59:00-10    | 20:59:00-10
+ 23:59:59.99-07 | 20:59:59.99-10 | 20:59:59.99-10
+(12 rows)
+
 ROLLBACK;
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql
index 33f7f8aafb..d797f478f0 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -100,4 +100,9 @@ CREATE VIEW timetz_local_view AS
   ORDER BY f1;
 SELECT pg_get_viewdef('timetz_local_view', true);
 TABLE timetz_local_view;
+SELECT f1 AS dat,
+       f1 AT TIME ZONE 'UTC+10' AS dat_at_tz,
+       f1 AT TIME ZONE INTERVAL '-10:00' AS dat_at_int
+  FROM TIMETZ_TBL
+  ORDER BY f1;
 ROLLBACK;