Function extent returns a bounding span that encloses a set of set or span values.

Union is a very useful operation for set and span types. As we have seen in the section called “Set Operations”, we can compute the union of two set or span values using the + operator. However, it is also very useful to have an aggregate version of the union operator for combining an arbitrary number of values. Functions setUnion and spanUnion can be used for this purpose.

Function tCount generalizes the traditional aggregate function count. The temporal count can be used to compute at each point in time the number of available objects (for example, number of spans). Function tCount returns a temporal integer (see Chapter 4, Temporal Types). The function has two optional parameters that specify the granularity (an interval) and the origin of time (a timestamptz). When these parameters are given, the temporal count is computed at time buckets of the given granularity (see the section called “Multidimensional Tiling”).

Bounding span

extent({set,spans}) → span

WITH spans(r) AS (
  SELECT floatspan '[1, 4)' UNION
  SELECT floatspan '(5, 8)' UNION
  SELECT floatspan '(7, 9)'
)
SELECT extent(r) FROM spans;
-- [1,9)
WITH times(ts) AS (
  SELECT tstzset '{2001-01-01, 2001-01-03, 2001-01-05}' UNION
  SELECT tstzset '{2001-01-02, 2001-01-04, 2001-01-06}' UNION
  SELECT tstzset '{2001-01-01, 2001-01-02}'
)
SELECT extent(ts) FROM times;
-- [2001-01-01, 2001-01-06]
WITH periods(ps) AS (
  SELECT tstzspanset '{[2001-01-01, 2001-01-02], [2001-01-03, 2001-01-04]}' UNION
  SELECT tstzspanset '{[2001-01-01, 2001-01-04], [2001-01-05, 2001-01-06]}' UNION
  SELECT tstzspanset '{[2001-01-02, 2001-01-06]}'
)
SELECT extent(ps) FROM periods;
-- [2001-01-01, 2001-01-06]

Aggregate union

setUnion({value,set}) → set

spanUnion(spans) → spanset

WITH times(ts) AS (
  SELECT tstzset '{2001-01-01, 2001-01-03, 2001-01-05}' UNION
  SELECT tstzset '{2001-01-02, 2001-01-04, 2001-01-06}' UNION
  SELECT tstzset '{2001-01-01, 2001-01-02}'
)
SELECT setUnion(ts) FROM times;
-- {2001-01-01, 2001-01-02, 2001-01-03, 2001-01-04, 2001-01-05, 2001-01-06}
WITH periods(ps) AS (
  SELECT tstzspanset '{[2001-01-01, 2001-01-02], [2001-01-03, 2001-01-04]}' UNION
  SELECT tstzspanset '{[2001-01-02, 2001-01-03], [2001-01-05, 2001-01-06]}' UNION
  SELECT tstzspanset '{[2001-01-07, 2001-01-08]}'
)
SELECT spanUnion(ps) FROM periods;
-- {[2001-01-01, 2001-01-04], [2001-01-05, 2001-01-06], [2001-01-07, 2001-01-08]}

Temporal count

tCount(times) → {tintSeq,tintSeqSet}

WITH times(ts) AS (
  SELECT tstzset '{2001-01-01, 2001-01-03, 2001-01-05}' UNION
  SELECT tstzset '{2001-01-02, 2001-01-04, 2001-01-06}' UNION
  SELECT tstzset '{2001-01-01, 2001-01-02}'
)
SELECT tCount(ts) FROM times;
-- {2@2001-01-01, 2@2001-01-02, 1@2001-01-03, 1@2001-01-04, 1@2001-01-05, 1@2001-01-06}
WITH periods(ps) AS (
  SELECT tstzspanset '{[2001-01-01, 2001-01-02), [2001-01-03, 2001-01-04)}' UNION
  SELECT tstzspanset '{[2001-01-01, 2001-01-04), [2001-01-05, 2001-01-06)}' UNION
  SELECT tstzspanset '{[2001-01-02, 2001-01-06)}'
)
SELECT tCount(ps) FROM periods;
-- {[2@2001-01-01, 3@2001-01-03, 1@2001-01-04, 2@2001-01-05, 2@2001-01-06)}