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)


Sun, 2 Jan 2000 15:15:47 +0100
Olivier PRENANT <ohp@pyrenet.fr>
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

Sun, 02 Jan 2000 13:02:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] Date calc bug 
> 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
Tue, 04 Jan 2000 08:11:32 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [BUGS] Date calc bug
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)
Tue, 4 Jan 2000 19:07:38 +0100 (MET)
Olivier PRENANT <ohp@pyrenet.fr>
Re: [BUGS] Date calc bug