From: Vik Fearing <vik@postgresfriends.org>
To: Michael Paquier <michael@paquier.xyz>
Cc: cary huang <hcary328@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Add support for AT LOCAL
Date: Wed, 4 Oct 2023 15:49:03 +0100
Message-ID: <c2c6f2c3-49e3-c1fe-65ca-49f275af41c2@postgresfriends.org> (raw)
In-Reply-To: <ZRZ8ePmlhx_1LvVV@paquier.xyz>
References: <8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org>
<b274179d-2121-68f3-7cd9-c5a572f8fd83@postgresfriends.org>
<169541920143.2315938.17412414971559687288.pgcf@coridan.postgresql.org>
<dbea9877-f39b-1a5b-6e5b-5aad2bbc2821@postgresfriends.org>
<ZRZ8ePmlhx_1LvVV@paquier.xyz>
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
Attachments:v3-0001-Add-support-for-AT-LOCAL.patch (text/x-patch, 11.9K) download
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.sgmlindex 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.yindex 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.cindex 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.cindex 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.datindex 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.outindex 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.sqlindex 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
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, michael@paquier.xyz, hcary328@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Add support for AT LOCAL
In-Reply-To: <c2c6f2c3-49e3-c1fe-65ca-49f275af41c2@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