diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e016f96fb4..e831242bf6 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2800,15 +2800,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, seconds), fractional units must be spilled to smaller units. + For example, because months are approximated to equal 30 days, + fractional values of units greater than months is rounded to be the + nearest integer number of months. Fractional months are rounded to + be the nearest integer number of days, assuming 24 hours per day. + Fractional units smaller than months are computed to the nearest + second. For example, '1.5 months' becomes + 1 month 15 days. 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 54ae632de2..5551102447 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -3300,35 +3300,43 @@ DecodeInterval(char **field, int *ftype, int nf, int range, case DTK_MONTH: tm->tm_mon += val; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* + * The DAYS_PER_MONTH is an estimate so just round + * to days, rather than spilling to seconds. + */ + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); tmask = DTK_M(MONTH); break; 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,11 +3573,15 @@ 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; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); break; case 'W': tm->tm_mday += val * 7; @@ -3601,7 +3613,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') @@ -3615,7 +3627,11 @@ DecodeISO8601Interval(char *str, if (dterr) return dterr; tm->tm_mon += val; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); if (*str == '\0') return 0; if (*str == 'T') diff --git a/src/interfaces/ecpg/pgtypeslib/interval.c b/src/interfaces/ecpg/pgtypeslib/interval.c index 02b3c47223..1a1cb51945 100644 --- a/src/interfaces/ecpg/pgtypeslib/interval.c +++ b/src/interfaces/ecpg/pgtypeslib/interval.c @@ -155,11 +155,15 @@ 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; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); break; case 'W': tm->tm_mday += val * 7; @@ -191,7 +195,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') @@ -205,7 +209,11 @@ DecodeISO8601Interval(char *str, if (dterr) return dterr; tm->tm_mon += val; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); if (*str == '\0') return 0; if (*str == 'T') @@ -522,35 +530,43 @@ DecodeInterval(char **field, int *ftype, int nf, /* int range, */ case DTK_MONTH: tm->tm_mon += val; - AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH); + /* + * The DAYS_PER_MONTH is an estimate so just round + * to days, rather than spilling to seconds. + */ + /* round to a full month? */ + if (rint(fval * DAYS_PER_MONTH) == DAYS_PER_MONTH) + tm->tm_mon++; + else + tm->tm_mday += rint(fval * DAYS_PER_MONTH); tmask = DTK_M(MONTH); break; 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 e4b1246f45..eae18da946 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -46,6 +46,18 @@ SELECT INTERVAL '1.5 months' AS "One month 15 days"; 1 mon 15 days (1 row) +SELECT INTERVAL '1.16 months 01:00:00' AS "One mon 5 days one hour"; + One mon 5 days one hour +------------------------- + 1 mon 5 days 01:00:00 +(1 row) + +SELECT INTERVAL '1.99 months' AS "Almost two months"; + Almost two months +------------------- + 2 mons +(1 row) + SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; 9 years... ---------------------------------- diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 6d532398bd..5a6c8924fe 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -13,6 +13,8 @@ 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 months' AS "One month 15 days"; +SELECT INTERVAL '1.16 months 01:00:00' AS "One mon 5 days one hour"; +SELECT INTERVAL '1.99 months' AS "Almost two months"; SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; CREATE TABLE INTERVAL_TBL (f1 interval);