Re: 6.5.0 datetime bug?

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