Chapter 3. Temporal Types

There are six built-in temporal types, namely tbool, tint, tfloat, ttext, tgeompoint, and tgeogpoint, which are, respectively, based on the base types bool, int, float, text, geometry, and geography (the last two types restricted to 2D or 3D points with Z dimension).

The interpolation of a temporal value states how the value evolves between successive instants. The interpolation is stepwise when the value remains constant between two successive instants. For example, the number of employees of a department may be represented with a temporal integer, which indicates that its value is constant between two time instants. On the other hand, the interpolation is linear when the value evolves linearly between two successive instants. For example, the temperature of a room may be represented with a temporal float, which indicates that the values are known at the two time instants but continuously evolve between them. Similarly, the location of a vehicule may be represented by a temporal point where the location between two consecutive GPS readings is obtained by linear interpolation. Temporal types based on discrete base types, that is the tbool, tint, or ttext evolve necesssarily in a stepwise manner. On the other hand, temporal types based on continuous base types, that is tfloat, tgeompoint, or tgeogpoint may evolve in a stepwise or linear manner.

The subtype of a temporal value states the temporal extent at which the evolution of values is recorded. Temporal values come in four subtypes, namely, instant, instant set, sequence, and sequence set.

A temporal value of instant subtype (briefly, an instant value) represents the value at a time instant, for example

SELECT tfloat '17@2018-01-01 08:00:00';

A temporal value of instant set subtype (briefly, an instant set value) represents the evolution of the value at a set of time instants, where the values between these instants are unknown. An example is as follows:

SELECT tfloat '{17@2018-01-01 08:00:00, 17.5@2018-01-01 08:05:00, 18@2018-01-01 08:10:00}';

A temporal value of sequence subtype (briefly, a sequence value) represents the evolution of the value during a sequence of time instants, where the values between these instants are interpolated using either a stepwise or a linear function (see below). An example is as follows:

SELECT tint '(10@2018-01-01 08:00:00, 20@2018-01-01 08:05:00, 15@2018-01-01 08:10:00]';

As can be seen, a sequence value has a lower and an upper bound that can be inclusive (represented by ‘[’ and ‘]’) or exclusive (represented by ‘(' and ‘)'). A sequence value with a single instant such as

SELECT tint '[10@2018-01-01 08:00:00]';

is called an instantaneous sequence. In that case, both bounds must be inclusive.

The value of a temporal sequence is interpreted by assuming that the period of time defined by every pair of consecutive values v1@t1 and v2@t2 is lower inclusive and upper exclusive, unless they are the first or the last instants of the sequence and in that case the bounds of the whole sequence apply. Furthermore, the value taken by the temporal sequence between two consecutive instants depends on whether the interpolation is stepwise or linear. For example, the temporal sequence above represents that the value is 10 during (2018-01-01 08:00:00, 2018-01-01 08:05:00), 20 during [2018-01-01 08:05:00, 2018-01-01 08:10:00), and 15 at the end instant 2018-01-01 08:10:00. On the other hand, the following temporal sequence

SELECT tfloat '(10@2018-01-01 08:00:00, 20@2018-01-01 08:05:00, 15@2018-01-01 08:10:00]';

represents that the value evolves linearly from 10 to 20 during (2018-01-01 08:00:00, 2018-01-01 08:05:00) and evolves from 20 to 15 during [2018-01-01 08:05:00, 2018-01-01 08:10:00].

Finally, a temporal value of sequence set subtype (briefly, a sequence set value) represents the evolution of the value at a set of sequences, where the values between these sequences are unknown. An example is as follows:

SELECT tfloat '{[17@2018-01-01 08:00:00, 17.5@2018-01-01 08:05:00],
  [18@2018-01-01 08:10:00, 18@2018-01-01 08:15:00]}';

Temporal values with instant or sequence subtype are called temporal unit values, while temporal values with instant set or sequence set subtype are called temporal set values. Temporal set values can be thought of as an array of the corresponding unit values. Temporal set values must be uniform, that is, they must be constructed from unit values of the same base type and the same subtype.

Temporal sequence values are converted into normal form so that equivalent values have identical representations. For this, consecutive instant values are merged when possible. For stepwise interpolation, three consecutive instant values can be merged into two if they have the same value. For linear interpolation, three consecutive instant values can be merged into two if the linear functions defining the evolution of values are the same. Examples of transformation into normal form are as follows.

