Re: date bug (again)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Darcy Buskermolen <darcy(at)ok-connect(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: date bug (again)
Date: 2000-08-19 02:59:16
Message-ID: 21317.966653956@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Darcy Buskermolen <darcy(at)ok-connect(dot)com> writes:
> SELECT date_part('day', (('2000-10-1 0:00'::datetime + '1 month') + '-1
> day'))::int4 AS days_in_month;

> days_in_month
> --------------
> 30
> (1 row)

This is not a bug, or at least it's not entirely clear what the behavior
ought to be. The issue is what happens at a daylight-savings
transition. The results I get (US Eastern timezone) are

regression=# select '2000-10-1 0:00'::timestamp;
?column?
------------------------
2000-10-01 00:00:00-04
(1 row)

regression=# select '2000-10-1 0:00'::timestamp + '1 month';
?column?
------------------------
2000-10-31 23:00:00-05
(1 row)

See what's happening? You get a result that's exactly 31 days times
24 hours later, but that date_trunc()'s down to only 30 days. A
finer-grain example is

regression=# select '2000-10-29 0:00'::timestamp ;
?column?
------------------------
2000-10-29 00:00:00-04
(1 row)

regression=# select '2000-10-29 0:00'::timestamp + '1 day';
?column?
------------------------
2000-10-29 23:00:00-05
(1 row)

The real question is whether "+ 1 day" ought to mean "+ 24 hours"
or not, and if not what it *should* mean...

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2000-08-19 03:08:07 Re: Segmentation fault.
Previous Message Stephan Szabo 2000-08-18 18:23:39 Re: INSERT/SELECT with ORDER BY