Обсуждение: BUG #2443: 1 hour error at date calculation

The following bug has been logged online:

Bug reference:      2443
Logged by:          nikolaus klepp
Email address:      dr.klepp@gmx.at
PostgreSQL version: 8.0.3, 7.4.8
Operating system:   suse linux 10.0
Description:        1 hour error at date calculation
Details:

i want to calculate the number of days in a month. when I do it this way:

select date_trunc('month', date_trunc('month', date('2006-3-1'))+interval
'1month')-date_trunc('month', date('2006-3-1'));

the result is: 30 days 23:00:00
that is obviously wrong.

"nikolaus klepp" <dr.klepp@gmx.at> writes:
> i want to calculate the number of days in a month. when I do it this way:

> select date_trunc('month', date_trunc('month', date('2006-3-1'))+interval
> '1month')-date_trunc('month', date('2006-3-1'));

> the result is: 30 days 23:00:00
> that is obviously wrong.

No, it's not wrong (I assume you live in a timezone where DST switches
during March).  Subtraction of two timestamps gives the number of hours
between them, and that's what you've got.

If you want the number of days without regard to minor details like DST
changes, cast the two values back to plain date before subtracting.

For me, DST changes in April, and I get:

regression=# select date_trunc('month', date_trunc('month', date('2006-4-1'))+i
nterval '1month')-date_trunc('month', date('2006-4-1'));
     ?column?
------------------
 29 days 23:00:00
(1 row)

regression=# select date_trunc('month', date_trunc('month', date('2006-4-1'))+i
nterval '1month')::date - date_trunc('month', date('2006-4-1'))::date;
 ?column?
----------
       30
(1 row)

regression=#

            regards, tom lane