Table of Contents
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:
ttype represents any temporal type,
time represents any time type, that is, timestamptz, period, timestampset, or periodset,
tnumber represents any temporal number type, that is, tint or tfloat,
torder represents any temporal type whose base type has a total order defined, that is, tint, tfloat, or ttext,
tpoint represents a temporal point type, that is, tgeompoint or tgeogpoint,
ttypeinst represents any temporal type with instant duration,
ttypei represents any temporal type with instant set duration,
ttypeseq represents any temporal type with sequence duration,
tdiscseq represents any temporal type with sequence duration and a discrete base type,
tcontseq represents any temporal type with sequence duration and a continuous base type,
ttypes represents any temporal type with sequence set duration,
base represents any base type of a temporal type, that is, bool, int, float, text, geometry, or geography,
number represents any number base type, that is, int or float,
numrange represents any number range of values, that is, either intrange or floatrange,
geo represents either geometry or geography,
point represents a geometry or a geography restricted to a point.
type[] represents an array of type.
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.
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)