Thread: Re: [BUGS] Date calc bug

[ Forwarded to hackers list from bugs list ]

Olivier PRENANT <ohp@pyrenet.fr> writes:
> ohp=> select '01-12-1999'::datetime + '@ 1 month - 1 sec' as bug;
> Thu 30 Dec 23:59:59 1999 MET

I see it here too, in a different timezone:

select '12-01-1999'::datetime + '@ 1 month - 1 sec' ;
Thu Dec 30 23:59:59 1999 EST

It's not a Y2K issue, because of this similar failure:

select '3-01-1999'::datetime + '@ 1month - 1 sec'::timespan;
Sun Mar 28 23:59:59 1999 EST

See the pattern?  I suspect what is going on is that the low-order
(seconds) part of the timespan is being added in before the high-order
(months) part.  If you did the calculation in two steps like this:

select '12-01-1999'::datetime + '@ - 1 sec'::timespan;
Tue Nov 30 23:59:59 1999 EST
select 'Tue Nov 30 23:59:59 1999 EST'::datetime + '@ 1 month'::timespan;
Thu Dec 30 23:59:59 1999 EST

then you'd think the result is reasonable.

The question for discussion is whether adding the months part and then
the seconds part would give more reasonable answers overall.  Are there
other cases where doing it that way would yield nonintuitive results,
but the current code works?

Thomas, do you know why the datetime+timespan addition code works like
this?  For that matter, is the internal representation of a timespan
going to continue to be months + seconds, or is that changing anyway?
        regards, tom lane

> forum=> select datetime(now())+'74565 days'::timespan as ido;
> Thu Jan 19 14:07:30 2068
and
> select '12-01-1999'::datetime + '@ 1 month - 1 sec' ;
> Thu Dec 30 23:59:59 1999 EST

I've repaired both problems in both the development and release trees.
Thanks for the reports and analysis. Patch enclosed...
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California*** ../src/backend/utils/adt/dt.c.orig    Mon Jan  3 08:27:24 2000
--- ../src/backend/utils/adt/dt.c    Mon Jan  3 16:41:08 2000
***************
*** 787,792 ****
--- 787,793 ----  * To add a month, increment the month, and use the same day of month.  * Then, if the next month has
fewerdays, set the day of month  *    to the last day of month.
 
+  * Lastly, add in the "quantitative time".  */ DateTime   * datetime_pl_span(DateTime *datetime, TimeSpan *span)
***************
*** 815,826 ****     {         dt = (DATETIME_IS_RELATIVE(*datetime) ? SetDateTime(*datetime) : *datetime); 
- #ifdef ROUND_ALL
-         dt = JROUND(dt + span->time);
- #else
-         dt += span->time;
- #endif
-          if (span->month != 0)         {             struct tm    tt,
--- 816,821 ----
***************
*** 853,858 ****
--- 848,859 ----                 DATETIME_INVALID(dt);         } 
+ #ifdef ROUND_ALL
+         dt = JROUND(dt + span->time);
+ #else
+         dt += span->time;
+ #endif
+          *result = dt;     } 
***************
*** 2441,2447 **** tm2timespan(struct tm * tm, double fsec, TimeSpan *span) {     span->month = ((tm->tm_year * 12) +
tm->tm_mon);
!     span->time = ((((((tm->tm_mday * 24) + tm->tm_hour) * 60) + tm->tm_min) * 60) + tm->tm_sec);     span->time =
JROUND(span->time+ fsec);      return 0;
 
--- 2442,2451 ---- tm2timespan(struct tm * tm, double fsec, TimeSpan *span) {     span->month = ((tm->tm_year * 12) +
tm->tm_mon);
!     span->time = ((((((tm->tm_mday * 24.0)
!                      + tm->tm_hour) * 60.0)
!                      + tm->tm_min) * 60.0)
!                      + tm->tm_sec);     span->time = JROUND(span->time + fsec);      return 0;

Thanks you!!

Patch applied and tested.

Great job.

That's exactly the reason why I love Internet and postresql.

You find a bug.. Send a message and correct it.

Best wishes for 2000.

Regards

On Tue, 4 Jan 2000, Thomas Lockhart wrote:

> > forum=> select datetime(now())+'74565 days'::timespan as ido;
> > Thu Jan 19 14:07:30 2068
> and
> > select '12-01-1999'::datetime + '@ 1 month - 1 sec' ;
> > Thu Dec 30 23:59:59 1999 EST
> 
> I've repaired both problems in both the development and release trees.
> Thanks for the reports and analysis. Patch enclosed...
> 
>                       - Thomas
> 
> 

-- 
Olivier PRENANT             Tel:    +33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou           +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                      +33-6-07-63-80-64 (GSM)
FRANCE                      Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)