Skip to content

Commit 97957fd

Browse files
michaelpqmichaelpq
committed
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. This includes three system functions able to do the work in the same way as the existing flavors for AT TIME ZONE, except that these need to be marked as stable as they depend on the session's TimeZone GUC. Bump catalog version. Author: Vik Fearing Reviewed-by: Laurenz Albe, Cary Huang, Michael Paquier Discussion: https://postgr.es/m/8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org
1 parent 0013ba2 commit 97957fd

File tree

11 files changed

+294
-4
lines changed

11 files changed

+294
-4
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 94 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10611,7 +10611,7 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1061110611
</sect2>
1061210612

1061310613
<sect2 id="functions-datetime-zoneconvert">
10614-
<title><literal>AT TIME ZONE</literal></title>
10614+
<title><literal>AT TIME ZONE and AT LOCAL</literal></title>
1061510615

1061610616
<indexterm>
1061710617
<primary>time zone</primary>
@@ -10622,6 +10622,10 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1062210622
<primary>AT TIME ZONE</primary>
1062310623
</indexterm>
1062410624

10625+
<indexterm>
10626+
<primary>AT LOCAL</primary>
10627+
</indexterm>
10628+
1062510629
<para>
1062610630
The <literal>AT TIME ZONE</literal> operator converts time
1062710631
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
1063210636
</para>
1063310637

1063410638
<table id="functions-datetime-zoneconvert-table">
10635-
<title><literal>AT TIME ZONE</literal> Variants</title>
10639+
<title><literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants</title>
1063610640
<tgroup cols="1">
1063710641
<thead>
1063810642
<row>
@@ -10665,6 +10669,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1066510669
</para></entry>
1066610670
</row>
1066710671

10672+
<row>
10673+
<entry role="func_table_entry"><para role="func_signature">
10674+
<type>timestamp without time zone</type> <literal>AT LOCAL</literal>
10675+
<returnvalue>timestamp with time zone</returnvalue>
10676+
</para>
10677+
<para>
10678+
Converts given time stamp <emphasis>without</emphasis> time zone to
10679+
time stamp <emphasis>with</emphasis> the session's
10680+
<varname>TimeZone</varname> value as time zone.
10681+
</para>
10682+
<para>
10683+
<literal>timestamp '2001-02-16 20:38:40' at local</literal>
10684+
<returnvalue>2001-02-17 03:38:40+00</returnvalue>
10685+
</para></entry>
10686+
</row>
10687+
1066810688
<row>
1066910689
<entry role="func_table_entry"><para role="func_signature">
1067010690
<type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
@@ -10681,6 +10701,22 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1068110701
</para></entry>
1068210702
</row>
1068310703

10704+
<row>
10705+
<entry role="func_table_entry"><para role="func_signature">
10706+
<type>timestamp with time zone</type> <literal>AT LOCAL</literal>
10707+
<returnvalue>timestamp without time zone</returnvalue>
10708+
</para>
10709+
<para>
10710+
Converts given time stamp <emphasis>with</emphasis> time zone to
10711+
time stamp <emphasis>without</emphasis> time zone, as the time would
10712+
appear with the session's <varname>TimeZone</varname> value as time zone.
10713+
</para>
10714+
<para>
10715+
<literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>
10716+
<returnvalue>2001-02-16 18:38:40</returnvalue>
10717+
</para></entry>
10718+
</row>
10719+
1068410720
<row>
1068510721
<entry role="func_table_entry"><para role="func_signature">
1068610722
<type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
@@ -10696,6 +10732,25 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1069610732
<returnvalue>10:34:17+00</returnvalue>
1069710733
</para></entry>
1069810734
</row>
10735+
10736+
<row>
10737+
<entry role="func_table_entry"><para role="func_signature">
10738+
<type>time with time zone</type> <literal>AT LOCAL</literal>
10739+
<returnvalue>time with time zone</returnvalue>
10740+
</para>
10741+
<para>
10742+
Converts given time <emphasis>with</emphasis> time zone to a new time
10743+
zone. Since no date is supplied, this uses the currently active UTC
10744+
offset for the session's <varname>TimeZone</varname> value.
10745+
</para>
10746+
<para>
10747+
Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:
10748+
</para>
10749+
<para>
10750+
<literal>time with time zone '05:34:17-05' at local</literal>
10751+
<returnvalue>10:34:17+00</returnvalue>
10752+
</para></entry>
10753+
</row>
1069910754
</tbody>
1070010755
</tgroup>
1070110756
</table>
@@ -10710,6 +10765,13 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1071010765
UTC, so it is not very common in practice.
1071110766
</para>
1071210767

