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;

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to