Re: Interval month, week -> day

On Sep 3, 2006, at 20:00 , Michael Glaesemann wrote:

>
> On Sep 1, 2006, at 9:32 , Tom Lane wrote:
>
>> Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
>>> On Sep 1, 2006, at 9:12 , Tom Lane wrote:
>>>> I agree that this seems like an oversight in the original
>>>> months/days/seconds patch, rather than behavior we want to keep.
>>>> But is DecodeInterval the only place with the problem?
>>
>>> I'll check on this tonight. Any idea where I might start to look?
>>
>> I'd look at the input routines for all the datetime types and see
>> where
>> they go. It's entirely possible that DecodeInterval is the only
>> place
>> with the problem, but I'd not assume that without looking.
>
> AFAICS, DecodeInterval is the only place that needed changing. I've
> looked through datetime.c, timestamp.c, date.c, and nabstime.c, and
> don't see anything else. It makes sense, too, as the only place
> where you could have weeks or non-integer months is during Interval
> input or interval multiplication/division. The pg_tm struct, which
> is used in time(stamp)?(tz)?/interval arithmetic only has integral
> months and no weeks component, so that shouldn't cause any
> problems. So, I think that's about it.

I realized there might be something in ecpg, and there was. I've
updated the ecpg DecodeInterval to match. However, I haven't been
able to get ecpg make check to work, so that part's untested.

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 3 Sep 2006 23:55:34 -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/interfaces/ecpg/pgtypeslib/interval.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/
interval.c,v
retrieving revision 1.32
diff -c -r1.32 interval.c
*** src/interfaces/ecpg/pgtypeslib/interval.c 6 Jun 2006 11:31:55
-0000 1.32
--- src/interfaces/ecpg/pgtypeslib/interval.c 3 Sep 2006 23:55:34 -0000
***************
*** 307,322 ****
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;
--- 307,329 ----
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;
***************
*** 325,340 ****
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;
--- 332,354 ----
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.16
diff -c -r1.16 interval.out
*** src/test/regress/expected/interval.out 3 Sep 2006 03:34:04 -0000
1.16
--- src/test/regress/expected/interval.out 3 Sep 2006 23:55:35 -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 3 Sep 2006 23:55:35 -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);