Subject: Add support for AT LOCAL

From: Vik Fearing <[email-masked]>
Date: Mon, 5 Jun 2023 23:13:52 -0400

The Standard defines time zone conversion as follows:

<datetime factor> ::=
   <datetime primary> [ <time zone> ]

<time zone> ::=
   AT <time zone specifier>

<time zone specifier> ::=
     LOCAL
   | TIME ZONE <interval primary>

While looking at something else, I noticed we do not support AT LOCAL. 
The local time zone is defined as that of *the session*, not the server, 
which can make this quite interesting in views where the view will 
automatically adjust to the session's time zone.

Patch against 3f1aaaa180 attached.
-- 
Vik Fearing

From b8317f3070c11df1e2ad791bd8d823aaae66dbe4 Mon Sep 17 00:00:00 2001
From: Vik Fearing <[email-masked]>
Date: Mon, 5 Jun 2023 19:42:42 -0400
Subject: [PATCH v1] 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         | 43 ++++++++++++++++++---
 src/test/regress/expected/timestamptz.out | 47 +++++++++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 21 ++++++++++
 5 files changed, 131 insertions(+), 5 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
@@ -10553,14 +10553,18 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     <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.
@@ -10641,26 +10645,35 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
     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>
 </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
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
@@ -14414,14 +14414,26 @@ a_expr:		c_expr									{ $$ = $1; }
 			| 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
+				{
+					/* 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
 		 * 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
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d3a973d86b..1fca65a7f2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10313,20 +10313,53 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
 		case F_TIMEZONE_INTERVAL_TIMESTAMP:
 		case F_TIMEZONE_INTERVAL_TIMESTAMPTZ:
 		case F_TIMEZONE_INTERVAL_TIMETZ:
 		case F_TIMEZONE_TEXT_TIMESTAMP:
 		case F_TIMEZONE_TEXT_TIMESTAMPTZ:
 		case F_TIMEZONE_TEXT_TIMETZ:
 			/* 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((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);
+			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:
 		case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:
 		case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_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
@@ -3131,14 +3131,61 @@ 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)
 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
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
@@ -607,14 +607,35 @@ 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)
 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';

base-commit: 3f1aaaa180689f2015e7f7bd01c9be6d7a993b42
-- 
2.34.1

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 06 Jun 2023 09:56:42 +0200

> Patch against 3f1aaaa180 attached.

+1 on the idea; it should be faily trivial, if not very useful.

At a quick glance, it looks like you resolve "timezone" at the time
the query is parsed.  Shouldn't the resolution happen at query
execution time?

Yours,
Laurenz Albe

---

From: Vik Fearing <[email-masked]>
Date: Tue, 6 Jun 2023 04:24:55 -0400

> +1 on the idea; it should be faily trivial, if not very useful.

Thanks.

> the query is parsed.  Shouldn't the resolution happen at query

current_setting(text) is stable, and my tests show that it is calculated 
at execution time.

postgres=# prepare x as values (now() at local);
PREPARE
postgres=# set timezone to 'UTC';
SET
postgres=# execute x;
           column1
----------------------------
  2023-06-06 08:23:02.088634
(1 row)

postgres=# set timezone to 'Asia/Pyongyang';
SET
postgres=# execute x;
           column1
----------------------------
  2023-06-06 17:23:14.837219
(1 row)

Am I missing something?
-- 
Vik Fearing

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 12 Jun 2023 17:37:07 +0200

> the query is parsed.  Shouldn't the resolution happen at query

Sounds like it -- consider the case where the timestamp value is a
partition key and one of the partition boundaries falls in between two
timezone offsets for some particular ts value; then you use a prepared
query to read from a view defined with AT LOCAL.  Partition pruning
would need to compute partitions to read from at runtime, not plan time.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

---

From: Vik Fearing <[email-masked]>
Date: Sun, 25 Jun 2023 02:14:52 +0200

> would need to compute partitions to read from at runtime, not plan time.

Can you show me an example of that happening with my patch?
-- 
Vik Fearing

---

From: Daniel Gustafsson <[email-masked]>
Date: Mon, 3 Jul 2023 15:42:44 +0200

> On 6 Jun 2023, at 05:13, Vik Fearing <[email-masked]> wrote:

> Patch against 3f1aaaa180 attached.

This patch fails to compile, the declaration of variables in the switch block
needs to be scoped within a { } block.  I've fixed this trivial error in the
attached v2 and also reflowed the comments which now no longer fit.

--
Daniel Gustafsson

---

From: Vik Fearing <[email-masked]>
Date: Mon, 3 Jul 2023 16:23:50 +0200

On 7/3/23 15:42, Daniel Gustafsson wrote:
> needs to be scoped within a { } block.

Interesting.  It compiles for me.

> I've fixed this trivial error in the

Thank you.
-- 
Vik Fearing