Thread
-
Date calc bug
ohp@pyrenet.fr — 2000-01-02T14:15:47Z
Happy new year to you all!! I've run into this problem 3 days ago: Script started on Sun Jan 2 14:59:03 2000 ~ 14:59:04: psql Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i586-pc-unixware7.0.1, compiled by cc ] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: ohp ohp=> select '01-12-1999'::datetime + '@ 1 month - 1 sec' as bug; bug ---------------------------- Thu 30 Dec 23:59:59 1999 MET (1 row) ^^ Should be 31!! ohp=> select '01-01-2000'::datetime + '@ 1 month - 1 sec' as good; good ---------------------------- Mon 31 Jan 23:59:59 2000 MET (1 row) Seems OK after Jan 1rst!! Any idea? Regards -- 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) -
Re: [BUGS] Date calc bug
Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-02T18:02:00Z
[ 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
-
Re: [BUGS] Date calc bug
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-01-04T08:11:32Z
> 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 -
Re: [BUGS] Date calc bug
ohp@pyrenet.fr — 2000-01-04T18:07:38Z
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)