Chapter 10. Temporal Types: Aggregation and Indexing

For all temporal types, the function tXount generalize the traditional function count. The temporal count can be used to compute at each point in time the number of available objects (for example, number of cars in an area).

For all temporal types, function extent returns a bounding box that encloses a set of temporal values. Depending on the base type, the result of this function can be a tstzspan, a tbox or an stbox.

For the temporal Boolean type, the functions tAnd and tor generalize the traditional functions and and or.

For temporal numeric types, there are two types of temporal aggregate functions. The functions tMin, tMax, tSum, and tAvg generalize the traditional functions min, max, sum, and avg. Furthermore, the functions wMin, wMax, wCount, wSum, and wAvg are window (or cumulative) versions of the traditional functions that, given a time interval w, compute the value of the function at an instant t by considering the values during the interval [t-w, t]. All window aggregate functions are available for temporal integers, while for temporal floats only window minimum and maximum are meaningful.

For the temporal text type, the functions tMin y tMax generalize the traditional functions min and max.

Finally, for temporal point types, the function tCentroid generalizes the function ST_Centroid provided by PostGIS. For example, given set of objects that move together (that is, a convoy or a flock) the temporal centroid will produce a temporal point that represents at each instant the geometric center (or the center of mass) of all the moving objects.

Temporal count

tCount(ttype) → {tintSeq,tintSeqSet}

SELECT tCount(NoEmps) FROM Department;
-- {[1@2001-01-01, 2@2001-02-01, 1@2001-08-01, 1@2001-10-01)}

Bounding box extent

extent(temp) → {tstzspan,tbox,stbox}

SELECT extent(noEmps) FROM Department;
-- TBOX XT((4,12),[2001-01-01,2001-10-01])
SELECT extent(Trip) FROM Trips;
-- STBOX XT(((0,0),(3,3)),[2001-01-01 08:00:00+01, 2001-01-01 08:20:00+01))

Temporal and

tAnd(tbool) → tbool

SELECT tAnd(NoEmps #> 6) FROM Department;
-- {[t@2001-01-01, f@2001-04-01, f@2001-10-01)}

Temporal or

tOr(tbool) → tbool

SELECT tOr(NoEmps #> 6) FROM Department;
-- {[t@2001-01-01, f@2001-08-01, f@2001-10-01)}

Temporal minimum

tMin(ttype) → ttype

SELECT tMin(NoEmps) FROM Department;
-- {[10@2001-01-01, 4@2001-02-01, 6@2001-06-01, 6@2001-10-01)}

Temporal maximum

tMax(ttype) → ttype

SELECT tMax(NoEmps) FROM Department;
-- {[10@2001-01-01, 12@2001-04-01, 6@2001-08-01, 6@2001-10-01)}

Temporal sum

tSum(tnumber) → {tnumberSeq,tnumberSeqSet}

SELECT tSum(NoEmps) FROM Department;
/* {[10@2001-01-01, 14@2001-02-01, 16@2001-04-01, 18@2001-06-01, 6@2001-08-01,
   6@2001-10-01)} */

Temporal average

tAvg(tnumber) → {tfloatSeq,tfloatSeqSet}

SELECT tAvg(NoEmps) FROM Department;
/* {[10@2001-01-01, 10@2001-02-01), [7@2001-02-01, 7@2001-04-01),
   [8@2001-04-01, 8@2001-06-01), [9@2001-06-01, 9@2001-08-01),
   [6@2001-08-01, 6@2001-10-01) */

Window minimum

wMin(tnumber,interval) → {tnumberSeq,tnumberSeqSet}

SELECT wMin(NoEmps, interval '2 days') FROM Department;
-- {[10@2001-01-01, 4@2001-04-01, 6@2001-06-03, 6@2001-10-03)}

Window maximum

wMax(tnumber,interval) → {tnumberDiscSeq,tnumberSeqSet}

SELECT wMax(NoEmps, interval '2 days') FROM Department;
-- {[10@2001-01-01, 12@2001-04-01, 6@2001-08-03, 6@2001-10-03)}

Window count

wCount(tnumber,interval) → {tintSeq,tintSeqSet}

SELECT wCount(NoEmps, interval '2 days') FROM Department;
/* {[1@2001-01-01, 2@2001-02-01, 3@2001-04-01, 2@2001-04-03, 3@2001-06-01, 2@2001-06-03,
   1@2001-08-03, 1@2001-10-03)} */

Window sum

wSum(tint,interval) → {tintSeq,tintSeqSet}

SELECT wSum(NoEmps, interval '2 days') FROM Department;
/* {[10@2001-01-01, 14@2001-02-01, 26@2001-04-01, 16@2001-04-03, 22@2001-06-01,
   18@2001-06-03, 6@2001-08-03, 6@2001-10-03)} */

Window average

wAvg(tint,interval) → {tfloatSeq,tfloatSeqSet}

SELECT wAvg(NoEmps, interval '2 days') FROM Department;
/* {[10@2001-01-01, 10@2001-02-01), [7@2001-02-01, 7@2001-04-01),
   [8.66666666666667@2001-04-01, 8.66666666666667@2001-04-03),
   [8@2001-04-03, 8@2001-06-01),
   [7.33333333333333@2001-06-01, 7.33333333333333@2001-06-03),
   [9@2001-06-03, 9@2001-08-03), [6@2001-08-03, 6@2001-10-03)} */

Temporal centroid

tCentroid(tgeompoint) → tgeompoint

SELECT tCentroid(Trip) FROM Trips;
/* {[POINT(0 0)@2001-01-01 08:00:00+00, POINT(1 0)@2001-01-01 08:05:00+00),
   [POINT(0.5 0)@2001-01-01 08:05:00+00, POINT(1.5 0.5)@2001-01-01 08:10:00+00,
   POINT(2 1.5)@2001-01-01 08:15:00+00),
   [POINT(2 2)@2001-01-01 08:15:00+00, POINT(3 3)@2001-01-01 08:20:00+00)} */