Chapter 2. Time Types and Range Types

Table of Contents

Functions and Operators for Time Types and Range Types
Constructor Functions
Casting
Accessor Functions
Modification Functions
Comparison Operators
Set Operators
Topological Operators
Relative Position Operators
Aggregate Functions
Indexing of Time Types

Temporal types are based on four time types: the timestamptz type provided by PostgreSQL and three new types which are period, timestampset, and periodset.

The period type is a specialized version of the tstzrange (short for timestamp with time zone range) type provided by PostgreSQL. Type period has similar functionality as type tstzrange but has a more efficient implementation, in particular it is of fixed length while the tstzrange type is of variable length. Furthermore, empty periods and infinite bounds are not allowed in period values, while they are allowed in tstzrange values.

A value of the period type has two bounds, the lower bound and the upper bound, which are timestamptz values. The bounds can be inclusive or exclusive. An inclusive bound means that the boundary instant is included in the period, while an exclusive bound means that the boundary instant is not included in the period. In the text form of a period value, inclusive and exclusive lower bounds are represented, respectively, by “[” and “(”. Likewise, inclusive and exclusive upper bounds are represented, respectively, by “]” and “)”. In a period value, the lower bound must be less than or equal to the upper bound. A period value with equal and inclusive bounds is called an instantaneous period and corresponds to a timestamptz value. Examples of period values are as follows:

SELECT period '[2012-01-01 08:00:00, 2012-01-03 09:30:00)';
-- Instant period
SELECT period '[2012-01-01 08:00:00, 2012-01-01 08:00:00]';
-- Erroneous period: invalid bounds
SELECT period '[2012-01-01 08:10:00, 2012-01-01 08:00:00]';
-- Erroneous period: empty period
SELECT period '[2012-01-01 08:00:00, 2012-01-01 08:00:00)';

The timestampset type represents a set of different timestamptz values. A timestampset value must contain at least one element, in which case it corresponds to a timestamptz value. The elements composing a timestampset value must be ordered. Examples of timestampset values are as follows:

SELECT timestampset '{2012-01-01 08:00:00, 2012-01-03 09:30:00}';
-- Singleton timestampset
SELECT timestampset '{2012-01-01 08:00:00}';
-- Erroneous timestampset: unordered elements
SELECT timestampset '{2012-01-01 08:10:00, 2012-01-01 08:00:00}';
-- Erroneous timestampset: duplicate elements
SELECT timestampset '{2012-01-01 08:00:00, 2012-01-01 08:00:00}';

Finally, the periodset type represents a set of disjoint period values. A periodset value must contain at least one element, in which case it corresponds to a period value. The elements composing a periodset value must be ordered. Examples of periodset values are as follows:

SELECT periodset '{[2012-01-01 08:00:00, 2012-01-01 08:10:00],
  [2012-01-01 08:20:00, 2012-01-01 08:40:00]}';
-- Singleton periodset
SELECT periodset '{[2012-01-01 08:00:00, 2012-01-01 08:10:00]}';
-- Erroneous periodset: unordered elements
SELECT periodset '{[2012-01-01 08:20:00, 2012-01-01 08:40:00],
  [2012-01-01 08:00:00, 2012-01-01 08:10:00]}';
-- Erroneous periodset: overlapping elements
SELECT periodset '{[2012-01-01 08:00:00, 2012-01-01 08:10:00],
  [2012-01-01 08:05:00, 2012-01-01 08:15:00]}';

Values of the periodset type are converted into normal form so that equivalent values have identical representations. For this, consecutive adjacent period values are merged when possible. An example of transformation into normal form is as follows:

SELECT periodset '{[2012-01-01 08:00:00, 2012-01-01 08:10:00),
  [2012-01-01 08:10:00, 2012-01-01 08:10:00], (2012-01-01 08:10:00, 2012-01-01 08:20:00]}';
-- "{[2012-01-01 08:00:00+00,2012-01-01 08:20:00+00]}"

Besides the built-in range types provided by PostgreSQL, MobilityDB defines two additional range types: intrange (another name for int4range) and floatrange.

Functions and Operators for Time Types and Range Types

