Re: BUG #2443: 1 hour error at date calculation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "nikolaus klepp" <dr(dot)klepp(at)gmx(dot)at>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2443: 1 hour error at date calculation
Date: 2006-05-19 14:18:19
Message-ID: 28895.1148048299@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"nikolaus klepp" <dr(dot)klepp(at)gmx(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message friedrich_sperling 2006-05-19 22:00:27 BUG #2445: incompatibility: realloc <> repalloc
Previous Message Alvaro Herrera 2006-05-19 13:23:06 Re: sequences problem