Обсуждение: Bug in Time/Date routines

Hi Folks !

I just found a bug in the postgresql time/date routines.

Try the following in the psql shell :

select ('10-01-2000'::date + ('1 month')::timespan)::date;

The result is going to be

10-31-2000

But it should be

11-01-2000

If you take any other month than October, it is working fine. So 09-01-2000 + 1 month => 10-01-2000.

I've tested this with postgresql 6.5.3 as well as 7.0.2

Thanks for the good work....

Moritz

P.S. what about a way to cancel a query that is waiting for a locked table / row ?

Moritz Gmelin <moritz.gmelin@gmx.de> writes:
> If you take any other month than October, it is working fine. So
> 09-01-2000 + 1 month => 10-01-2000.

Hmm, I was expecting to find that fixed in current sources, but it's
still broken:

regression=# select ('10-01-2000'::timestamp + ('1 month')::timespan);
        ?column?
------------------------
 2000-10-31 23:00:00-05
(1 row)

Thomas, isn't the addition of '1 month' done symbolically in a struct tm?
I suspect you may have forgotten to set tm_isdst = -1 before invoking
mktime(), causing it to interpret the time of day on the target date
as the same DST or not-DST case that applied on the initial date.
Seems to me that both times should be taken as "local time", so you
should force mktime() to recompute whether DST is in effect or not.

OTOH, it's much less clear whether that's a good idea when the timespan
is expressed in hours... maybe do this only if the timespan had a
nonzero "months" component?  (Really, timespans should account for
months, days, and hours separately --- it's false that 1 day == 24 hours
-- so IMHO a reasonable implementation would reset isdst if either the
months or days component was nonzero.  I don't have time to fix that for
7.1, however.)

> P.S. what about a way to cancel a query that is waiting for a locked table / row ?

That works in 7.0.*, no?  Sure looks like it works to me:

play=> begin;
BEGIN
play=> lock table int4_tbl;
-- type control-C
Cancel request sent
ERROR:  Query cancel requested while waiting lock
play=>


            regards, tom lane

> > If you take any other month than October, it is working fine. So
> > 09-01-2000 + 1 month => 10-01-2000.
> regression=# select ('10-01-2000'::timestamp + ('1 month')::timespan);
>         ?column?
> ------------------------
>  2000-10-31 23:00:00-05
> (1 row)
> Thomas, isn't the addition of '1 month' done symbolically in a struct tm?
> I suspect you may have forgotten to set tm_isdst = -1 before invoking
> mktime(), causing it to interpret the time of day on the target date
> as the same DST or not-DST case that applied on the initial date.
> Seems to me that both times should be taken as "local time", so you
> should force mktime() to recompute whether DST is in effect or not.

That particular operation needs mktime() called twice in some cases :(

Once for the input timestamp and once for the intermediate result. I'm
testing a fix now which gives the right result for this particular case.

                   - Tom