We present next the functions and operators for time and range types. These functions and operators are polymorphic, that is, their arguments may be of several types, and the result type may depend on the type of the arguments. To express this in the signature of the operators, we use the following notation:

  • A set of types such as {period,timestampset,periodset} represents any of the types listed,

  • time represents any time type, that is, timestamptz, period, timestampset, or periodset,

  • number represents any number type, that is, integer or float,

  • range represents any number range type, that is, intrange or floatrange.

  • type[] represents an array of type.

As an example, the signature of the contains operator (@>) is as follows:

{timestampset, period, periodset} @> time: boolean

In the following, for conciseness, the time part of the timestamps is omitted in the examples. Recall that in that case PostgreSQL assumes the time 00:00:00.

Constructor Functions

The period type has a constructor function that accepts two or four arguments. The two-argument form constructs a period in normal form, that is, with inclusive lower bound and exclusive upper bound. The four-argument form constructs a period with bounds specified by the third and fourth arguments, which are Boolean values stating, respectively, whether the left and right bounds are inclusive or not.

  • Constructor for period

    period(timestamptz,timestamptz,left_inc=true,right_inc=false): period

    -- Period defined with two arguments
    SELECT period('2012-01-01 08:00:00', '2012-01-03 08:00:00');
    -- [2012-01-01 08:00:00+01, 2012-01-03 08:00:00+01)
    -- Period defined with four arguments
    SELECT period('2012-01-01 08:00:00', '2012-01-03 09:30:00', false, true);
    -- (2012-01-01 08:00:00+01, 2012-01-03 09:30:00+01]
    

The timestampset type has a constructor function that accepts a single argument which is an array of timestamptz values.

  • Constructor for timestampset

    timestampset(timestamptz[]): timestampset

    SELECT timestampset(ARRAY[timestamptz '2012-01-01 08:00:00', '2012-01-03 09:30:00']);
    -- "{2012-01-01 08:00:00+00, 2012-01-03 09:30:00+00}"
    

The periodset type has a constructor function that accepts a single argument which is an array of period values.

  • Constructor for periodset

    periodset(period[]): periodset

    SELECT periodset(ARRAY[period '[2012-01-01 08:00:00, 2012-01-01 08:10:00]',
    -- '[2012-01-01 08:20:00, 2012-01-01 08:40:00]']);
    

Casting

Values of the timestamptz, tstzrange, or the time types can be converted to one another using the function CAST or using the :: notation.

  • Cast a timestamptz to another time type

    timestamptz::timestampset

    timestamptz::period

    timestamptz::periodset

    SELECT CAST(timestamptz '2012-01-01 08:00:00' AS timestampset);
    -- "{2012-01-01 08:00:00+01}"
    SELECT CAST(timestamptz '2012-01-01 08:00:00' AS period);
    -- "[2012-01-01 08:00:00+01, 2012-01-01 08:00:00+01]"
    SELECT CAST(timestamptz '2012-01-01 08:00:00' AS periodset);
    -- "{[2012-01-01 08:00:00+01, 2012-01-01 08:00:00+01]}"
    
  • Cast a timestampset to a periodset

    timestampset::periodset

    SELECT CAST(timestampset '{2012-01-01 08:00:00, 2012-01-01 08:15:00,
      2012-01-01 08:25:00}' AS periodset);
    -- "{[2012-01-01 08:00:00+01, 2012-01-01 08:00:00+01],
      [2012-01-01 08:15:00+01, 2012-01-01 08:15:00+01],
      [2012-01-01 08:25:00+01, 2012-01-01 08:25:00+01]}"
    
  • Cast a period to another time type

    period::periodset

    period::tstzrange

    SELECT period '[2012-01-01 08:00:00, 2012-01-01 08:30:00)'::periodset;
    -- "{[2012-01-01 08:00:00+01, 2012-01-01 08:30:00+01)}"
    SELECT period '[2012-01-01 08:00:00, 2012-01-01 08:30:00)'::tstzrange;
    -- "["2012-01-01 08:00:00+01","2012-01-01 08:30:00+01")"
    
  • Cast a tstzrange to a period

    tstzrange::period

    SELECT tstzrange '[2012-01-01 08:00:00, 2012-01-01 08:30:00)'::period;
    -- "[2012-01-01 08:00:00+01, 2012-01-01 08:30:00+01)"
    

