Re: Range types

David Fetter wrote:
> On Tue, Dec 15, 2009 at 11:31:05AM -0800, Scott Bailey wrote:
>> Jeff Davis wrote:
>>> On Tue, 2009-12-15 at 10:19 -0500, Tom Lane wrote:
>> Would it be OK if we handled float timestamp ranges as continuous
>> and int64 timestamps discrete?
>
> That sounds like a recipe for disaster. Whatever timestamp ranges
> are, float and int64 should be treated the same way so as not to get
> "surprises" due to implementation details.
>
>> You effectively lose the ability to build non-contiguous sets with
>> continuous ranges. Which is integral to the work I'm doing (union,
>> intersect, coalesce and minus sets of ranges)
>>
>> As for the extra bits, would it be better to just require continuous
>> ranges to be either [] or [)? But I don't know which would be
>> preferred. My inclination would be toward [), but Tom seemed to
>> indicate that perhaps [] was the norm.
>
> [] makes certain operations--namely the important ones in
> calendaring--impossible, or at least incredibly kludgy, to do. I
> think we ought to leave openness at each end up to the user,
> independent of the underlying implementation details.
>
> FWIW, I think it would be a good idea to treat timestamps as
> continuous in all cases.

Ok, let me give an example of what we can do with the current
implementations that would not be possible with timestamps if we
implement as suggested. Jeff's implementation uses a 1 microsecond step
size or granule. And my implementation uses an interval step size and
can be configured database wide, but default is 1 second.

The function below takes two period arrays that can have overlapping and
adjacent elements. It subtracts all values in pa1 that intersect with
values in pa2. So perhaps pa1 is all of your work shifts for the month
and pa2 is a combination of your leave and holidays. The result is a
coalesced non-contiguous set of the times you would actually be working.
But to do this kind of thing you need to be able to determine prior,
first, last and next. I need an implementation that can do this for
timestamps and not just ints and dates.

CREATE OR REPLACE FUNCTION period_minus(
pa1 IN period[],
pa2 IN period[]
) RETURNS period[] AS
$$
SELECT array_agg(prd)
FROM (
SELECT period((t_in).start_time,
MIN((t_out).end_time)) AS prd
FROM (
SELECT DISTINCT first(p) AS start_time
FROM unnest($1) p
WHERE NOT contains($2, first(p))
AND NOT contains($1, prior(p))

UNION

SELECT DISTINCT next(p)
FROM unnest($2) p
WHERE contains($1, next(p))
AND NOT contains($2, next(p))
) t_in
JOIN (
SELECT next(p) AS end_time
FROM unnest($1) p
WHERE NOT contains($1, next(p))

UNION ALL

SELECT first(p)
FROM unnest($2) p
WHERE contains($1, first(p))
AND NOT contains($2, prior(p))
) t_out ON t_in.start_time < t_out.end_time
GROUP BY t_in.start_time
ORDER BY t_in.start_time
) sub;
$$ LANGUAGE 'sql' IMMUTABLE STRICT;