Re: [PATCHES] 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  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);