Accessor Functions

  • Get the memory size in bytes

    memSize({timestampset,periodset}): integer

    SELECT memSize(timestampset '{2012-01-01, 2012-01-02, 2012-01-03}');
    -- 104
    SELECT memSize(periodset '{[2012-01-01, 2012-01-02], [2012-01-03, 2012-01-04],
      [2012-01-05, 2012-01-06]}');
    -- 136
    
  • Get the lower bound

    lower(period): timestamptz

    SELECT lower(period '[2011-01-01, 2011-01-05)');
    -- "2011-01-01"
    
  • Get the upper bound

    upper(period): timestamptz

    SELECT upper(period '[2011-01-01, 2011-01-05)');
    -- "2011-01-05"
    
  • Is the lower bound inclusive?

    lower_inc(period): boolean

    SELECT lower_inc(period '[2011-01-01, 2011-01-05)');
    -- true
    
  • Is the upper bound inclusive?

    upper_inc(period): boolean

    SELECT upper_inc(period '[2011-01-01, 2011-01-05)');
    -- false
    
  • Get the duration

    duration({period,periodset}): interval

    SELECT duration(period '[2012-01-01, 2012-01-03)');
    -- "2 days"
    SELECT duration(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}');
    -- "3 days"
    
  • Get the timespan ignoring the potential time gaps

    timespan({timestampset,periodset}): interval

    SELECT timespan(timestampset '{2012-01-01, 2012-01-03}');
    -- "2 days"
    SELECT timespan(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}');
    -- "4 days"
    
  • Get the period on which the timestamp set or period set is defined ignoring the potential time gaps

    period({timestampset,periodset}): period

    SELECT period(timestampset '{2012-01-01, 2012-01-03, 2012-01-05}');
    -- "[2012-01-01, 2012-01-05]"
    SELECT period(periodset '{[2012-01-01, 2012-01-02), [2012-01-03, 2012-01-04)}');
    -- "[2012-01-01, 2012-01-04)"
    
  • Get the number of different timestamps

    numTimestamps({timestampset,periodset}): integer

    SELECT numTimestamps(timestampset '{2012-01-01, 2012-01-03, 2012-01-04}');
    -- 3
    SELECT numTimestamps(periodset '{[2012-01-01, 2012-01-03), (2012-01-03, 2012-01-05)}');
    -- 3
    
  • Get the start timestamp

    startTimestamp({timestampset,periodset}): timestamptz

    The function does not take into account whether the bounds are inclusive or not.

    SELECT startTimestamp(periodset '{[2012-01-01, 2012-01-03), (2012-01-03, 2012-01-05)}');
    -- "2012-01-01"
    
  • Get the end timestamp

    endTimestamp({timestampset,periodset}): timestamptz

    The function does not take into account whether the bounds are inclusive or not.

    SELECT endTimestamp(periodset '{[2012-01-01, 2012-01-03), (2012-01-03, 2012-01-05)}');
    -- "2012-01-05"
    
  • Get the n-th different timestamp

    timestampN({timestampset,periodset},integer): timestamptz

    The function does not take into account whether the bounds are inclusive or not.

    SELECT timestampN(periodset '{[2012-01-01, 2012-01-03), (2012-01-03, 2012-01-05)}', 3);
    -- "2012-01-04"
    
  • Get the different timestamps

    timestamps({timestampset,periodset}): timestampset

    The function does not take into account whether the bounds are inclusive or not.

    SELECT timestamps(periodset '{[2012-01-01, 2012-01-03), (2012-01-03, 2012-01-05)}');
    -- "{"2012-01-01", "2012-01-03", "2012-01-05"}"
    
  • Get the number of periods

    numPeriods(periodset): integer

    SELECT numPeriods(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-04],
      [2012-01-05, 2012-01-06)}');
    -- 3
    
  • Get the start period

    startPeriod(periodset): period

    SELECT startPeriod(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-04],
      [2012-01-05, 2012-01-06)}');
    -- "[2012-01-01,2012-01-03)"
    
  • Get the end period

    endPeriod(periodset): period

    SELECT endPeriod(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-04],
      [2012-01-05, 2012-01-06)}');
    -- "[2012-01-05,2012-01-06)"
    
  • Get the n-th period

    periodN(periodset,integer): period

    SELECT periodN(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-04],
      [2012-01-05, 2012-01-06)}', 2);
    -- "[2012-01-04,2012-01-04]"
    
  • Get the periods

    periods(periodset): period[]

    SELECT periods(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-04],
      [2012-01-05, 2012-01-06)}');
    -- "{"[2012-01-01,2012-01-03)", "[2012-01-04,2012-01-04]", "[2012-01-05,2012-01-06)"}"
    