10768+
<para>
10769+
The syntax <literal>AT LOCAL</literal> may be used as shorthand for
10770+
<literal>AT TIME ZONE <replaceable>local</replaceable></literal>, where
10771+
<replaceable>local</replaceable> is the session's
10772+
<varname>TimeZone</varname> value.
10773+
</para>
10774+
1071310775
<para>
1071410776
Examples (assuming the current <xref linkend="guc-timezone"/> setting
1071510777
is <literal>America/Los_Angeles</literal>):
@@ -10722,14 +10784,31 @@ SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/D
1072210784

1072310785
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
1072410786
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
10787+
10788+
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
10789+
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>
10790+
10791+
SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
10792+
<lineannotation>Result: </lineannotation><computeroutput>17:38:40</computeroutput>
1072510793
</screen>
1072610794
The first example adds a time zone to a value that lacks it, and
1072710795
displays the value using the current <varname>TimeZone</varname>
1072810796
setting. The second example shifts the time stamp with time zone value
1072910797
to the specified time zone, and returns the value without a time zone.
1073010798
This allows storage and display of values different from the current
1073110799
<varname>TimeZone</varname> setting. The third example converts
10732-
Tokyo time to Chicago time.
10800+
Tokyo time to Chicago time. The fourth example shifts the time stamp
10801+
with time zone value to the time zone currently specified by the
10802+
<varname>TimeZone</varname> setting and returns the value without a
10803+
time zone.
10804+
</para>
10805+
10806+
<para>
10807+
The fifth example is a cautionary tale. Due to the fact that there is no
10808+
date associated with the input value, the conversion is made using the
10809+
current date of the session. Therefore, this static example may show a wrong
10810+
result depending on the time of the year it is viewed because
10811+
<literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.
1073310812
</para>
1073410813

1073510814
<para>
@@ -10745,6 +10824,18 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'A
1074510824
<literal><replaceable>time</replaceable> AT TIME ZONE
1074610825
<replaceable>zone</replaceable></literal>.
1074710826
</para>
10827+
10828+
<para>
10829+
The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>
10830+
is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>
10831+
AT LOCAL</literal>.
10832+
</para>
10833+
10834+
<para>
10835+
The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>
10836+
is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>
10837+
AT LOCAL</literal>.
10838+
</para>
1074810839
</sect2>
1074910840

1075010841
<sect2 id="functions-datetime-current">

‎src/backend/parser/gram.y

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14508,6 +14508,13 @@ a_expr: c_expr { $$ = $1; }
1450814508
COERCE_SQL_SYNTAX,
1450914509
@2);
1451014510
}
14511+
| a_expr AT LOCAL %prec AT
14512+
{
14513+
$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
14514+
list_make1($1),
14515+
COERCE_SQL_SYNTAX,
14516+
-1);
14517+
}
1451114518
/*
1451214519
* These operators must be called out explicitly in order to make use
1451314520
* of bison's automatic operator-precedence handling. All other

‎src/backend/utils/adt/date.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3125,3 +3125,18 @@ timetz_izone(PG_FUNCTION_ARGS)
31253125

31263126
PG_RETURN_TIMETZADT_P(result);
31273127
}
3128+
3129+
/* timetz_at_local()
3130+
*
3131+
* Unlike for timestamp[tz]_at_local, the type for timetz does not flip between
3132+
* time with/without time zone, so we cannot just call the conversion function.
3133+
*/
3134+
Datum
3135+
timetz_at_local(PG_FUNCTION_ARGS)
3136+
{
3137+
Datum time = PG_GETARG_DATUM(0);
3138+
const char *tzn = pg_get_timezone_name(session_timezone);
3139+
Datum zone = PointerGetDatum(cstring_to_text(tzn));
3140+
3141+
return DirectFunctionCall2(timetz_zone, zone, time);
3142+
}

‎src/backend/utils/adt/ruleutils.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10347,6 +10347,16 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
1034710347
appendStringInfoChar(buf, ')');
1034810348
return true;
1034910349

