Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUYOh-0004Pb-LN for pgsql-hackers@arkaria.postgresql.org; Thu, 08 Apr 2021 17:24:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lUYOg-0006VV-Jl for pgsql-hackers@arkaria.postgresql.org; Thu, 08 Apr 2021 17:24:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUYOg-0006VI-CK for pgsql-hackers@lists.postgresql.org; Thu, 08 Apr 2021 17:24:50 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUYOd-0007hq-OA for pgsql-hackers@postgresql.org; Thu, 08 Apr 2021 17:24:49 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1lUYOa-0001wF-Py; Thu, 08 Apr 2021 13:24:44 -0400 Date: Thu, 8 Apr 2021 13:24:44 -0400 From: Bruce Momjian To: Justin Pryzby Cc: Bryn Llewellyn , Zhihong Yu , Isaac Morland , John W Higgins , pgsql-hackers@postgresql.org, Tom Lane Subject: Re: Have I found an interval arithmetic bug? Message-ID: <20210408172444.GC6909@momjian.us> References: <20210403022126.GG29126@momjian.us> <20210405163310.GP6592@telsasoft.com> <20210405180158.GB12179@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="17pEHd4RhPHOinZp" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <20210405180158.GB12179@momjian.us> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --17pEHd4RhPHOinZp Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit 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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion. --17pEHd4RhPHOinZp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="interval.diff" From 0b5bf02e5baaa29e74fac9ac3823d593b6c5e3f2 Mon Sep 17 00:00:00 2001 From: Bruce Momjian 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.sgml index 7c341c8e3f..9454107e75 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2770,15 +2770,13 @@ P years-months- - In the verbose input format, and in some fields of the more compact - input formats, 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. + Field values can have fractional parts; for example, '1.5 + weeks' or '01:02:03.45'. The fractional + parts are used to compute appropriate values for the next lower-order + internal fields (months, days, seconds). For example, '1.5 + months' becomes 1 month 15 days. + 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. diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 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.c index 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.out index 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.sql index 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 --17pEHd4RhPHOinZp--