Modification Functions

  • Shift the time value by an interval

    shift({timestampset,period,periodset}): {timestampset,period,periodset}

    SELECT shift(timestampset '{2001-01-01, 2001-01-03, 2001-01-05}', '1 day'::interval);
    -- "{2001-01-02, 2001-01-04, 2001-01-06}"
    SELECT shift(period '[2001-01-01, 2001-01-03]', '1 day'::interval);
    -- "[2001-01-02, 2001-01-04]"
    SELECT shift(periodset '{[2001-01-01, 2001-01-03], [2001-01-04, 2001-01-05]}',
      '1 day'::interval);
    -- "{[2001-01-02, 2001-01-04], [2001-01-05, 2001-01-06]}"
    
  • Round the bounds of a float range to a number of decimal places

    setPrecision(floatrange,integer): floatrange

    SELECT setPrecision(floatrange '[1.123456789,2.123456789)', 3);
    -- "[1.123,2.123)"
    SELECT setPrecision(floatrange '(,2.123456789]', 3);
    -- "(,2.123]"
    SELECT setPrecision(floatrange '[1.123456789, inf)', 3);
    -- "[1.123,Infinity)"
    

Comparison Operators

The comparison operators (=, <, and so on) require that the left and right arguments be of the same type. Excepted equality and inequality, the other comparison operators are not useful in the real world but allow B-tree indexes to be constructed on time types. For period values, the operators compare first the lower bound, then the upper bound. For timestamp set and period set values, the operators compare first the bounding periods, and if those are equal, they compare the first N instants or periods, where N is the minimum of the number of composing instants or periods of both values.

The comparison operators available for the time types are given next.

  • Are the time values equal?

    time = time: boolean

    SELECT period '[2012-01-01, 2012-01-04)' = period '[2012-01-01, 2012-01-04)';
    -- true
    
  • Are the time values different?

    time <> time: boolean

    SELECT period '[2012-01-01, 2012-01-04)' <> period '[2012-01-03, 2012-01-05)';
    -- true
    
  • Is the first time value less than the second one?

    time < time: boolean

    SELECT timestampset '{2012-01-01, 2012-01-04}' < timestampset '{2012-01-01, 2012-01-05}';
    -- true
    
  • Is the first time value greater than the second one?

    time > time: boolean

    SELECT period '[2012-01-03, 2012-01-04)' > period '[2012-01-02, 2012-01-05)';
    -- true
    
  • Is the first time value less than or equal to the second one?

    time <= time: boolean

    SELECT periodset '{[2012-01-01, 2012-01-04)}' <=
      periodset '{[2012-01-01, 2012-01-05), [2012-01-06, 2012-01-07)}';
    -- true
    
  • Is the first time value greater than or equal to the second one?

    time >= time: boolean

    SELECT period '[2012-01-03, 2012-01-05)' >= period '[2012-01-03, 2012-01-04)';
    -- true
    

Set Operators

The set operators available for the time types are given next.

  • Union of the time values

    time + time: time

    SELECT timestampset '{2011-01-01, 2011-01-03, 2011-01-05}' +
      timestampset '{2011-01-03, 2011-01-06}';
    -- "{2011-01-01, 2011-01-03, 2011-01-05, 2011-01-06}"
    SELECT period '[2011-01-01, 2011-01-05)' + period '[2011-01-03, 2011-01-07)';
    -- "[2011-01-01, 2011-01-07)"
    SELECT periodset '{[2011-01-01, 2011-01-03), [2011-01-04, 2011-01-05)}' +
      period '[2011-01-03, 2011-01-04)';
    -- "{[2011-01-01, 2011-01-05)}"
    
  • Intersection of the time values

    time * time: time

    SELECT timestampset '{2011-01-01, 2011-01-03}' * timestampset '{2011-01-03, 2011-01-05}';
    -- "{2011-01-03}"
    SELECT period '[2011-01-01, 2011-01-05)' * period '[2011-01-03, 2011-01-07)';
    -- "[2011-01-03, 2011-01-05)"
    
  • Difference of the time values

    time - time: time

    SELECT period '[2011-01-01, 2011-01-05)' - period '[2011-01-03, 2011-01-07)';
    -- "[2011-01-01, 2011-01-03)"
    SELECT period '[2011-01-01, 2011-01-05]' - period '[2011-01-03, 2011-01-04]'
    -- "{[2011-01-01,2011-01-03), (2011-01-04,2011-01-05]}"
    SELECT periodset '{[2011-01-01, 2011-01-06], [2011-01-07, 2011-01-10]}' -
      periodset '{[2011-01-02, 2011-01-03], [2011-01-04, 2011-01-05],
      [2011-01-08, 2011-01-09]}';
    -- "{[2011-01-01,2011-01-02), (2011-01-03,2011-01-04), (2011-01-05,2011-01-06],
      [2011-01-07,2011-01-08), (2011-01-09,2011-01-10]}"
    

