On 9/29/23 09:27, Michael Paquier wrote:
On Sat, Sep 23, 2023 at 12:54:01AM +0200, Vik Fearing wrote:
On 9/22/23 23:46, cary huang wrote:
I think this feature can be a useful addition in dealing with time
zones. I have applied and tried out the patch, The feature works as
described and seems promising. The problem with compilation failure
was probably reported on CirrusCI when compiled on different
platforms. I have run the latest patch on my own Cirrus CI environment
and everything checked out fine.
Thank you for reviewing!
+ | 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);
As the deparsing code introduced by this patch is showing, this leads
to a lot of extra complexity. And, actually, this can be quite
expensive as well with these two layers of functions. Note also that
in comparison to SQLValueFunctions, COERCE_SQL_SYNTAX does less
inlining. So here comes my question: why doesn't this stuff just use
one underlying function to do this job?
Okay. Here is a v3 using that approach.
--
Vik Fearing
From e68305dafd2d4f9439cbd341e8f04745d8a945ed Mon Sep 17 00:00:00 2001
From: Vik Fearing <v...@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