SELECT tint '[1@2001-01-01, 2@2001-01-03, 2@2001-01-04, 2@2001-01-05)';
-- "[1@2001-01-01 00:00:00+00, 2@2001-01-03 00:00:00+00, 2@2001-01-05 00:00:00+00)"
SELECT tgeompoint '[Point(1 1)@2001-01-01 08:00:00, Point(1 1)@2001-01-01 08:05:00,
  Point(1 1)@2001-01-01 08:10:00)';
-- "[Point(1 1)@2001-01-01 08:00:00, Point(1 1)@2001-01-01 08:10:00)"
SELECT tfloats(ARRAY[tfloat '[1@2001-01-01, 2@2001-01-03, 3@2001-01-05]']);
-- "{[1@2001-01-01 00:00:00+00, 3@2001-01-05 00:00:00+00]}"
SELECT tgeompoint '[Point(1 1)@2001-01-01 08:00:00, Point(2 2)@2001-01-01 08:05:00,
  Point(3 3)@2001-01-01 08:10:00]';
-- "[Point(1 1)@2001-01-01 08:00:00, Point(3 3)@2001-01-01 08:10:00]"

Similary, temporal sequence set values are converted into normal form. For this, consecutive sequence values are merged when possible. Examples of transformation into a normal form are as follows.

SELECT tints(ARRAY[tint '[1@2001-01-01, 1@2001-01-03)', '[2@2001-01-03, 2@2001-01-05)']);
-- '{[1@2001-01-01 00:00:00+00, 2@2001-01-03 00:00:00+00, 2@2001-01-05 00:00:00+00)}'
SELECT tfloats(ARRAY[tfloat '[1@2001-01-01, 2@2001-01-03)',
  '[2@2001-01-03, 3@2001-01-05]']);
-- '{[1@2001-01-01 00:00:00+00, 3@2001-01-05 00:00:00+00]}'
SELECT tfloats(ARRAY[tfloat '[1@2001-01-01, 3@2001-01-05)', '[3@2001-01-05]']);
-- '{[1@2001-01-01 00:00:00+00, 3@2001-01-05 00:00:00+00]}'
SELECT tgeompoint '{[Point(0 0)@2001-01-01 08:00:00,
  Point(1 1)@2001-01-01 08:05:00, Point(1 1)@2001-01-01 08:10:00),
  [Point(1 1)@2001-01-01 08:10:00, Point(1 1)@2001-01-01 08:15:00)}';
-- "{[[Point(0 0)@2001-01-01 08:00:00, Point(1 1)@2001-01-01 08:05:00,
  Point(1 1)@2001-01-01 08:15:00)}"
SELECT tgeompoint '{[Point(1 1)@2001-01-01 08:00:00, Point(2 2)@2001-01-01 08:05:00),
  [Point(2 2)@2001-01-01 08:05:00, Point(3 3)@2001-01-01 08:10:00]}';
-- "{[Point(1 1)@2001-01-01 08:00:00, Point(3 3)@2001-01-01 08:10:00]}"
SELECT tgeompoint '{[Point(1 1)@2001-01-01 08:00:00, Point(3 3)@2001-01-01 08:10:00),
  [Point(3 3)@2001-01-01 08:10:00]}';
-- "{[Point(1 1)@2001-01-01 08:00:00, Point(3 3)@2001-01-01 08:10:00]}"

Temporal types support type modifiers (or typmod in PostgreSQL terminology), which specify additional information for a column definition. For example, in the following table definition:

CREATE TABLE Department(DeptNo integer, DeptName varchar(25), NoEmps tint(Sequence));

the type modifier for the type varchar is the value 25, which indicates the maximum length of the values of the column, while the type modifier for the type tint is the string Sequence, which restricts the subtype of the values of the column to be sequences. In the case of temporal alphanumeric types (that is, tbool, tint, tfloat, and ttext), the possible values for the type modifier are Instant, InstantSet, Sequence, and SequenceSet. If no type modifier is specified for a column, values of any subtype are allowed.

On the other hand, in the case of temporal point types (that is, tgeompoint or tgeogpoint) the type modifier may be used to specify specify the subtype, the dimensionality, and/or the spatial reference identifier (SRID). For example, in the following table definition:

CREATE TABLE Flight(FlightNo integer, Route tgeogpoint(Sequence, PointZ, 4326));

the type modifier for the type tgeogpoint is composed of three values, the first one indicating the subtype as above, the second one the spatial type of the geographies composing the temporal point, and the last one the SRID of the composing geographies. For temporal points, the possible values for the first argument of the type modifier are as above, those for the second argument are either Point or PointZ, and those for the third argument are valid SRIDs. All the three arguments are optional and if any of them is not specified for a column, values of any subtype, dimensionality, and/or SRID are allowed.