Topological Operators

The topological operators available for the time types are given next.

  • Do the time values overlap (have instants in common)?

    {timestampset,period,periodset} && {timestampset,period,periodset}: boolean

    SELECT period '[2011-01-01, 2011-01-05)' && period '[2011-01-02, 2011-01-07)';
    -- true
    
  • Does the first time value contain the second one?

    {timestampset,period,periodset} @> time: boolean

    SELECT period '[2011-01-01, 2011-05-01)' @> period '[2011-02-01, 2011-03-01)';
    -- true
    SELECT period '[2011-01-01, 2011-05-01)' @> timestamptz '2011-02-01';
    -- true
    
  • Is the first time value contained by the second one?

    time <@ {timestampset,period,periodset}: boolean

    SELECT period '[2011-02-01, 2011-03-01)' <@ period '[2011-01-01, 2011-05-01)';
    -- true
    SELECT timestamptz '2011-01-10' <@ period '[2011-01-01, 2011-05-01)';
    -- true
    
  • Is the first time value adjacent to the second one?

    time -|- time: boolean

    SELECT period '[2011-01-01, 2011-01-05)' -|- timestampset '{2011-01-05, 2011-01-07}';
    -- true
    SELECT periodset '{[2012-01-01, 2012-01-02]}' -|- period '[2012-01-02, 2012-01-03)';
    -- false
    

Relative Position Operators

In PostgreSQL, the range operators <<, &<, >>, &>, and -|- only accept ranges as left or right argument. We extended these operators for numeric ranges so that one argument may be an integer or a float.

The relative position operators available for the time types and range types are given next.

  • Is the first number or range value strictly left of the second one?

    {number,range} << {number,range}: boolean

    SELECT intrange '[15, 20)' << 20;
    -- true
    
  • Is the first number or range value strictly right of the second one?

    {number,range} >> {number,range}: boolean

    SELECT intrange '[15, 20)' >> 10;
    -- true
    
  • Is the first number or range value not to the right of the second one?

    {number,range} &< {number,range}: boolean

    SELECT intrange '[15, 20)' &< 18;
    -- false
    
  • Is the first number or range value not to the left of the second one?

    {number,range} &> {number,range}: boolean

    SELECT period '[2011-01-01, 2011-01-03)' &> period '[2011-01-01, 2011-01-05)';
    -- true
    SELECT intrange '[15, 20)' &> 30;
    -- true
    
  • Is the first number or range value adjacent to the second one?

    {number,range} -|- {number,range}: boolean

    SELECT floatrange '[15, 20)' -|- 20;
    -- true
    
  • Is the first time value scritly before the second one?

    time <<# time: boolean

    SELECT period '[2011-01-01, 2011-01-03)' <<# timestampset '{2011-01-03, 2011-01-05}';
    -- true
    
  • Is the first time value strictly after the second one?

    time #>> time: boolean

    SELECT period '[2011-01-04, 2011-01-05)' #>>
      periodset '{[2011-01-01, 2011-01-04), [2011-01-05, 2011-01-06)}';
    -- true
    
  • Is the first time value not after the second one?

    time &<# time: boolean

    SELECT timestampset '{2011-01-02, 2011-01-05}' &<# period '[2011-01-01, 2011-01-05)';
    -- false
    
  • Is the first time value not before the second one?

    time #&> time: boolean

    SELECT timestamp '2011-01-01' #&> period '[2011-01-01, 2011-01-05)';
    -- true
    

Aggregate Functions

