Re: Using infinite values with DateTimeTZRange

On 08/23/2017 06:14 PM, Fennell, Felix W. wrote:
>> While it is not an error to use 'infinity' or '-infinity' in a
>> tstzrange, since those are acceptable timestamptz values, that is not
>> the normal way to represent a range which extends infinitely in one
>> direction. The normal way to represent a range with an infinite upper or
>> lower bound is to omit the bound, as in '["2010-01-01 10:00:00 +01",]'.
>
> Thanks for that - in my case I want to distinguish between cases where the end-date for a range is not known, and where it just been considered yet. The latter case I'm representing as a date-range without an upper bound, so thought 'infinity' was the best choice for where the end-date had been considered, but is unknown.
>
> Hope that reasoning makes sense, and if there's a better way to represent those two states then I'd be open to changing that.

Yes, it does make sense and I've dealt with exactly the same problem. In
some cases, I've decided to use separate timestamptz columns in a table
to represent the upper and lower bounds. I was still able to take
advantage of the feature of the range type system I've found most
useful: an EXCLUDE constraint using the tstzrange constructor in the
constraint.

I found this approach to be more flexible in one crucial way. In one
table, I needed to store date ranges in which a range could start and
end on the same day. That was impossible using a daterange, since range
types with the same upper and lower bounds are empty and lose all other
information.

>
> -- Felix

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers(at)emphasys-software(dot)com