Re: Ranges for well-ordered types - Mailing list pgsql-hackers

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