The temporal aggregate functions generalize the traditional aggregate functions. Their semantics is that they compute the value of the function at every instant in the union of the temporal extents of the values to aggregate. In contrast, recall that all other functions manipulating time types compute the value of the function at every instant in the intersection of the temporal extents of the arguments.

The temporal aggregate functions are the following ones:

  • Function tcount generalizes the traditional function count. The temporal count can be used to compute at each point in time the number of available objects (for example, number of periods). Function tcount returns a temporal integer (see Chapter 3, Temporal Types).

  • Function extent returns a bounding period that encloses a set of time values.

Similarly, there is an aggregate function for range types:

  • Function extent returns a bounding range that encloses a set of integer or float range values.

Union is a very useful operation for time types. As we have seen in the section called “Set Operators”, we can compute the union of two time 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. Function tunion can be used for this purpose.

  • Temporal count

    tcount({timestampset,period,periodset}): {tinti,tints}

    WITH times(ts) AS (
      SELECT timestampset '{2000-01-01, 2000-01-03, 2000-01-05}' UNION
      SELECT timestampset '{2000-01-02, 2000-01-04, 2000-01-06}' UNION
      SELECT timestampset '{2000-01-01, 2000-01-02}'
    )
    SELECT tcount(ts) FROM times;
    -- "{2@2000-01-01, 2@2000-01-02, 1@2000-01-03, 1@2000-01-04, 1@2000-01-05, 1@2000-01-06}"
    
    WITH periods(ps) AS (
      SELECT periodset '{[2000-01-01, 2000-01-02], [2000-01-03, 2000-01-04]}' UNION
      SELECT periodset '{[2000-01-01, 2000-01-04], [2000-01-05, 2000-01-06]}' UNION
      SELECT periodset '{[2000-01-02, 2000-01-06]}'
    )
    SELECT tcount(ps) FROM periods;
    -- {[2@2000-01-01, 3@2000-01-02], (2@2000-01-02, 3@2000-01-03, 3@2000-01-04],
       (1@2000-01-04, 2@2000-01-05, 2@2000-01-06]}
    
  • Bounding period

    extent({timestampset,period,periodset}): period

    WITH times(ts) AS (
      SELECT timestampset '{2000-01-01, 2000-01-03, 2000-01-05}' UNION
      SELECT timestampset '{2000-01-02, 2000-01-04, 2000-01-06}' UNION
      SELECT timestampset '{2000-01-01, 2000-01-02}'
    )
    SELECT extent(ts) FROM times;
    -- "[2000-01-01, 2000-01-06]"
    
    WITH periods(ps) AS (
      SELECT periodset '{[2000-01-01, 2000-01-02], [2000-01-03, 2000-01-04]}' UNION
      SELECT periodset '{[2000-01-01, 2000-01-04], [2000-01-05, 2000-01-06]}' UNION
      SELECT periodset '{[2000-01-02, 2000-01-06]}'
    )
    SELECT extent(ps) FROM periods;
    -- "[2000-01-01, 2000-01-06]"
    
  • Bounding range

    extent(range): range

    WITH ranges(r) AS (
      SELECT floatrange '[1, 4)' UNION
      SELECT floatrange '(5, 8)' UNION
      SELECT floatrange '(7, 9)'
    )
    SELECT extent(r) FROM ranges;
    -- "[1,9)"
    
  • Temporal union

    tunion({timestampset,period,periodset}): {timestampset,periodset}

    WITH times(ts) AS (
      SELECT timestampset '{2000-01-01, 2000-01-03, 2000-01-05}' UNION
      SELECT timestampset '{2000-01-02, 2000-01-04, 2000-01-06}' UNION
      SELECT timestampset '{2000-01-01, 2000-01-02}'
    )
    SELECT tunion(ts) FROM times;
    -- "{2000-01-01, 2000-01-02, 2000-01-03, 2000-01-04, 2000-01-05, 2000-01-06}"
    WITH periods(ps) AS (
      SELECT periodset '{[2000-01-01, 2000-01-02], [2000-01-03, 2000-01-04]}' UNION
      SELECT periodset '{[2000-01-02, 2000-01-03], [2000-01-05, 2000-01-06]}' UNION
      SELECT periodset '{[2000-01-07, 2000-01-08]}'
    )
    SELECT tunion(ps) FROM periods;
    -- "{[2000-01-01, 2000-01-04], [2000-01-05, 2000-01-06], [2000-01-07, 2000-01-08]}"