When trying to improve the rounding in interval_div and interval_mul,
I came across some behavior that seems counterintuitive to me:
test=# select '1.5 mon'::interval;
interval
-----------------
1 mon 360:00:00
(1 row)
With the time/day/month interval struct introduced in 8.1, I'd expect
this to return '1 mon 15 days'. The reason is that the DecodeInterval
converts fractional months to time directly, rather than cascading
first to days.
Similar behavior happens with weeks:
select '1.5 week'::interval;
interval
-----------------
7 days 84:00:00
(1 row)
Similarly, I believe should return 10 days 12 hours (7 days + 3.5 days).
I've patched DecodeInterval and the regression tests to check this. I
think tmask lines need to be updated, but I'm not sure how these work
so I've left them as is. I'd appreciate it if someone could look at
these areas in particular.
I think this is a behavior changing bug fix, as it was the intention
of the Interval struct change to treat days and time differently.
This patch brings the DecodeInterval function more in line with that
intention.
Thanks for your consideration.
Michael Glaesemann
grzm seespotcode net
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.169
diff -c -r1.169 datetime.c
*** src/backend/utils/adt/datetime.c 25 Jul 2006 03:51:21 -0000 1.169
--- src/backend/utils/adt/datetime.c 27 Aug 2006 23:25:53 -0000
***************
*** 2920,2935 ****
tm->tm_mday += val * 7;
if (fval != 0)
{
! int
sec;
!
! fval *= 7 *
SECS_PER_DAY;
! sec = fval;
! tm->tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
1000000;
#else
! *fsec += fval - sec;
#endif
}
tmask = (fmask & DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
--- 2920,2942 ----
tm->tm_mday += val * 7;
if (fval != 0)
{
! int extra_days;
! fval *= 7;
! extra_days = (int32)
fval;
! tm->tm_mday +=
extra_days;
! fval -= extra_days;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm->tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 1000000;
#else
! *fsec += fval -
sec;
#endif
+ }
}
tmask = (fmask & DTK_M(DAY)) ?
0 : DTK_M(DAY);
break;
***************
*** 2938,2953 ****
tm->tm_mon += val;
if (fval != 0)
{
! int
sec;
!
! fval *= DAYS_PER_MONTH
* SECS_PER_DAY;
! sec = fval;
! tm->tm_sec += sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval - sec) *
1000000;
#else
! *fsec += fval - sec;
#endif
}
tmask = DTK_M(MONTH);
break;
--- 2945,2967 ----
tm->tm_mon += val;
if (fval != 0)
{
! int day;
! fval *= DAYS_PER_MONTH;
! day = fval;
! tm->tm_mday += day;
! fval -= day;
! if (fval != 0)
! {
! int
sec;
! fval *=
SECS_PER_DAY;
! sec = fval;
! tm->tm_sec +=
sec;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec += (fval
- sec) * 1000000;
#else
! *fsec += fval -
sec;
#endif
+ }
}
tmask = DTK_M(MONTH);
break;
Index: src/test/regress/expected/interval.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/
interval.out,v
retrieving revision 1.15
diff -c -r1.15 interval.out
*** src/test/regress/expected/interval.out 6 Mar 2006 22:49:17 -0000
1.15
--- src/test/regress/expected/interval.out 27 Aug 2006 23:25:56 -0000
***************
*** 39,44 ****
--- 39,56 ----
-1 days +02:03:00
(1 row)
+ SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
+ Ten days twelve hours
+ -----------------------
+ 10 days 12:00:00
+ (1 row)
+
+ SELECT INTERVAL '1.5 months' AS "One month 15 days";
+ One month 15 days
+ -------------------
+ 1 mon 15 days
+ (1 row)
+
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years...
----------------------------------
Index: src/test/regress/sql/interval.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/interval.sql,v
retrieving revision 1.9
diff -c -r1.9 interval.sql
*** src/test/regress/sql/interval.sql 6 Mar 2006 22:49:17 -0000 1.9
--- src/test/regress/sql/interval.sql 27 Aug 2006 23:25:56 -0000
***************
*** 11,16 ****
--- 11,18 ----
SELECT INTERVAL '-05' AS "Five 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 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
CREATE TABLE INTERVAL_TBL (f1 interval);
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
