Thread: Re: 6.5.0 datetime bug?
On Thu, Mar 30, 2000 at 01:48:27PM -0600, Ross J. Reedstrom wrote:
> > testtime=> select date_part('day', '3-26-2000'::timestamp-'3-6-2000'::timestamp) as days;
> > 20
> > testtime=> select date_part('day', '3-27-2000'::timestamp-'3-6-2000'::timestamp) as days;
> > 20
> Hmm, I happen to have a 6.5.0 system sitting here: It works there, so I suspect
> something with your local operating system config. Are you running LOCALE enabled?
> Since the same version works on my system, others reports of higher versions working
> for them probably don't mean much.
> Ross
now, this is weird.
no idea if I have LOCALE enabled, I don't use it that's for sure.
anyone?
tinus.
(I'll try upgrading anyhow)
> > Hmm, I happen to have a 6.5.0 system sitting here: It works there, so I suspect
> > something with your local operating system config.
> now, this is weird.
I should have asked originally: what time zone are you running in?
From your mailing address I'll bet that you are on the other side of
GMT from where I run my tests:
postgres=# set time zone 'Europe/Amsterdam';
SET VARIABLE
postgres=# select date_part('day',
'3-27-2000'::timestamp-'3-6-2000'::timestamp) as days;days
------ 20
(1 row)
OK, I see the problem in current sources :(
Thanks for pursuing this; I'll take a look at it.
btw, if we were to add some "other side of GMT" time zone testing to
our regression test, what time zone would be the most likely to be
universally supported? We know that PST8PDT works pretty well, but I'm
not sure of the best candidate for the other side...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > Hmm, I happen to have a 6.5.0 system sitting here: It works there, so I suspect
> > something with your local operating system config.
> anyone?
It turns out to be a problem in the local country config :)
Why does the Netherlands (or at least my RH5.2 timezone database)
think you switch to DST on March 26? The date_part() function was just
masking the problem:
postgres=# select '3-27-2000'::timestamp-'3-6-2000'::timestamp;?column?
----------20 23:00
(1 row)
postgres=# select '3-26-2000'::timestamp-'3-6-2000'::timestamp;?column?
----------20 00:00
(1 row)
When you do the date arithmetic, you are automatically calculating an
*absolute* time difference which can be affected by DST boundaries.
For some reason, we don't have a date_part() available for the date
data type, which would have been my suggested workaround. We'd flame
the implementer, but that's me so I'll be nice :(
It is probably too late to get this added for v7.0, though I might be
able to add the code to the backend so it could be a (very) small
CREATE FUNCTION operation to get it usable for 7.0. Will look at it.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart wrote: > > Why does the Netherlands (or at least my RH5.2 timezone database) > think you switch to DST on March 26? The date_part() function was just Hmmmm, maybe because we actually switched on march 26? In fact, whole of europe did AFAIK.... Maarten -- Maarten Boekhold, maarten.boekhold@tibcofinance.com TIBCO Finance Technology Inc. "Sevilla" Building Entrada 308 1096 ED Amsterdam, The Netherlands tel: +31 20 6601000 (direct: +31 20 6601066) fax: +31 20 6601005 http://www.tibcofinance.com
> > Why does the Netherlands (or at least my RH5.2 timezone database)
> > think you switch to DST on March 26?
> Hmmmm, maybe because we actually switched on march 26? In fact, whole of
> europe did AFAIK....
How quaint ;) The US switches this next weekend, which pushes it into
April. So it didn't occur to me that it was a DST issue at first.
And, I got off on the wrong track suggesting a solution. Having a
date_part() which works on dates explicitly doesn't really address the
issue, since you are trying to do the date_part() on a time interval,
not on an absolute date. And the time interval probably *should* keep
track of hours etc.
However, we *do* have an explicit subtraction operator for dates,
which returns a difference in days, which may be what you want:
postgres=# select '3-27-2000'::date-'3-6-2000'::date as days;days
------ 21
(1 row)
Or, force the type of the timestamp field to be date:
postgres=# select
date('3-27-2000'::timestamp)-date('3-6-2000'::timestamp) as days;days
------ 21
(1 row)
And, if you still want to do the arithmetic using timestamps, you can
force the evaluation of the input into the *same* timezone, as in this
example:
postgres=# select date_part('day','3-27-2000 CET'::timestamp-'3-6-2000 CET'::timestamp) as days;days
------ 21
(1 row)
I'm no longer thinking that an explicit date_part() for date or time
types will be useful.
HTH
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California