Re: Range types - Mailing list pgsql-hackers

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
NOTcontains($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
BYt_in.start_time    ) sub;
 
$$ LANGUAGE 'sql' IMMUTABLE STRICT;