Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qq4TD-0084cc-GX for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Oct 2023 04:35:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qq4SC-003aoG-DP for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Oct 2023 04:34:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qq4SB-003amc-Hf for pgsql-hackers@lists.postgresql.org; Tue, 10 Oct 2023 04:34:44 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qq4S9-001E3U-0J for pgsql-hackers@lists.postgresql.org; Tue, 10 Oct 2023 04:34:43 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 852673200A34; Tue, 10 Oct 2023 00:34:37 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Tue, 10 Oct 2023 00:34:37 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm2; t=1696912477; x=1696998877; bh=Ic bhYr/Wi8loFWp9uc5FLdHHQ8eaf1oRhfehsnYK+aw=; b=AvVXst7sAw2u10pOZP CLOPa8s/dgMgKX+JWQEAE6yZrzl7s1d3PLdFXSqJKBHn/XbiYXcpwTYo3pU0i3pH ElA/pOTcIMBwnKVyxSo6hpatqqYod+GydtPp6CdQyWJCQTFcTkKTxK0snmiye8yU FXao/pBlLl/PqbdZ1C9erTyWCut8zejsfoOtdeXboQ/Jgb5bNR6O4EkOnntGZEGE WPJ/caH9W0HNb75fztm/ePNaoi4x6VPrsrOBs6Aty1IHsnELJCbVW6ynIg8aX2Rf DqSuHycorEqpkMIh5Q4gIe8xzMfwF6kAOageBR/los3EVpkkPxtjBMytxqByThTg AkWg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; t=1696912477; x=1696998877; bh=IcbhYr/Wi8loF Wp9uc5FLdHHQ8eaf1oRhfehsnYK+aw=; b=X9E4HJvCEmMiTDG6VXsiM37O0sWoo RJ9B6M3oAa0r17VilNDdsXUQCuM4vD+uQUCvBxjOF6IN5WOX3ltB76E87ka6GV7w cRtgm0aQgBCAa2K8zE5IMkpAELNZUZaU+LjpaseDA0Oy9cCxpDB9J3FYPnYbgUSV CezJ33oOzt5KWFjupMroV2OdtlKY5sYnb3iPGk/YPrtlxf/qyVwG4m0EIP2M3vwZ deukYVh6IdmaKhNuA4zBgbyJ63Mj+CqzoxqP5+FxfVomx/o0El9N/1+2cwEAprIs A8PEbd9+Mwyhu1NATa/1s12fFVv1OQuMGILKgyHi4aXtKaEaotEJ+oaKg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrheeggdekgecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdejtddmnecujfgurhepfffhvfevuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeetleeifedufffhhfdtteelgeeg geffhfekueevteeigfduudevudetgfegiedvjeenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 10 Oct 2023 00:34:35 -0400 (EDT) Date: Tue, 10 Oct 2023 13:34:32 +0900 From: Michael Paquier To: Vik Fearing Cc: cary huang , pgsql-hackers@lists.postgresql.org Subject: Re: Add support for AT LOCAL Message-ID: References: <8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org> <169541920143.2315938.17412414971559687288.pgcf@coridan.postgresql.org> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="8XgrHVTZuCbHZYPH" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --8XgrHVTZuCbHZYPH Content-Type: multipart/mixed; boundary="TCMajMvI3WDdhz+Q" Content-Disposition: inline --TCMajMvI3WDdhz+Q Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --TCMajMvI3WDdhz+Q Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0001-Add-support-for-AT-LOCAL-with-timestamps.patch" Content-Transfer-Encoding: quoted-printable =46rom e136d38967a863452762e1ee6e28d9ab40056adf Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier 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 =3D> '1159', descr =3D> 'adjust timestamp to new time zone', proname =3D> 'timezone', prorettype =3D> 'timestamp', proargtypes =3D> 'text timestamptz', prosrc =3D> 'timestamptz_zone' }, +{ oid =3D> '9159', descr =3D> 'adjust timestamp to local time zone', + proname =3D> 'timezone', prorettype =3D> 'timestamp', + proargtypes =3D> 'timestamptz', prosrc =3D> 'timestamptz_at_local' }, +{ oid =3D> '9160', descr =3D> 'adjust timestamp to local time zone', + proname =3D> 'timezone', prorettype =3D> 'timestamptz', + proargtypes =3D> 'timestamp', prosrc =3D> 'timestamp_at_local' }, =20 { oid =3D> '1160', descr =3D> 'I/O', proname =3D> 'interval_in', provolatile =3D> 's', prorettype =3D> 'inter= val', 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 { $$ =3D $1; } COERCE_SQL_SYNTAX, @2); } + | a_expr AT LOCAL %prec AT + { + $$ =3D (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/rule= utils.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_contex= t *context) appendStringInfoChar(buf, ')'); return true; =20 + 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/time= stamp.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/e= xpected/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 TIM= E ZONE 'MSK'; Sun Oct 26 02:00:00 2014 (1 row) =20 +-- +-- Test AT LOCAL +-- +BEGIN; +SET LOCAL TIME ZONE 'Europe/Paris'; +VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZO= NE) AT LOCAL); + column1 =20 +-------------------------- + Sat Jul 08 01:38:00 1978 +(1 row) + +VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL); + column1 =20 +------------------------------- + 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 ZO= NE) AT LOCAL); + column1 =20 +-------------------------- + Sat Jul 08 09:38:00 1978 +(1 row) + +VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL); + column1 =20 +------------------------------- + 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 TIM= E 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 LOCA= L),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 =20 +--------------------------+--------------------------+--------------------= ----------+------------------------------ + 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 #145= 04) -- diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/ti= mestamptz.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'; =20 +-- +-- Test AT LOCAL +-- +BEGIN; +SET LOCAL TIME ZONE 'Europe/Paris'; +VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZO= NE) 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 ZO= NE) 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 TIM= E 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 #145= 04) -- 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 =20 - <literal>AT TIME ZONE</literal> + <literal>AT TIME ZONE and AT LOCAL</literal> =20 time zone @@ -10622,6 +10622,10 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-1= 1 15:44:17', TIMESTAMP '2001-0 AT TIME ZONE =20 + + AT LOCAL + + The AT TIME ZONE operator converts time stamp without time zone to/from @@ -10632,7 +10636,7 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11= 15:44:17', TIMESTAMP '2001-0 =20 - <literal>AT TIME ZONE</literal> Variants + <literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal= > Variants @@ -10665,6 +10669,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-1= 1 15:44:17', TIMESTAMP '2001-0 =20 + + + timestamp without time zone AT LOCAL + timestamp with time zone + + + Converts given time stamp without time zone = to + time stamp with the session's + TimeZone value as time zone. + + + timestamp '2001-02-16 20:38:40' at local + 2001-02-17 03:38:40+00 + + + timestamp with time zone AT TIME ZONE zone @@ -10681,6 +10701,23 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-1= 1 15:44:17', TIMESTAMP '2001-0 =20 + + + timestamp with time zone AT LOCAL + timestamp without time zone + + + Converts given time stamp with time zone to + time stamp without time zone, as the time wo= uld + appear with the session's TimeZone<= /varname> + value as time zone. + + + timestamp with time zone '2001-02-16 20:38:40-05' at loc= al + 2001-02-16 18:38:40 + + + time with time zone AT TIME ZONE = zone @@ -10710,6 +10747,13 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-1= 1 15:44:17', TIMESTAMP '2001-0 UTC, so it is not very common in practice. =20 + + The syntax AT LOCAL may be used as shorthand for + AT TIME ZONE local, where + local is the session's + TimeZone value. + + Examples (assuming the current setting is America/Los_Angeles): @@ -10722,6 +10766,9 @@ SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:4= 0-05' AT TIME ZONE 'America/D =20 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME Z= ONE 'America/Chicago'; Result: 2001-02-16 05:38:= 40 + +SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL; +Result: 2001-02-16 17:38:= 40 The first example adds a time zone to a value that lacks it, and displays the value using the current TimeZone @@ -10729,7 +10776,10 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZON= E '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 TimeZone 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 + TimeZone setting and returns the value without a + time zone. =20 @@ -10738,6 +10788,12 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZON= E 'Asia/Tokyo' AT TIME ZONE 'A timestamp AT TIME ZONE zone. + + + The function timezone(times= tamp) + is equivalent to the SQL-conforming construct ti= mestamp + AT LOCAL. + =20 --=20 2.42.0 --TCMajMvI3WDdhz+Q-- --8XgrHVTZuCbHZYPH Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmUk1FgACgkQnvQgOdby QH1qtw/9Fv6rmSf7xfF7XKOrAlFmpdmD/nsVxcijL+TBKLuZ1ME+Gpq3IQQfOguN zTFqrdCKuoLqSs6QyZnr1/XgVq+Nmj/HegBan9hwnoUIbO5XoWxpZEOpLnZ+C13d rnfSWugIyrpWbCZJJ8EG/Sb71e1C6ly3zRTlho7Aq/0MgS2EQNqQe5hp9pFZfGzc SmD0CF2+MuTJ8Yz6IgBbDQUE4TD1YOApXDZmqUufQiYvzEi55d8BRNf8CGsd9+qZ SKD4uPj5PuMhJQONf0axyNMU4vr8OBYX28wSyFWaQyS2hl6Tb9+FOkawKzmczAib qDfUz3aeERIY55UZTjrCOUgq587Mgd06DzZB2jgT2/qIUn61sOANo04vcz78i55w OWG0f3D2YCstZ9xt9UP6XEoeJw/3wZzqAqYv1D+rxf12f/XM62twa1n8yacVlwML gBeA6vHkqSGrwRrY+NeTCav4KI3XHtrM6Plvyv5j4rYG5nhswg6A3JQR19QVmU9i g5UU24NgCOm0s9te220h0EOU0d1iUNZlkHrk4wasFfr244E8ef5Cl5HycbCRI4SB MYYwkGg1rBLcbXMslwhwWuROYKYCJICWhEAXUUCVfI59zicsUTbvqYAbsT8aE+RY OgPturg0g1YRPrjdrsvPqUhtgZy4MmnouXpJL0EFWV05yB8BOps= =kqhE -----END PGP SIGNATURE----- --8XgrHVTZuCbHZYPH--