>
>
>> Yeah, I really didn't expect to change the behavior, but wanted to make
> sure that the existing behavior was understood. I'll whip up a patch.
>
Attached is an attempt at an explanation of the edge cases I was
encountering, as well as some examples. If nothing else, the examples will
draw eyes and searches to the explanations that were already there.
From 618a7fbd5606510b993697a0a1968fde5f02fbb2 Mon Sep 17 00:00:00 2001
From: coreyhuinker <corey.huin...@gmail.com>
Date: Sun, 19 Sep 2021 22:34:42 -0400
Subject: [PATCH 1/2] Explain some of the nuances with INTERVAL data when the
string literal offers fewer positions of information than than the fields
spefication requires.
---
doc/src/sgml/datatype.sgml | 25 ++++++++++++++++++++++++-
1 file changed, 24 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 50a2c8e5f1..83ebb68333 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2816,7 +2816,30 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
defined with a <replaceable>fields</replaceable> specification, the interpretation of
unmarked quantities depends on the <replaceable>fields</replaceable>. For
example <literal>INTERVAL '1' YEAR</literal> is read as 1 year, whereas
- <literal>INTERVAL '1'</literal> means 1 second. Also, field values
+ <literal>INTERVAL '1'</literal> means 1 second. If the string provided contains
+ a <literal>:</literal> then the number to the left of the first <literal>:</literal>
+ will be used to fill in the most significant sub-day part of the
+ <replaceable>fields</replaceable> speficification.
+
+<programlisting>
+SELECT INTERVAL '2' HOUR TO SECOND;
+ interval
+----------
+ 00:00:02
+
+SELECT INTERVAL '4:2' HOUR TO SECOND;
+ interval
+----------
+ 04:02:00
+
+
+SELECT INTERVAL '2:' DAY TO SECOND;
+ interval
+----------
+ 02:00:00
+</programlisting>
+
+ Also, field values
<quote>to the right</quote> of the least significant field allowed by the
<replaceable>fields</replaceable> specification are silently discarded. For
example, writing <literal>INTERVAL '1 day 2:03:04' HOUR TO MINUTE</literal>
--
2.30.2
From 042467c84a4f9d3cbbdb7660b0b174d99dadde9d Mon Sep 17 00:00:00 2001
From: coreyhuinker <corey.huin...@gmail.com>
Date: Sun, 19 Sep 2021 23:26:28 -0400
Subject: [PATCH 2/2] Add example to show the effect of AT TIME ZONE INTERVAL.
---
doc/src/sgml/func.sgml | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 78812b2dbe..eee10f2db9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10435,6 +10435,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 '2001-02-16 20:38:40' AT TIME ZONE 'UTC' AT TIME ZONE INTERVAL '3:21:20' HOUR TO SECOND;
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-17 00:00:00+00</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>
@@ -10442,7 +10445,8 @@ 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 adds the time zone UTC
+ to a value that lacks it, and then applies a highly contrived fixed offset.
</para>
<para>
--
2.30.2
