Chapter 5. Manipulating Temporal Types

Table of Contents

Input/Output of Temporal Types
Constructor Functions
Casting
Transformation Functions
Accessor Functions
Spatial Functions
Restriction Functions
Difference Functions
Comparison Operators
Ever and Always Comparison Operators
Temporal Comparison Operators
Mathematical Functions and Operators
Boolean Operators
Text Functions and Operators
Bounding Box Operators
Distance Operators
Topological Relationships for Temporal Points
Possible Spatial Relationships
Temporal Spatial Relationships
Aggregate Functions for Temporal Types
Utility Functions
Indexing of Temporal Types
Statistics and Selectivity for Temporal Types
Statistics Collection
Selectivity Estimation of Operators

We present next the functions and operators for temporal 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, we use the following notation:

A common way to generalize the traditional operations to the temporal types is to apply the operation at each instant, which yields a temporal value as result. In that case, the operation is only defined on the intersection of the emporal extents of the operands; if the temporal extents are disjoint, then the result is null. For example, the temporal comparison operators, such as #<, test whether the values taken by their operands at each instant satisfy the condition and return a temporal Boolean. Examples of the various generalizations of the operators are given next.

-- Temporal comparison
SELECT tint '[2@2001-01-01, 2@2001-01-03)' #< tfloat '[1@2001-01-01, 3@2001-01-03)';
-- "{[f@2001-01-01, f@2001-01-02], (t@2001-01-02, t@2001-01-03)}"
SELECT tfloat '[1@2001-01-01, 3@2001-01-03)' #< tfloat '[3@2001-01-03, 1@2001-01-05)';
-- NULL
-- Temporal addition
SELECT tint '[1@2001-01-01, 1@2001-01-03)' + tint '[2@2001-01-02, 2@2001-01-05)';
-- "[3@2001-01-02, 3@2001-01-03)"
-- Temporal intersects
SELECT tintersects(tgeompoint '[Point(0 1)@2001-01-01, Point(3 1)@2001-01-04)',
geometry 'Polygon((1 0,1 2,2 2,2 0,1 0))');
-- "{[f@2001-01-01, t@2001-01-02, t@2001-01-03], (f@2001-01-03, f@2001-01-04]}"
-- Temporal distance
SELECT tgeompoint '[Point(0 0)@2001-01-01 08:00:00, Point(0 1)@2001-01-03 08:10:00)' <->
tgeompoint '[Point(0 0)@2001-01-02 08:05:00, Point(1 1)@2001-01-05 08:15:00)';
-- "[0.5@2001-01-02 08:05:00+00, 0.745184033794557@2001-01-03 08:10:00+00)"
		

Another common requirement is to determine whether the operands ever or always satisfy a condition with respect to an operation. These can be obtained by applying the ever/always comparison operators. These operators are denoted by prefixing the traditional comparison operators with, respectively, ? (ever) and % (always). Examples of ever and always comparison operators are given next.

-- Does the operands ever intersect?
SELECT tintersects(tgeompoint '[Point(0 1)@2001-01-01, Point(3 1)@2001-01-04)',
geometry 'Polygon((1 0,1 2,2 2,2 0,1 0))') ?= true;
-- true
-- Does the operands always intersect?
SELECT tintersects(tgeompoint '[Point(0 1)@2001-01-01, Point(3 1)@2001-01-04)',
geometry 'Polygon((0 0,0 2,4 2,4 0,0 0))') %= true;
-- true
-- Is the left operand ever less than the right one ?
SELECT (tfloat '[1@2001-01-01, 3@2001-01-03)' #<
tfloat '[3@2001-01-01, 1@2001-01-03)') ?= true;
-- true
-- Is the left operand always less than the right one ?
SELECT (tfloat '[1@2001-01-01, 3@2001-01-03)' #<
tfloat '[2@2001-01-01, 4@2001-01-03)') %= true;
-- true
		

For efficiency reasons, some common operations with the ever or the always semantics are natively provided. For example, the intersects function determines whether there is an instant at which the two arguments spatially intersect.

We describe next the functions and operators for temporal types. For conciseness, in the examples we mostly use sequences composed of two instants.

Input/Output of Temporal Types

A temporal instant value is a couple of the form v@t, where v is a value of the base type and t is a timestamptz value. A temporal sequence value is a set of values v1@t1,...,vn@tn delimited by a lower and an upper bounds that can be inclusive (represented by ‘[' and ‘]') or exclusive (represented by ‘(' and ‘)'). Examples of input of temporal unit values are as follows:

SELECT tbool 'true@2001-01-01 08:00:00';
SELECT tint '1@2001-01-01 08:00:00';
SELECT tfloat '1.5@2001-01-01 08:00:00';
SELECT ttext 'AAA@2001-01-01 08:00:00';
SELECT tgeompoint 'Point(0 0)@2017-01-01 08:00:05';
SELECT tgeogpoint 'Point(0 0)@2017-01-01 08:00:05';
SELECT tbool '[true@2001-01-01 08:00:00, true@2001-01-03 08:00:00]';
SELECT tint '[1@2001-01-01 08:00:00, 1@2001-01-03 08:00:00]';
SELECT tfloat '[2.5@2001-01-01 08:00:00, 3@2001-01-03 08:00:00, 1@2001-01-04 08:00:00]';
SELECT tfloat '[1.5@2001-01-01 08:00:00]';  -- Instant sequence
SELECT ttext '[BBB@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00]';
SELECT tgeompoint '[Point(0 0)@2017-01-01 08:00:00, Point(0 0)@2017-01-01 08:05:00)';
SELECT tgeogpoint '[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00,
Point(0 0)@2017-01-01 08:10:00)';
			

The temporal extent of a temporal instant value is a single instant while the temporal extent of temporal sequence value is a period defined by the first and last instants as well as the upper and lower bounds.

A temporal set value is a set {v1,...,vn} where every vi is a temporal unit value of the corresponding type. Examples of input of temporal set values are as follows:

SELECT tbool '{true@2001-01-01 08:00:00, false@2001-01-03 08:00:00}';
SELECT tint '{1@2001-01-01 08:00:00, 2@2001-01-03 08:00:00}';
SELECT tfloat '{1.0@2001-01-01 08:00:00, 2.0@2001-01-03 08:00:00}';
SELECT ttext '{AAA@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00}';
SELECT tgeompoint '{Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-02 08:05:00}';
SELECT tgeogpoint '{Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-02 08:05:00}';
SELECT tbool '{[false@2001-01-01 08:00:00, false@2001-01-03 08:00:00),
[true@2001-01-03 08:00:00], (false@2001-01-04 08:00:00, false@2001-01-06 08:00:00]}';
SELECT tint '{[1@2001-01-01 08:00:00, 1@2001-01-03 08:00:00),
[2@2001-01-04 08:00:00, 3@2001-01-05 08:00:00, 3@2001-01-06 08:00:00]}';
SELECT tfloat '{[1@2001-01-01 08:00:00, 2@2001-01-03 08:00:00, 2@2001-01-04 08:00:00,
3@2001-01-06 08:00:00]}';
SELECT ttext '{[AAA@2001-01-01 08:00:00, BBB@2001-01-03 08:00:00, BBB@2001-01-04 08:00:00),
[CCC@2001-01-05 08:00:00, CCC@2001-01-06 08:00:00]}';
SELECT tgeompoint '{[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00),
[Point(0 1)@2017-01-01 08:10:00, Point(1 1)@2017-01-01 08:15:00)}';
SELECT tgeogpoint '{[Point(0 0)@2017-01-01 08:00:00, Point(0 1)@2017-01-01 08:05:00),
[Point(0 1)@2017-01-01 08:10:00, Point(1 1)@2017-01-01 08:15:00)}';
			

The temporal extent of a temporal instant set value is a set of instants while the temporal extent of temporal sequence set value is a set of periods.

Temporal values of sequence or sequence set duration whose base type is continuous may specify that the interpolation is stepwise. If this is not specified, it is supposed that the interpolation is linear by default.

-- Linear interpolation by default
SELECT tfloat '[2.5@2001-01-01, 3@2001-01-03, 1@2001-01-04]';
SELECT tgeompoint '{[Point(2.5 2.5)@2001-01-01, Point(3 3)@2001-01-03],
[Point(1 1)@2001-01-04, Point(1 1)@2001-01-04]}';
-- Stepwise interpolation
SELECT tfloat 'Interp=Stepwise;[2.5@2001-01-01, 3@2001-01-03, 1@2001-01-04]';
SELECT tgeompoint 'Interp=Stepwise;{[Point(2.5 2.5)@2001-01-01, Point(3 3)@2001-01-03],
[Point(1 1)@2001-01-04, Point(1 1)@2001-01-04]}';
			

For temporal values of sequence set duration all component sequences are supposed to be in the same interpolation, either stepwise or linear, as in the examples above.

For temporal points, it is possible to specify the spatial reference identifier (SRID) using the Extended Well-Known text (EWKT) representation as follows:

SELECT tgeompoint 'SRID=5435;[Point(0 0)@2000-01-01,Point(0 1)@2000-01-02]'
			

All components geometries will then be of the given SRID. Furthermore, each component geometry can specify its SRID with the EWKT format as in the following example

SELECT tgeompoint '[SRID=5435;Point(0 0)@2000-01-01,SRID=5435;Point(0 1)@2000-01-02]'
			

An error is raised if the component geometries are not all in the same SRID or if the SRID of a component geometry is different from the one of the temporal point

SELECT tgeompoint '[SRID=5435;Point(0 0)@2000-01-01,SRID=4326;Point(0 1)@2000-01-02]';
ERROR: Geometry SRID (4326) does not match temporal type SRID (5435)
SELECT tgeompoint 'SRID=5435;[SRID=4326;Point(0 0)@2000-01-01,
SRID=4326;Point(0 1)@2000-01-02]'
ERROR: Geometry SRID (4326) does not match temporal type SRID (5435)