Re: Timestamp/Interval proposals: Part 2 - Mailing list pgsql-hackers

Thomas,

> Please define "a full set of operators". Or do the subsequent
> proposals
> defining new behaviors and some operations constitute that list?

+ - / * < > = and, if appropriate, %
Where support is lacking is * and /

Don't get me wrong.  PostgreSQL has the best implementation of
date/time/interval handling in any database I use.  It's just that
there are a few limitations and wierdnesses left, and I'd really like
to see them ironed out so that we can call our impelmentation "near
perfect".  Also, so I can stop coding workarounds into my database
apps.

> You are overstating the problem imho, but there is a problem for some
> users. SQL9x avoids the issue by defining *only* constant offsets for
> time zones. That doesn't work in the real world :/
> 
> We would expand the storage size by at least 4 bytes to accomodate
> the
> "qualitative day" information. Currently takes 12 bytes, and will
> take
> 16 or more. We will need to check for overflows during date/time
> math,
> we will need some heuristics for conversions between hours and days
> during calculations, and some users will need to cope with the
> changed
> behavior. Operations like math and comparisons will be more expensive
> (though may not be a hugely noticable effect).

I can see why you've put off doing it.   At a basic level, though,
current behaviour is counter-intuitive, so we'll need to do it someday.

> Oh. Didn't know it wasn't already there.

Not in 7.2.1.  And if you don't know about it, probably not in 7.3
either.

> > Alternative #1: Treat Interval Increments as Atomic, and Round
> 
> Yuck (imho of course ;)

Hey, I did ask for an opinion.  <grin>

> > Alternative #2: Tie Intervals to a Specific Timestamp
> 
> Double yuck. You already have this capability by your choice of
> schema;
> intervals are intervals and timestamps are timestamps. The behaviors
> you
> discuss above (both current and possible) handle this.

Hmmm?   How much is '1 month' / '4 days' then?

The current implementation does not support the / and * operators; that
is, they are supported for some type combos, but not for others, and
the results are inconsistent and sometimes confusing.

> Bad example, and I'm not following your argument here. PostgreSQL
> supports *many* time zones (Peter E. has said "too many") and any
> change
> for the Arizona example will be at odds with how dates and times are
> expected to be handled in, uh, Arizona. They use Mountain Standard
> Time
> (MST), except for years when they didn't, and are covered by
> specifying
> "MST" on input and "SET TIME ZONE 'America/Phoenix'" (and perhaps
> others
> too; it seems that "MST6" gives me consistant behavior on my Linux
> box).

Actually, the real problems I have encountered with time zones would be
solved mostly by adding the 'WEEKS TO DAYS' subtype above.  Currently
I'm forced to use TIMESTAMP WITHOUT TIMEZONE in order to avoid the
wierd one-hour shifts in my calendaring app.

> I can't imagine that you are not finding a workable solution with the
> current capabilities. That said, we are considering adopting the
> historic zinc package to support time zones within PostgreSQL (sounds
> like you might be doing some of the development ;). And for time zone
> lookup (not supported in the zinc API) it *would* be nice to move to
> a
> DBMS table-based implementation, rather than the hardcoded tables we
> have now. They may have been good enough for the last 12 years, but
> certainly lookup stuff seems like it should be in a database table,
> eh?

Yeah.  I'd love to have somebody explain this to me.  I noticed when
zinc was mentioned, but I don't know *what* it is.  Care to send me a
link?


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco