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: Fri, 13 Oct 2023 02:20:59 +0200
Message-ID: <a2bd6444-7fe4-44cd-8cb6-d7ac13f4d3e0@postgresfriends.org> (raw)
In-Reply-To: <ZSTUWON7DOfo-2ES@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>
<c2c6f2c3-49e3-c1fe-65ca-49f275af41c2@postgresfriends.org>
<ZR-jmKYU5BDQEAe4@paquier.xyz>
<e3b4c42d-739d-4375-9401-2f62ad59d326@postgresfriends.org>
<ZSTUWON7DOfo-2ES@paquier.xyz>
On 10/10/23 05:34, Michael Paquier wrote:
> I am attaching a v5 that addresses the documentation bits, could you
> look at the business with date.c?
Here is a v6 which hopefully addresses all of your concerns.
--
Vik Fearing
Attachments:v6-0001-Add-support-for-AT-LOCAL.patch (text/x-patch, 26.6K) download
From 042ce9b581ca3b17afbf229d209ca59addb6c9a2 Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik@postgresfriends.org>
Date: Wed, 4 Oct 2023 15:46:38 +0100
Subject: [PATCH v6] 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 | 103 +++++++++++++++++++++-
src/backend/parser/gram.y | 7 ++
src/backend/utils/adt/date.c | 14 +++
src/backend/utils/adt/ruleutils.c | 10 +++
src/backend/utils/adt/timestamp.c | 20 +++++
src/include/catalog/pg_proc.dat | 9 ++
src/test/regress/expected/timestamptz.out | 47 ++++++++++
src/test/regress/expected/timetz.out | 39 ++++++++
src/test/regress/sql/timestamptz.sql | 21 +++++
src/test/regress/sql/timetz.sql | 17 ++++
10 files changed, 284 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex f1ad64c3d6..ce62cb37b5 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -10604,42 +10604,46 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
that <function>date_bin</function> can truncate to an arbitrary interval.
</para>
<para>
The <parameter>stride</parameter> interval must be greater than zero and
cannot contain units of month or larger.
</para>
</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>
<secondary>conversion</secondary>
</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
variants.
</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>
<entry role="func_table_entry"><para role="func_signature">
Operator
</para>
<para>
Description
</para>
<para>
@@ -10658,93 +10662,186 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
Converts given time stamp <emphasis>without</emphasis> time zone to
time stamp <emphasis>with</emphasis> time zone, assuming the given
value is in the named time zone.
</para>
<para>
<literal>timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</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 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>
<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 in that zone.
</para>
<para>
<literal>timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</literal>
<returnvalue>2001-02-16 18:38:40</returnvalue>
</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 with 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>
<returnvalue>time with time zone</returnvalue>
</para>
<para>
Converts given time <emphasis>with</emphasis> time zone to a new time
zone. Since no date is supplied, this uses the currently active UTC
offset for the named destination zone.
</para>
<para>
<literal>time with time zone '05:34:17-05' at time zone 'UTC'</literal>
<returnvalue>10:34:17+00</returnvalue>
</para></entry>
</row>
++ <row>+ <entry role="func_table_entry"><para role="func_signature">+ <type>time with time zone</type> <literal>AT LOCAL</literal>+ <returnvalue>time with time zone</returnvalue>+ </para>+ <para>+ Converts given time <emphasis>with</emphasis> time zone to a new time+ zone. Since no date is supplied, this uses the currently active UTC+ offset for the session's <varname>TimeZone</varname> value.+ </para>+ <para>+ Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:+ </para>+ <para>+ <literal>time with time zone '05:34:17-05' at local</literal>+ <returnvalue>10:34:17+00</returnvalue>+ </para></entry>+ </row>
</tbody>
</tgroup>
</table>
<para>
In these expressions, the desired time zone <replaceable>zone</replaceable> can be
specified either as a text value (e.g., <literal>'America/Los_Angeles'</literal>)
or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
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>++SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;+<lineannotation>Result: </lineannotation><computeroutput>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
<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>+ The fifth example is a cautionary tale. Due to the fact that there is no+ date associated with the input value, the conversion is made using the+ current date of the session. Therefore, this static example may show a wrong+ result depending on the time of the year it is viewed because+ <literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.
</para>
<para>
The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
<replaceable>timestamp</replaceable>)</literal> is equivalent to the SQL-conforming construct
<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>++ <para>+ The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,+ <replaceable>time</replaceable>)</literal> is equivalent to the SQL-conforming construct+ <literal><replaceable>time</replaceable> AT TIME ZONE <replaceable>zone</replaceable></literal>.+ </para>++ <para>+ The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>+ is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>+ AT LOCAL</literal>.+ </para>
</sect2>
<sect2 id="functions-datetime-current">
<title>Current Date/Time</title>
<indexterm>
<primary>date</primary>
<secondary>current</secondary>
</indexterm>
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@@ -14501,20 +14501,27 @@ a_expr: c_expr { $$ = $1; }
n->location = @2;
$$ = (Node *) n;
}
| a_expr AT TIME ZONE a_expr %prec AT
{
$$ = (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.
*
* If you add more explicitly-known operators, be sure to add them
* also to b_expr and to the MathOp list below.
*/
| '+' a_expr %prec UMINUS
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.cindex ae0f24de2c..e4d21098cb 100644--- a/src/backend/utils/adt/date.c+++ b/src/backend/utils/adt/date.c@@ -3118,10 +3118,24 @@ timetz_izone(PG_FUNCTION_ARGS)
result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
while (result->time < INT64CONST(0))
result->time += USECS_PER_DAY;
while (result->time >= USECS_PER_DAY)
result->time -= USECS_PER_DAY;
result->zone = tz;
PG_RETURN_TIMETZADT_P(result);
}
++/* timetz_at_local()+ *+ * Unlike for timestamp[tz]_at_local, the type for timetz does not flip between+ * time with/without time zone, so we cannot just call the conversion function.+ */+Datum+timetz_at_local(PG_FUNCTION_ARGS)+{+ Datum time = PG_GETARG_DATUM(0);+ Datum zone = PointerGetDatum(cstring_to_text(pg_get_timezone_name(session_timezone)));++ return DirectFunctionCall2(timetz_zone, zone, time);+}diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 442205382e..ed7f40f053 100644--- a/src/backend/utils/adt/ruleutils.c+++ b/src/backend/utils/adt/ruleutils.c@@ -10340,20 +10340,30 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
/* AT TIME ZONE ... note reversed argument order */
appendStringInfoChar(buf, '(');
get_rule_expr_paren((Node *) lsecond(expr->args), context, false,
(Node *) expr);
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:+ case F_TIMEZONE_TIMETZ:+ /* 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:
case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_INTERVAL:
case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_TIMESTAMP:
case F_OVERLAPS_TIMETZ_TIMETZ_TIMETZ_TIMETZ:
case F_OVERLAPS_TIME_INTERVAL_TIME_INTERVAL:
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@@ -5914,10 +5914,30 @@ Datum
generate_series_timestamptz(PG_FUNCTION_ARGS)
{
return generate_series_timestamptz_internal(fcinfo);
}
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..b33fc94502 100644--- a/src/include/catalog/pg_proc.dat+++ b/src/include/catalog/pg_proc.dat@@ -2312,20 +2312,26 @@
proargtypes => 'timestamptz timestamptz', prosrc => 'timestamp_ge' },
{ oid => '1157',
proname => 'timestamptz_gt', proleakproof => 't', prorettype => 'bool',
proargtypes => 'timestamptz timestamptz', prosrc => 'timestamp_gt' },
{ 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', provolatile => 's', prorettype => 'timestamp',+ proargtypes => 'timestamptz', prosrc => 'timestamptz_at_local' },+{ oid => '9160', descr => 'adjust timestamp to local time zone',+ proname => 'timezone', provolatile => 's', 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',
proargtypes => 'interval', prosrc => 'interval_out' },
{ oid => '2903', descr => 'I/O typmod',
proname => 'intervaltypmodin', prorettype => 'int4',
proargtypes => '_cstring', prosrc => 'intervaltypmodin' },
@@ -6088,20 +6094,23 @@
proargtypes => 'timestamp timestamp', prosrc => 'timestamp_smaller' },
{ oid => '2036', descr => 'larger of two',
proname => 'timestamp_larger', prorettype => 'timestamp',
proargtypes => 'timestamp timestamp', prosrc => 'timestamp_larger' },
{ oid => '2037', descr => 'adjust time with time zone to new zone',
proname => 'timezone', provolatile => 's', prorettype => 'timetz',
proargtypes => 'text timetz', prosrc => 'timetz_zone' },
{ oid => '2038', descr => 'adjust time with time zone to new zone',
proname => 'timezone', prorettype => 'timetz',
proargtypes => 'interval timetz', prosrc => 'timetz_izone' },
+{ oid => '9161', descr => 'adjust time to local time zone',+ proname => 'timezone', provolatile => 's', prorettype => 'timetz',+ proargtypes => 'timetz', prosrc => 'timetz_at_local' },
{ oid => '2039', descr => 'hash',
proname => 'timestamp_hash', prorettype => 'int4', proargtypes => 'timestamp',
prosrc => 'timestamp_hash' },
{ oid => '3411', descr => 'hash',
proname => 'timestamp_hash_extended', prorettype => 'int8',
proargtypes => 'timestamp int8', prosrc => 'timestamp_hash_extended' },
{ oid => '2041', descr => 'intervals overlap?',
proname => 'overlaps', proisstrict => 'f', prorettype => 'bool',
proargtypes => 'timestamp timestamp timestamp timestamp',
prosrc => 'overlaps_timestamp' },
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.outindex 0dd2fe2c82..64fdc64a14 100644--- a/src/test/regress/expected/timestamptz.out+++ b/src/test/regress/expected/timestamptz.out@@ -3128,20 +3128,67 @@ SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
--------------------------
Sun Oct 26 01:00:01 2014
(1 row)
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)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on tmptz
diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.outindex be49588b6d..a9a3a49ebb 100644--- a/src/test/regress/expected/timetz.out+++ b/src/test/regress/expected/timetz.out@@ -255,10 +255,49 @@ SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
-----------
25.575401
(1 row)
SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
date_part
--------------
63025.575401
(1 row)
+--+-- test LOCAL time zone+--+BEGIN;+SET LOCAL TimeZone TO 'UTC';+CREATE VIEW time_local AS+SELECT f1,+ f1 AT LOCAL AS f2,+ timezone(f1) AS f3,+ f1 AT TIME ZONE current_setting('TimeZone') AS f4+FROM TIMETZ_TBL+ORDER BY f1;+\sv time_local+CREATE OR REPLACE VIEW public.time_local AS+ SELECT f1,+ (f1 AT LOCAL) AS f2,+ timezone(f1) AS f3,+ (f1 AT TIME ZONE current_setting('TimeZone'::text)) AS f4+ FROM timetz_tbl+ ORDER BY f1+TABLE time_local;+ f1 | f2 | f3 | f4 +----------------+----------------+----------------+----------------+ 00:01:00-07 | 07:01:00+00 | 07:01:00+00 | 07:01:00+00+ 01:00:00-07 | 08:00:00+00 | 08:00:00+00 | 08:00:00+00+ 02:03:00-07 | 09:03:00+00 | 09:03:00+00 | 09:03:00+00+ 08:08:00-04 | 12:08:00+00 | 12:08:00+00 | 12:08:00+00+ 07:07:00-08 | 15:07:00+00 | 15:07:00+00 | 15:07:00+00+ 11:59:00-07 | 18:59:00+00 | 18:59:00+00 | 18:59:00+00+ 12:00:00-07 | 19:00:00+00 | 19:00:00+00 | 19:00:00+00+ 12:01:00-07 | 19:01:00+00 | 19:01:00+00 | 19:01:00+00+ 15:36:39-04 | 19:36:39+00 | 19:36:39+00 | 19:36:39+00+ 15:36:39-05 | 20:36:39+00 | 20:36:39+00 | 20:36:39+00+ 23:59:00-07 | 06:59:00+00 | 06:59:00+00 | 06:59:00+00+ 23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00+(12 rows)++DROP VIEW time_local;+COMMIT;diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sqlindex 69b36d0420..53b5878e21 100644--- a/src/test/regress/sql/timestamptz.sql+++ b/src/test/regress/sql/timestamptz.sql@@ -604,18 +604,39 @@ SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
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)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sqlindex 93c7bb1428..a274ce7423 100644--- a/src/test/regress/sql/timetz.sql+++ b/src/test/regress/sql/timetz.sql@@ -77,10 +77,27 @@ SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30');
SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04:30');
SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
-- date_part implementation is mostly the same as extract, so only
-- test a few cases for additional coverage.
SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
++--+-- test LOCAL time zone+--+BEGIN;+SET LOCAL TimeZone TO 'UTC';+CREATE VIEW time_local AS+SELECT f1,+ f1 AT LOCAL AS f2,+ timezone(f1) AS f3,+ f1 AT TIME ZONE current_setting('TimeZone') AS f4+FROM TIMETZ_TBL+ORDER BY f1;+\sv time_local+TABLE time_local;+DROP VIEW time_local;+COMMIT;
base-commit: 82a7132f531b8b12e77d17476e9bfd599c3c30cf
--
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: <a2bd6444-7fe4-44cd-8cb6-d7ac13f4d3e0@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