Re: A date bug: number of day of October

From: Guillaume Perréal <perreal(at)lyon(dot)cemagref(dot)fr>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: A date bug: number of day of October
Date: 2000-04-17 06:47:03
Message-ID: 38FAB367.21B02708@lyon.cemagref.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tom Lane wrote:
>
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > In the CET zone (where you probably live) daylight savings time ends in
> > October, so it is true that
>
> > 1999-10-01 00:00:00 + (24h * 31 days) = 1999-10-31 23:00:00
>
> > Of course a month is not defined as "24h * 30/31 days" but instead as the
> > time between xxxx-yy-zz and xxxx-(yy+1)-zz, so the above could be condered
> > wrong. It is especially wrong that the same thing happens if you use DATE
> > instead of TIMESTAMP (or DATETIME, now deprecated).
>
> Actually, INTERVAL does know the difference between '1 month' and '31 days',
> as illustrated by the fact that it gets these two cases right:
>
> regression=# select '1999-6-1'::timestamp + '1 month'::interval;
> ?column?
> ------------------------
> 1999-07-01 00:00:00-04
> (1 row)
>
> regression=# select '1999-6-1'::timestamp + '2 month'::interval;
> ?column?
> ------------------------
> 1999-08-01 00:00:00-04
> (1 row)
>
> (remember June and July have different numbers of days). The problem
> here is a plain old code bug: after transforming the input value to
> y/m/d/h/m/s/timezone, and correctly adding the N-month offset to
> this symbolic form, timestamp_pl_span transforms back to a GMT-based
> timestamp using *the same timezone offset*.
>
> Thus, for example,
>
> regression=# select '1999-10-1'::timestamp + '1 month'::interval;
> ?column?
> ------------------------
> 1999-10-31 23:00:00-05
> (1 row)
>
> for me (I live in EST5EDT, where Oct 1 is in daylight savings time
> GMT-4, but Nov 1 is standard time GMT-5).
>
> Correct behavior, IMHO, is to change to the local timezone appropriate
> for the converted date before transforming y/m/d/etc to timestamp.
> I have no idea how hard that is to do. One kluge that comes to mind is
> to convert the modified y/m/d/etc date to timestamp, convert that back
> to local time to get a timezone, and then convert the same y/m/d/h/m/s
> plus new timezone to timestamp. But perhaps there's a cleaner/faster
> way to do it. I'm not real sure that said algorithm would give
> plausible behavior if the result time falls within a DST transition
> anyway. (But what is plausible behavior in that case?)
>
> Another issue: for intervals smaller than a month, INTERVAL currently
> represents the value as X number of seconds. Thus, since our last
> DST->EST transition was early morning 1999/10/31,
>
> regression=# select '1999-10-31'::timestamp + '1 day'::interval;
> ?column?
> ------------------------
> 1999-10-31 23:00:00-05
> (1 row)
>
> which is fairly unintuitive --- though if I'd asked for +'24 hours'
> I would accept it as correct. This is not a code bug but designed
> behavior. ISTM that really, INTERVAL ought to have a three-part
> representation: months (which can serve for larger units as well),
> days, and sub-day units (which can all be converted to seconds).
> But representing days as seconds breaks at DST boundaries.
>
> regards, tom lane

Thanks you all,

As I just use this code to get the number of seconds in a month (and don't care
anyway of the timezone), I think I could use this:

select date_part('epoch', date_trunc('month', '1999-10-01'::timestamp + '1 month
1 hour'::interval))-date_part('epoch', '1999-10-01'::timestamp);

Regards,

Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Denis N. Stepanov 2000-04-18 12:30:24 7.0RC1 on Linux Alpha
Previous Message Tom Lane 2000-04-15 02:37:49 Re: A date bug: number of day of October