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,
ttype_inst represents any temporal type with instant subtype,
ttype_instset represents any temporal type with instant set subtype,
ttype_seq represents any temporal type with sequence subtype,
tdisc_seq represents any temporal type with sequence subtype and a discrete base type,
tcont_seq represents any temporal type with sequence subtype and a continuous base type,
ttype_seqset represents any temporal type with sequence set subtype,
base represents any base type of a temporal type, that is, boolean, integer, float, text, geometry, or geography,
number represents any number base type, that is, integer or float,
numrange represents any number range type, that is, either intrange or floatrange,
geo represents the types geometry or geography,
geompoint represents the type geometry restricted to a point.
point represents the types geometry or 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.
An 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 sequence value is a set of values v1@t1,...,vn@tn delimited by lower and upper bounds, which 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 an instant value is a single instant while the temporal extent of a 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 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 an instant set value is a set of timestamps while the temporal extent of a sequence set value is a set of periods.
Sequence or sequence set values 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 sequence set values 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)