On Sat, Oct 07, 2023 at 02:35:06AM +0100, Vik Fearing wrote: > I realized that my regression tests are not exercising what I originally > intended them to after this change. They are supposed to show that calling > the function explicitly or using AT LOCAL is correctly reproduced by > ruleutils.c.
Yes, I don't really see the point in adding more tests for the deparsing of AT TIME ZONE in this context. I would not expect one to call directly timezone() with the grammar in place, but I have no objections either to do that in the view for the regression tests as you are suggesting in v4. The minimal set of changes to test is to make sure that both paths (ts->tstz and tstz->tz) are exercised, and that's what you do. Anyway, upon review, I have a few issues with this patch. First is the documentation that I find too light: - There is no description for AT LOCAL and what kind of result one gets back depending on the input given, while AT TIME ZONE has more details about the arguments that can be used and the results obtained. - The function timezone(zone, timestamp) is mentioned, and I think that we should do the same with timezone(timestamp) for AT LOCAL. Another thing that I have been surprised with is the following, which is inconsistent with AT TIME ZONE because we are lacking one system function: =# select time with time zone '05:34:17-05' at local; ERROR: 42883: function pg_catalog.timezone(time with time zone) does not exist I think that we should include that to have a full set of operations supported, similar to AT TIME ZONE (see \df+ timezone). It looks like this would need one extra timetz_at_local(), which would require a bit more refactoring in date.c so as an equivalent of timetz_zone() could feed on the current session's TimeZone instead. I guess that you could just have an timetz_zone_internal() that optionally takes a timezone provided by the user or gets the current session's Timezone (session_timezone). Am I missing something? I am attaching a v5 that addresses the documentation bits, could you look at the business with date.c? -- Michael
From e136d38967a863452762e1ee6e28d9ab40056adf Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@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
signature.asc
Description: PGP signature