10350+
case F_TIMEZONE_TIMESTAMP:
10351+
case F_TIMEZONE_TIMESTAMPTZ:
10352+
case F_TIMEZONE_TIMETZ:
10353+
/* AT LOCAL */
10354+
appendStringInfoChar(buf, '(');
10355+
get_rule_expr_paren((Node *) linitial(expr->args), context, false,
10356+
(Node *) expr);
10357+
appendStringInfoString(buf, " AT LOCAL)");
10358+
return true;
10359+
1035010360
case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
1035110361
case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
1035210362
case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:

‎src/backend/utils/adt/timestamp.c

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5921,3 +5921,23 @@ generate_series_timestamptz_at_zone(PG_FUNCTION_ARGS)
59215921
{
59225922
return generate_series_timestamptz_internal(fcinfo);
59235923
}
5924+
5925+
/* timestamp_at_local()
5926+
* timestamptz_at_local()
5927+
*
5928+
* The regression tests do not like two functions with the same proargs and
5929+
* prosrc but different proname, but the grammar for AT LOCAL needs an
5930+
* overloaded name to handle both types of timestamp, so we make simple
5931+
* wrappers for it.
5932+
*/
5933+
Datum
5934+
timestamp_at_local(PG_FUNCTION_ARGS)
5935+
{
5936+
return timestamp_timestamptz(fcinfo);
5937+
}
5938+
5939+
Datum
5940+
timestamptz_at_local(PG_FUNCTION_ARGS)
5941+
{
5942+
return timestamptz_timestamp(fcinfo);
5943+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202309251
60+
#define CATALOG_VERSION_NO 202310131
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2319,6 +2319,9 @@
23192319
{ oid => '1159', descr => 'adjust timestamp to new time zone',
23202320
proname => 'timezone', prorettype => 'timestamp',
23212321
proargtypes => 'text timestamptz', prosrc => 'timestamptz_zone' },
2322+
{ oid => '9159', descr => 'adjust timestamp to local time zone',
2323+
proname => 'timezone', provolatile => 's', prorettype => 'timestamp',
2324+
proargtypes => 'timestamptz', prosrc => 'timestamptz_at_local' },
23222325

23232326
{ oid => '1160', descr => 'I/O',
23242327
proname => 'interval_in', provolatile => 's', prorettype => 'interval',
@@ -6095,6 +6098,9 @@
60956098
{ oid => '2038', descr => 'adjust time with time zone to new zone',
60966099
proname => 'timezone', prorettype => 'timetz',
60976100
proargtypes => 'interval timetz', prosrc => 'timetz_izone' },
6101+
{ oid => '9161', descr => 'adjust time to local time zone',
6102+
proname => 'timezone', provolatile => 's', prorettype => 'timetz',
6103+
proargtypes => 'timetz', prosrc => 'timetz_at_local' },
60986104
{ oid => '2039', descr => 'hash',
60996105
proname => 'timestamp_hash', prorettype => 'int4', proargtypes => 'timestamp',
61006106
prosrc => 'timestamp_hash' },
@@ -6190,6 +6196,9 @@
61906196
{ oid => '2070', descr => 'adjust timestamp to new time zone',
61916197
proname => 'timezone', prorettype => 'timestamptz',
61926198
proargtypes => 'interval timestamp', prosrc => 'timestamp_izone' },
6199+
{ oid => '9160', descr => 'adjust timestamp to local time zone',
6200+
proname => 'timezone', provolatile => 's', prorettype => 'timestamptz',
6201+
proargtypes => 'timestamp', prosrc => 'timestamp_at_local' },
61936202
{ oid => '2071',
61946203
proname => 'date_pl_interval', prorettype => 'timestamp',
61956204
proargtypes => 'date interval', prosrc => 'date_pl_interval' },

‎src/test/regress/expected/timestamptz.out

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3135,6 +3135,62 @@ SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
31353135
Sun Oct 26 02:00:00 2014
31363136
(1 row)
31373137

3138+
--
3139+
-- Test LOCAL time zone
3140+
--
3141+
BEGIN;
3142+
SET LOCAL TIME ZONE 'Europe/Paris';
3143+
VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3144+
column1
3145+
--------------------------
3146+
Sat Jul 08 01:38:00 1978
3147+
(1 row)
3148+
3149+
VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3150+
column1
3151+
-------------------------------
3152+
Fri Jul 07 19:38:00 1978 CEST
3153+
(1 row)
3154+
3155+
SET LOCAL TIME ZONE 'Australia/Sydney';
3156+
VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3157+
column1
3158+
--------------------------
3159+
Sat Jul 08 09:38:00 1978
3160+
(1 row)
3161+
3162+
VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3163+
column1
3164+
-------------------------------
3165+
Fri Jul 07 19:38:00 1978 AEST
3166+
(1 row)
3167+
3168+
SET LOCAL TimeZone TO 'UTC';
3169+
CREATE VIEW timestamp_local_view AS
3170+
SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL AS ttz_at_local,
3171+
timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)) AS ttz_func,
3172+
TIMESTAMP '1978-07-07 19:38' AT LOCAL AS t_at_local,
3173+
timezone(TIMESTAMP '1978-07-07 19:38') AS t_func;
3174+
SELECT pg_get_viewdef('timestamp_local_view', true);
3175+
pg_get_viewdef
3176+
----------------------------------------------------------------------------------------------
3177+
SELECT ('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone AT LOCAL) AS ttz_at_local,+
3178+
timezone('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone) AS ttz_func, +
3179+
('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL) AS t_at_local, +
3180+
timezone('Fri Jul 07 19:38:00 1978'::timestamp without time zone) AS t_func;
3181+
(1 row)
3182+
3183+
\x
3184+
TABLE timestamp_local_view;
3185+
-[ RECORD 1 ]+-----------------------------
3186+
ttz_at_local | Fri Jul 07 23:38:00 1978
3187+
ttz_func | Fri Jul 07 23:38:00 1978
3188+
t_at_local | Fri Jul 07 19:38:00 1978 UTC
3189+
t_func | Fri Jul 07 19:38:00 1978 UTC
3190+
3191+
\x
3192+
DROP VIEW timestamp_local_view;
3193+
COMMIT;
31383194
--
31393195
-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
31403196
--

‎src/test/regress/expected/timetz.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -262,3 +262,45 @@ SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
262262
63025.575401
263263
(1 row)
264264

265+
--
266+
-- AT LOCAL with timetz
267+
--
268+
BEGIN;
269+
SET LOCAL TimeZone TO 'UTC';
270+
CREATE VIEW timetz_local_view AS
271+
SELECT f1 AS dat,
272+
timezone(f1) AS dat_func,
273+
f1 AT LOCAL AS dat_at_local,
274+
f1 AT TIME ZONE current_setting('TimeZone') AS dat_at_time
275+
FROM TIMETZ_TBL
276+
ORDER BY f1;
277+
SELECT pg_get_viewdef('timetz_local_view', true);
278+
pg_get_viewdef
279+
------------------------------------------------------------------------
280+
SELECT f1 AS dat, +
281+
timezone(f1) AS dat_func, +
282+
(f1 AT LOCAL) AS dat_at_local, +
283+
(f1 AT TIME ZONE current_setting('TimeZone'::text)) AS dat_at_time+
284+
FROM timetz_tbl +
285+
ORDER BY f1;
286+
(1 row)
287+
288+
TABLE timetz_local_view;
289+
dat | dat_func | dat_at_local | dat_at_time
290+
----------------+----------------+----------------+----------------
291+
00:01:00-07 | 07:01:00+00 | 07:01:00+00 | 07:01:00+00
292+
01:00:00-07 | 08:00:00+00 | 08:00:00+00 | 08:00:00+00
293+
02:03:00-07 | 09:03:00+00 | 09:03:00+00 | 09:03:00+00
294+
08:08:00-04 | 12:08:00+00 | 12:08:00+00 | 12:08:00+00
295+
07:07:00-08 | 15:07:00+00 | 15:07:00+00 | 15:07:00+00
296+
11:59:00-07 | 18:59:00+00 | 18:59:00+00 | 18:59:00+00
297+
12:00:00-07 | 19:00:00+00 | 19:00:00+00 | 19:00:00+00
298+
12:01:00-07 | 19:01:00+00 | 19:01:00+00 | 19:01:00+00
299+
15:36:39-04 | 19:36:39+00 | 19:36:39+00 | 19:36:39+00
300+
15:36:39-05 | 20:36:39+00 | 20:36:39+00 | 20:36:39+00
301+
23:59:00-07 | 06:59:00+00 | 06:59:00+00 | 06:59:00+00
302+
23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00
303+
(12 rows)
304+
305+
DROP VIEW timetz_local_view;
306+
COMMIT;

0 commit comments

Comments
 (0)