Chapter 2. Time Types and Range Types

Table of Contents

Functions and Operators for Time Types and Range Types
Constructor Functions
Casting
Accessor Functions
Comparison Operators
Set Operators
Topological Operators
Relative Position Operators
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 instant 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 periods
SELECT period '[2012-01-01 08:10:00, 2012-01-01 08:00:00]'; -- invalid bounds
SELECT period '[2012-01-01 08:00:00, 2012-01-01 08:00:00)'; -- empty period
		

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 values
SELECT timestampset '{2012-01-01 08:10:00, 2012-01-01 08:00:00}'; -- unordered elements
SELECT timestampset '{2012-01-01 08:00:00, 2012-01-01 08:00:00}'; -- duplicate elements
		

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 values
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]}';
-- unordered 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]}';
-- overlapping elements
		

Values of the periodset type are converted into a normal form so that equivalent values have identical representations. For this, consecutive adjacent period values are merged when possible. An example of transformation into a 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, two additional range types are defined, 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 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, int 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
			

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

    -- It is assumed that the lower bound is inclusive and the upper bound is exclusive.
    SELECT period('2012-01-01 08:00:00', '2012-01-03 08:00:00');
    -- Period defined with four arguments
    SELECT period('2012-01-01 08:00:00', '2012-01-03 09:30:00', false, true);
    					

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 type, the tstzrange type, or the time types can be converted to one another using an explicit 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 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 timespan

    timespan({timestampset, period, periodset}): interval

    SELECT timespan(period '[2012-01-01, 2012-01-03)');
    -- "2 days"
    SELECT timespan(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}');
    -- "3 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}): int

    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}, int): 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): int

    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, int): 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)"}"
    					
  • 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]}"
    					

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.

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

  • Are the time values equal?

    time = time

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

    time <> time

    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

    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

    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

    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

    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

    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

    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

    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 points in common)?

    {timestampset, period, periodset} && {timestampset, period, periodset}

    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

    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}

    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

    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 a range as left or right argument. We extended these operators for number 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}

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

    {number, range} >> {number, range}

    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}

    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}

    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}

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

    time <<# time

    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

    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

    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

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