From: Vik Fearing <vik@postgresfriends.org>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Add support for AT LOCAL
Date: Mon, 5 Jun 2023 23:13:52 -0400
Message-ID: <8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org> (raw)
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
Attachments:v1-0001-Add-support-for-AT-LOCAL.patch (text/x-patch, 11.6K) download
From b8317f3070c11df1e2ad791bd8d823aaae66dbe4 Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
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.sgmlindex 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.yindex 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.cindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: vik@postgresfriends.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Add support for AT LOCAL
In-Reply-To: <8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox