Chapter 4. Temporal Types

MobilityDB provides six built-in temporal types, namely, tbool, tint, tfloat, ttext, tgeompoint, and tgeogpoint, which are, respectively, based on the base types bool, integer, 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 discrete when the value is unknown between two successive instants. They can represent, for example, checkins/checkouts when using an RFID card reader to enter or exit a building. The interpolation is step when the value remains constant between two successive instants. For example, the gear used by a moving car 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 speed of a car may be represented with a temporal float, which indicates that the values are known at the 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 step manner. On the other hand, temporal types based on continuous base types, that is tfloat, tgeompoint, or tgeogpoint may evolve in a step 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 three subtypes, explained next.

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 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 a discrete, step, or a linear function (see above). An example is as follows:

-- Discrete interpolation
SELECT tfloat '{17@2018-01-01 08:00:00, 17.5@2018-01-01 08:05:00, 18@2018-01-01 08:10:00}';
-- Step interpolation
SELECT tfloat 'Interp=Step;(10@2018-01-01 08:00:00, 20@2018-01-01 08:05:00,
  15@2018-01-01 08:10:00]';
-- Linear interpolation
SELECT tfloat '(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 ‘)'). By definition, both bounds must be inclusive when the interpolation is discrete or when the sequence has a single instant (called an instantaneous sequence), as the next example

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

Sequence values must be uniform, that is, they must be composed of instant values of the same base type. Sequence values with step or linear interpolation are referred to as continuous sequences.

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 step 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]}';

As shown in the above examples, sequence set values can only be of step or linear interpolation. Furtheremore, all composing sequences of a sequence set value must be of the same base type and the same interpolation.

Continuous sequence values are converted into normal form so that equivalent values have identical representations. For this, consecutive instant values are merged when possible. For step 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 asText(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 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 asText(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 tint '{[1@2001-01-01, 1@2001-01-03), [2@2001-01-03, 2@2001-01-05)}';
-- '{[1@2001-01-01, 2@2001-01-03, 2@2001-01-05)}'
SELECT tfloat '{[1@2001-01-01, 2@2001-01-03), [2@2001-01-03, 3@2001-01-05]}';
-- '{[1@2001-01-01, 3@2001-01-05]}'
SELECT tfloat '{[1@2001-01-01, 3@2001-01-05), [3@2001-01-05]}';
-- '{[1@2001-01-01, 3@2001-01-05]}'
SELECT asText(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 asText(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 asText(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, 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 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 the section called “Introduction” and in the following chapters. Some of these operations, in particular those related to indexes, manipulate bounding boxes for efficiency reasons.