Re: Ranges for well-ordered types

Thanks to everyone for the feedback that I've received so far. It's
clear that there's interest in this.

On Jun 12, 2006, at 3:22 , Josh Berkus wrote:

> I do think Jim is right, though, in that we may want to look for
> portions of
> the functionality which are achievable in the context of one
> PostgreSQL
> version, unless you're going to be working full-time on this patch.

I definitely agree with implementing it in parts. I doubt it's
possible, but perhaps a first bit might make it into 8.2 :)

> In real-world calendaring applications, I *certainly* see the need
> for a
> successor function. However, that would require being able to define
> timestamps with a variable precision, e.g. TIMESTAMP('5 minutes').
> This, by
> itself would be a significant effort, yet useful ... maybe that's
> where to
> start?

As mentioned in an earlier email, I think calendaring applications in
particular would benefit from timestamp precisions of less than 1
second, e.g., TIMESTAMP('5 minutes') or TIMESTAMP('1 hour'). However,
I think this is a thorny problem. To elaborate, I believe the
precision has to be relative to some "baseline". From 12:00, 30
minute precision would presumably allow 12:00, 12:30, 13:00, 13:30,
and so on. Precision of '1 hour' would allow 12:00, 13:00, 14:00, and
so on. But these are relative to the time zone they're in. While
12:00 in Tokyo (+9) would be a timestamp value with 1 hour precision,
that same timestamp is 4:30 in Tehran (+3:30) if I got the math
right. Is 4:30 a timestamp value with 1 hour precision? Because of
this, I think timestamp precisions of less than 1 second (timestamp
(0)) require storing the time zone as part of the timestamp value.

Pushing this even further, would we allow arbitrary precision? For
example, would 45-minute precision be allowed? In that case, I
believe we'd need to go further than storing just the time zone with
the timestamp value. The timestamp value would have to be relative to
some baseline timestamp to be able to calculate whether or not the
difference between any particular timestamp and the baseline
timestamp is integral. Perhaps this could be accomplished using
domains and some additional checking function? I'm not sure. It's
enough to make me want to forget about the idea of disallowing any
precision that is not an evenly divided into the next larger "time
part": any precision between 0 seconds and 1 minute would have to be
a number of seconds evenly divided into 60; between 1 hour and 1 day,
precision would have to be one of the values 1, 2, 3, 4, 6, 8, or 12
hours.

I've been able to discuss the issue of timestamp precision without
bringing up successor functions or ranges at all, and indeed I think
it's orthogonal to the range implementation. I think they're both
concepts that should be included in PostgreSQL, but as for myself,
I'm more interested in the range implementation than the the
timestamp precision issue.

By the way, anyone care to weigh in on what term we should use when
discussing this? Josh has used PERIOD. Should we go with that for now?

A somewhat related issue: would we want any implementation to follow
(at least part) of the not-yet-standard SQL/Temporal draft? Or would
it be more desirable to steer clear of using any terms/syntax that
was included in an attempt to prevent any possible conflict with a
future SQL spec?

> You're probably going to have to give up on B-Tree indexes for
> PERIODs, and
> look towards GiST. For one thing, I would see UNIQUE in the
> context of a
> PERIOD defined as non-overlapping. e.g.:

I think that a non-overlapping constraint goes above and beyond what
UNIQUE requires. In my opinion, UNIQUE should test for equality,
rather than non-overlapping, as that keeps the meaning of UNIQUE
consistent across all types and may actually be useful in some
instances. I do think it would be convenient to have some sort of
syntax that would provide a non-overlapping constraint rather than
having to code up a constraint trigger every time you wanted to do
this. As Martijn pointed out, when GiST can be used for a UNIQUE
constraint, we should be able to define the non-overlapping
constraint quite easily. So this could be thought of as a third
orthogonal issue for ranges, the first two being the range type
constructor and timestamp precision < 1 second. Any one of these
three could be done independently and improve PostgreSQL. In
combination they are definitely a very nice package.

On Jun 13, 2006, at 13:25 , Bruno Wolff III wrote:

> Date ranges are really closed open as well (as finite sets of
> isolated points
> are both open and closed). The only oddity would be that the date
> used to
> indicate the open end of the range might not be what the user expects.

I think it's definitely a matter of interpretation. [2006-01-01,
2006-12-31] and [2006-01-01, 2007-01-01) both include the same days.
Who's to say which is the "real" representation? For all practical
purposes (i.e., what can be represented within the database)
[2006-01-01 00:00:00+0, 2006-12-31 23:59:59] and [2006-01-01
00:00:00, 2007-01-01 00:00:00+0] represent the same timestamp(0) with
time zone ranges as well. While one might idealize time to be
continuous, as far as I know there isn't a way to represent time that
way in a computer, at the very least, not in PostgreSQL.

And for the very reason that it might not be what the user expects,
if there's a way to convert between closed-open and closed-closed as
appropriate, I think it makes it much more use friendly to do so. For
example, the closed-closed representation is equivalent to what
BETWEEN does. It would be very nice to be able to provide sometime
equivalent with ranges.

As for the successor function itself: Any "exact" datatype, such as
timestamp (at least with --enable-integer-datetimes), date, integer,
or numeric, has some built-in precision anyway and a successor
function follows quite directly from that precision. I don't see that
as problematic or even very difficult.

Thanks again for your comments, past, present and future! It's been
very helpful for me to hear from others on this.

Michael Glaesemann
grzm seespotcode net