Re: [PATCHES] Interval month, week -> day - Mailing list pgsql-hackers

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

>
> On Sep 1, 2006, at 9:32 , Tom Lane wrote:
>
>> Michael Glaesemann <grzm@seespotcode.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);