The following bug has been logged online:

Bug reference: 2443
Logged by: nikolaus klepp
Email address: dr.klepp (AT) gmx (DOT) 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"nikolaus klepp" <dr.klepp (AT) gmx (DOT) at> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match