From 95ab1e0a9db321dd796344d526457016eada027f Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 3 Aug 2021 12:10:29 -0400 Subject: [PATCH] interval: round values when spilling to months Previously spilled units greater than months were truncated to months. Also document the spill behavior. Reported-by: Bryn Llewelly Discussion: https://postgr.es/m/BDAE4B56-3337-45A2-AC8A-30593849D6C0@yugabyte.com Backpatch-through: master --- doc/src/sgml/datatype.sgml | 25 +++++++++++++---------- src/backend/utils/adt/datetime.c | 16 ++++++--------- src/interfaces/ecpg/pgtypeslib/interval.c | 16 ++++++--------- 3 files changed, 26 insertions(+), 31 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 453115f942f..50a2c8e5f16 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2840,15 +2840,18 @@ 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'. However, + because interval internally stores only three integer units (months, + days, microseconds), fractional units must be spilled to smaller + units. Fractional parts of units greater than months is rounded to + be an integer number of months, e.g. '1.5 years' + becomes '1 year 6 mons'. Fractional parts of + weeks and days are computed to be an integer number of days and + microseconds, assuming 30 days per month and 24 hours per day, e.g., + '1.75 months' becomes 1 mon 22 days + 12:00:00. Only seconds will ever be shown as fractional + on output. @@ -2892,10 +2895,10 @@ P years-months- Internally interval values are stored as months, days, - and seconds. This is done because the number of days in a month + and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers - while the seconds field can store fractions. Because intervals are + while the microseconds field can store fractional seconds. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results: diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 54ae632de24..cb3fa85892b 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -3306,29 +3306,25 @@ 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 +3561,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 +3597,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 02b3c472233..a7e530cb5d2 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') @@ -528,29 +528,25 @@ 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; -- 2.30.2