"nikolaus klepp" <[EMAIL PROTECTED]> 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
---------------------------(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
