Re: Ranges for well-ordered types

On Jun 11, 2006, at 5:15 , Bruno Wolff III wrote:

> I think you might want to reconsider your design. It works well for
> dates
> because sets of dates are made of of isolated points and such sets are
> both open and closed. If you are using time, I think it will be
> more convenient
> to use a closed, open representation.

Under design I proposed, closed-closed and closed-open are just two
different representations of the same range: to the commonly used
notation, the closed-open range [p1, p2) is equivalent to the closed-
closed range [p1, next(p2)], where next() is the successor function.
I agree than depending on the context, it may be better to use one
representation than the other (a budget meeting that lasts from 10:00
until 11:00 meets but doesn't share any points with an early lunch
meeting that starts at 11:00). Perhaps there should be probably some
to_char functions to format the range in the desired form.

Time (and timestamp) is a bit of a issue conceptually. The "default"
successor function would depend on the precision of the timestamp.
timestamp(0) would have a successor function of + 1 second, while
timestamp(3) would have a successor function of + .001 second. In the
above example, Monday's budget meeting in Tokyo from 10:00 until
11:00 could be represented with ranges of timestamp(0) with time zone as
[2006-06-12 10:00:00+09, 2006-06-12 11:00:00+09)
or as
[2006-06-12 10:00:00+09, 2006-06-12 10:59:59+09]

With timestamp(3) with time zone, that'd be
[2006-06-12 10:00:00.000+09, 2006-06-12 11:00:00.000+09)
or as
[2006-06-12 10:00:00.000+09, 2006-06-12 10:59:59.999+09]

Most people would be more comfortable with the first representation
of each pair, but the two representations in each pair represent the
same range.

For a lot of scheduling applications, using timestamps with a
precision greater that 0 probably wouldn't be very useful (and when
not using integer datetimes, not all that exact). Indeed, some
scheduling applications may want a precision of 1 minute, rather than
1 second, or perhaps a precision of 15 minutes, or even an hour. I
see this as a limitation of the timestamp type, and perhaps a
workaround could be found using check constraints and more
sophisticated successor functions.

For example, a first cut of a successor function for a timestamp with
precision of 1 hour might use + 3600 seconds, but the difference in
seconds between the top of any two hours may not necessarily be 3600
seconds in some corner cases when the calendar has changed. In those
cases, the successor function would need to be sure to return the
next hour, rather than the previous hour + 3600 seconds. (Perhaps the
calendar has never made a change where this would be a problem, but
for some arbitrary timestamp precision, for example 1 day, this could
be true. I haven't done enough research yet to determine how much of
a problem this is. In those cases it might be better to use dates
than timestamps.)

With time zones and daylight saving time, this becomes even more
interesting, especially for time zone offsets that aren't integral
hours (e.g., South Australia Standard Time +9:30, Iran Time +3:30,
India Time +5:30). A 1 hour precision requirement would need to
include the applicable time zone. There's been previous discussion of
including such time zone information in the timestamp value, but as
far as I know, no work has been done in that direction yet.

These are interesting questions, and improvements in timestamp can
make ranges even more convenient. I still see utility in ranges using
the current timestamp implementation as well.

Michael Glaesemann
grzm seespotcode net