Each temporal type is associated to another type, referred to as its bounding box, which represent its extent in the value and/or the time dimension. The bounding box of the various temporal types are as follows:

A rich set of functions and operators is available to perform various operations on temporal types. They are explained in Chapter 5. Some of these operations, in particular those related to indexes, manipulate bounding boxes for efficiency reasons.

CREATE TABLE Department(DeptNo integer, DeptName varchar(25), NoEmps tint);
INSERT INTO Department VALUES
  (10, 'Research', tint '[10@2012-01-01, 12@2012-04-01, 12@2012-08-01)'),
  (20, 'Human Resources', tint '[4@2012-02-01, 6@2012-06-01, 6@2012-10-01)');
CREATE TABLE Temperature(RoomNo integer, Temp tfloat);
INSERT INTO Temperature VALUES
  (1001, tfloat '{18.5@2012-01-01 08:00:00, 20.0@2012-01-01 08:10:00}'),
  (2001, tfloat '{19.0@2012-01-01 08:00:00, 22.5@2012-01-01 08:10:00}');
-- Value at a timestamp
SELECT RoomNo, valueAtTimestamp(Temp, '2012-01-01 08:10:00')
FROM temperature;
-- 1001;
   2001;22.5
-- Restriction to a value
SELECT DeptNo, atValue(NoEmps, 10)
FROM Department;
-- 10;"[10@2012-01-01 00:00:00+00, 10@2012-04-01 00:00:00+00)"
   20; NULL
-- Restriction to a period
SELECT DeptNo, atPeriod(NoEmps, '[2012-01-01, 2012-04-01]')
FROM Department;
-- 10;"[10@2012-01-01 00:00:00+00, 12@2012-04-01 00:00:00+00]"
   20;"[4@2012-02-01 00:00:00+00, 4@2012-04-01 00:00:00+00]"
-- Temporal comparison
SELECT DeptNo, NoEmps #<= 10
FROM Department;
-- 10;"[t@2012-01-01 00:00:00+00, f@2012-04-01 00:00:00+00, f@2012-08-01 00:00:00+00)"
   20;"[t@2012-04-02 00:00:00+00, t@2012-10-01 00:00:00+00)"
-- Temporal aggregation
SELECT tsum(NoEmps)
FROM Department;
-- "{[10@2012-01-01 00:00:00+00, 14@2012-02-01 00:00:00+00, 16@2012-04-01 00:00:00+00,
  18@2012-06-01 00:00:00+00, 6@2012-08-01 00:00:00+00, 6@2012-10-01 00:00:00+00)}"

CREATE TABLE Trips(CarId integer, TripId integer, Trip tgeompoint);
INSERT INTO Trips VALUES
  (10, 1, tgeompoint '{[Point(0 0)@2012-01-01 08:00:00, Point(2 0)@2012-01-01 08:10:00,
Point(2 1)@2012-01-01 08:15:00)}'),
  (20, 1, tgeompoint '{[Point(0 0)@2012-01-01 08:05:00, Point(1 1)@2012-01-01 08:10:00,
  Point(3 3)@2012-01-01 08:20:00)}');
-- Value at a given timestamp
SELECT CarId, ST_AsText(valueAtTimestamp(Trip, timestamptz '2012-01-01 08:10:00'))
FROM Trips;
-- 10;"POINT(2 0)"
   20;"POINT(1 1)"
-- Restriction to a given value
SELECT CarId, asText(atValue(Trip, 'Point(2 0)'))
FROM Trips;
-- 10;"{"[POINT(2 0)@2012-01-01 08:10:00+00]"}"
   20; NULL
-- Restriction to a period
SELECT CarId, asText(atPeriod(Trip, '[2012-01-01 08:05:00,2012-01-01 08:10:00]'))
FROM Trips;
-- 10;"{[POINT(1 0)@2012-01-01 08:05:00+00, POINT(2 0)@2012-01-01 08:10:00+00]}"
   20;"{[POINT(0 0)@2012-01-01 08:05:00+00, POINT(1 1)@2012-01-01 08:10:00+00]}"
-- Temporal distance
SELECT T1.CarId, T2.CarId, T1.Trip <-> T2.Trip
FROM Trips T1, Trips T2
WHERE T1.CarId < T2.CarId;
-- 10;20;"{[1@2012-01-01 08:05:00+00, 1.4142135623731@2012-01-01 08:10:00+00,
  1@2012-01-01 08:15:00+00)}"