From 03273214f0320e347a0b012763dc82cd91ae6775 Mon Sep 17 00:00:00 2001 From: Vik Fearing Date: Wed, 4 Oct 2023 15:46:38 +0100 Subject: [PATCH v4] 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 AT TIME ZONE + + AT LOCAL + + The AT TIME ZONE operator converts time stamp without time zone to/from time stamp with time zone, and time with time zone values to different time zones. 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 . The interval case is only useful for zones that have fixed offsets from UTC, so it is not very common in practice. + + 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): SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; Result: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver'; Result: 2001-02-16 18:38:40 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE '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 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..64fdc64a14 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, + 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) -- 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..53b5878e21 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, + 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) -- create temp table tmptz (f1 timestamptz primary key); insert into tmptz values ('2017-01-18 00:00+00'); explain (costs off) base-commit: ffb69b23115a1ca1d81f7e273d50b75154ae7b0b -- 2.34.1