From: Bruce Momjian <bruce@momjian.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Bryn Llewellyn <bryn@yugabyte.com>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: Isaac Morland <isaac.morland@gmail.com>
Cc: John W Higgins <wishdev@gmail.com>
Cc: pgsql-hackers@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Have I found an interval arithmetic bug?
Date: Thu, 8 Apr 2021 13:24:44 -0400
Message-ID: <20210408172444.GC6909@momjian.us> (raw)
In-Reply-To: <20210405180158.GB12179@momjian.us>
References: <E34DA978-0C0E-4F43-97CD-278FA0C228E5@yugabyte.com>
<20210403022126.GG29126@momjian.us>
<20210405163310.GP6592@telsasoft.com>
<20210405180158.GB12179@momjian.us>
On Mon, Apr 5, 2021 at 02:01:58PM -0400, Bruce Momjian wrote:
> On Mon, Apr 5, 2021 at 11:33:10AM -0500, Justin Pryzby wrote:
> Well, bug or not, we are not going to change back branches for this, and
> if you want a larger discussion, it will have to wait for PG 15.
>
> > > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> > > « …field values can have fractional parts; for example '1.5 week' or '01:02:03.45'. Such input is converted to the appropriate number of months, days, and seconds for storage. When this would result in a fractional number of months or days, the fraction is added to the lower-order fields using the conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output. »
>
> I see that. What is not clear here is how far we flow down. I was
> looking at adding documentation or regression tests for that, but was
> unsure. I adjusted the docs slightly in the attached patch.
Here is an updated patch, which will be for PG 15. It updates the
documentation to state:
The fractional parts are used to compute appropriate values for the next
lower-order internal fields (months, days, seconds).
It removes the flow from fractional months/weeks to
hours-minutes-seconds, and adds missing rounding for fractional
computations.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Attachments:interval.diff (text/x-diff, 7.0K) download
From 0b5bf02e5baaa29e74fac9ac3823d593b6c5e3f2 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Thu, 8 Apr 2021 13:07:16 -0400
Subject: [PATCH] interval squash commit
---
doc/src/sgml/datatype.sgml | 16 +++++++---------
src/backend/utils/adt/datetime.c | 13 ++++++-------
src/interfaces/ecpg/pgtypeslib/interval.c | 12 ++++++------
src/test/regress/expected/interval.out | 8 ++++----
src/test/regress/sql/interval.sql | 2 +-
5 files changed, 24 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlindex 7c341c8e3f..9454107e75 100644--- a/doc/src/sgml/datatype.sgml+++ b/doc/src/sgml/datatype.sgml@@ -2770,15 +2770,13 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
</para>
<para>
- In the verbose input format, and in some fields of the more compact- input formats, field values can have fractional parts; for example- <literal>'1.5 week'</literal> or <literal>'01:02:03.45'</literal>. Such input is- converted to the appropriate number of months, days, and seconds- for storage. When this would result in a fractional number of- months or days, the fraction is added to the lower-order fields- using the conversion factors 1 month = 30 days and 1 day = 24 hours.- For example, <literal>'1.5 month'</literal> becomes 1 month and 15 days.- Only seconds will ever be shown as fractional on output.+ Field values can have fractional parts; for example, <literal>'1.5+ weeks'</literal> or <literal>'01:02:03.45'</literal>. The fractional+ parts are used to compute appropriate values for the next lower-order+ internal fields (months, days, seconds). For example, <literal>'1.5+ months'</literal> becomes <literal>1 month 15 days</literal>.+ The fractional conversion factors used are 1 month = 30 days and 1 day+ = 24 hours. Only seconds will ever be shown as fractional on output.
</para>
<para>
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.cindex 889077f55c..d5b3705145 100644--- a/src/backend/utils/adt/datetime.c+++ b/src/backend/utils/adt/datetime.c@@ -526,7 +526,6 @@ AdjustFractDays(double frac, struct pg_tm *tm, fsec_t *fsec, int scale)
extra_days = (int) frac;
tm->tm_mday += extra_days;
frac -= extra_days;
- AdjustFractSeconds(frac, tm, fsec, SECS_PER_DAY);
}
/* Fetch a fractional-second value with suitable error checking */
@@ -3307,28 +3306,28 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
case DTK_YEAR:
tm->tm_year += val;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
tmask = DTK_M(YEAR);
break;
case DTK_DECADE:
tm->tm_year += val * 10;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 10;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 10);
tmask = DTK_M(DECADE);
break;
case DTK_CENTURY:
tm->tm_year += val * 100;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 100;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 100);
tmask = DTK_M(CENTURY);
break;
case DTK_MILLENNIUM:
tm->tm_year += val * 1000;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 1000;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 1000);
tmask = DTK_M(MILLENNIUM);
break;
@@ -3565,7 +3564,7 @@ DecodeISO8601Interval(char *str,
{
case 'Y':
tm->tm_year += val;
- tm->tm_mon += (fval * MONTHS_PER_YEAR);+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
break;
case 'M':
tm->tm_mon += val;
@@ -3601,7 +3600,7 @@ DecodeISO8601Interval(char *str,
return DTERR_BAD_FORMAT;
tm->tm_year += val;
- tm->tm_mon += (fval * MONTHS_PER_YEAR);+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
if (unit == '\0')
return 0;
if (unit == 'T')
diff --git a/src/interfaces/ecpg/pgtypeslib/interval.c b/src/interfaces/ecpg/pgtypeslib/interval.cindex 4245016c8e..fccb9765ae 100644--- a/src/interfaces/ecpg/pgtypeslib/interval.c+++ b/src/interfaces/ecpg/pgtypeslib/interval.c@@ -155,7 +155,7 @@ DecodeISO8601Interval(char *str,
{
case 'Y':
tm->tm_year += val;
- tm->tm_mon += (fval * MONTHS_PER_YEAR);+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
break;
case 'M':
tm->tm_mon += val;
@@ -191,7 +191,7 @@ DecodeISO8601Interval(char *str,
return DTERR_BAD_FORMAT;
tm->tm_year += val;
- tm->tm_mon += (fval * MONTHS_PER_YEAR);+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
if (unit == '\0')
return 0;
if (unit == 'T')
@@ -529,28 +529,28 @@ DecodeInterval(char **field, int *ftype, int nf, /* int range, */
case DTK_YEAR:
tm->tm_year += val;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR);
tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
break;
case DTK_DECADE:
tm->tm_year += val * 10;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 10;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 10);
tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
break;
case DTK_CENTURY:
tm->tm_year += val * 100;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 100;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 100);
tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
break;
case DTK_MILLENNIUM:
tm->tm_year += val * 1000;
if (fval != 0)
- tm->tm_mon += fval * MONTHS_PER_YEAR * 1000;+ tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 1000);
tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
break;
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.outindex 0191949137..3f948f56c0 100644--- a/src/test/regress/expected/interval.out+++ b/src/test/regress/expected/interval.out@@ -34,10 +34,10 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
-1 day +02:03:00
(1 row)
-SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";- Ten days twelve hours ------------------------- 10 days 12:00:00+SELECT INTERVAL '1.5 weeks' AS "Ten days";+ Ten days +----------+ 10 days
(1 row)
SELECT INTERVAL '1.5 months' AS "One month 15 days";
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sqlindex 6d532398bd..58bc06178c 100644--- a/src/test/regress/sql/interval.sql+++ b/src/test/regress/sql/interval.sql@@ -11,7 +11,7 @@ SELECT INTERVAL '+02:00' AS "Two hours";
SELECT INTERVAL '-08:00' AS "Eight hours";
SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
-SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";+SELECT INTERVAL '1.5 weeks' AS "Ten days";
SELECT INTERVAL '1.5 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
--
2.20.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: bruce@momjian.us, pryzby@telsasoft.com, bryn@yugabyte.com, zyu@yugabyte.com, isaac.morland@gmail.com, wishdev@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Have I found an interval arithmetic bug?
In-Reply-To: <20210408172444.GC6909@momjian.us>
* 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