Re: PostgreSQL 7.0.2 Date Miscalculation

> > The RELTIME function is miscalculating dates.
> > (all my graphs were wrong today!)

Just an aside: INTERVAL is the preferred type for, uh, intervals.
RELTIME is used internally for historical reasons. In particular,
INTERVAL maintains the distinction between qualitative units such as
months and years, while RELTIME assumes a 30 day month and 365 day year
*always*.

But for your example that does not make a difference...

> >          stamp
> > ------------------------
> >  2001-03-25 02:53:52-05
> > When the date is:
> > Mon Apr  2 19:45:40 EDT 2001
> > And the result SHOULD be:
> > ------------------------
> >  2001-03-26 02:53:52-05

Should be fixed in current sources (and the upcoming 7.1 release).

> > I also have NO idea what this means:
> > SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
> > ------------
> >  0345-05-14

Whoops. Still a problem even in current sources, probably related to
changes to help with time zone manipulation. There is an internal units
mismatch between DATE and RELTIME. Use INTERVAL instead.

> If I changed the system clock back before the DST change... it works fine.
> --- ./results/horology.out      Mon Apr  2 17:06:59 2001
>   SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
>    03:31:00-08
>   -------------
> !  03:31:00-07

Hmm. This is just a badly designed regression test (I can say that,
since it is probably mine ;)

I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
zone for today. That really won't work in a testable way, since the
result varies during the year :(

This illustrates a fundamental problem with the SQL9x TIME WITH TIME
ZONE type, which carries no date info for context. And they have no
"date with time zone", which except for a few hours a year might be more
helpful. imho TIMESTAMP is to be preferred in most cases.

                      - Thomas