Table of Contents
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.
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.
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.
The periodset type has a constructor function that accepts a single argument which is an array of period values.
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)"
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)"}"
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)"
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
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]}"
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
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
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).
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 Section 2.1.6, 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]}"