Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group