Обсуждение: 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)