Get the memory size in bytes
memSize(ttype): integer
SELECT memSize(tint '{1@2012-01-01, 2@2012-01-02, 3@2012-01-03}'); -- 280
Get the temporal type
tempSubtype(ttype): {'Instant', 'InstantSet', 'Sequence', 'SequenceSet'}
SELECT tempSubtype(tint '[1@2012-01-01, 2@2012-01-02, 3@2012-01-03]'); -- "Sequence"
Get the interpolation
interpolation(ttype): {'Discrete', 'Stepwise', 'Linear'}
SELECT interpolation(tfloat '{1@2012-01-01, 2@2012-01-02, 3@2012-01-03}'); -- "Discrete" SELECT interpolation(tint '[1@2012-01-01, 2@2012-01-02, 3@2012-01-03]'); -- "Stepwise" SELECT interpolation(tfloat '[1@2012-01-01, 2@2012-01-02, 3@2012-01-03]'); -- "Linear" SELECT interpolation(tfloat 'Interp=Stepwise;[1@2012-01-01, 2@2012-01-02, 3@2012-01-03]'); -- "Stepwise" SELECT interpolation(tgeompoint 'Interp=Stepwise;[Point(1 1)@2012-01-01, Point(2 2)@2012-01-02, Point(3 3)@2012-01-03]'); -- "Stepwise"
Get the value
getValue(ttypeinst): base
SELECT getValue(tint '1@2012-01-01'); -- 1 SELECT ST_AsText(getValue(tgeompoint 'Point(0 0)@2012-01-01')); -- "POINT(0 0)"
Get the values
getValues(ttype): {base[], floatrange[], geo}
SELECT getValues(tint '[1@2012-01-01, 2@2012-01-03]'); -- "{1,2}" SELECT getValues(tfloat '[1@2012-01-01, 2@2012-01-03)'); -- "{[1,2)}" SELECT getValues(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 4@2012-01-05)}'); -- "{[1,2),[3,4)}" SELECT getValues(tfloat 'Interp=Stepwise;{[1@2012-01-01, 2@2012-01-02], [3@2012-01-03, 4@2012-01-05]}'); -- "{"[1,1]","[2,2]","[3,3]","[4,4]"}" SELECT ST_AsText(getValues(tgeompoint '{[Point(0 0)@2012-01-01, Point(0 1)@2012-01-02), [Point(0 1)@2012-01-03, Point(1 1)@2012-01-04)}')); -- "LINESTRING(0 0,0 1,1 1)" SELECT ST_AsText(getValues(tgeompoint '{[Point(0 0)@2012-01-01, Point(0 1)@2012-01-02), [Point(1 1)@2012-01-03, Point(2 2)@2012-01-04)}')); -- "MULTILINESTRING((0 0,0 1),(1 1,2 2)" SELECT ST_AsText(getValues(tgeompoint 'Interp=Stepwise;{[Point(0 0)@2012-01-01, Point(0 1)@2012-01-02], [Point(0 1)@2012-01-03, Point(1 1)@2012-01-04]}')); -- "GEOMETRYCOLLECTION(MULTIPOINT(0 0,0 1),MULTIPOINT(0 1,1 1))" SELECT ST_AsText(getValues(tgeompoint '{Point(0 0)@2012-01-01, Point(0 1)@2012-01-02}')); -- "MULTIPOINT(0 0,0 1)" SELECT ST_AsText(getValues(tgeompoint '{[Point(0 0)@2012-01-01, Point(0 1)@2012-01-02), [Point(1 1)@2012-01-03, Point(1 1)@2012-01-04), [Point(2 1)@2012-01-05, Point(2 2)@2012-01-06)}')); -- "GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,0 1),LINESTRING(2 1,2 2))"
Get the start value
startValue(ttype): base
The function does not take into account whether the bounds are inclusive or not.
SELECT startValue(tfloat '(1@2012-01-01, 2@2012-01-03)'); -- 1
Get the end value
endValue(ttype): base
The function does not take into account whether the bounds are inclusive or not.
SELECT endValue(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- 5
Get the minimum value
minValue(torder): base
The function does not take into account whether the bounds are inclusive or not.
SELECT minValue(tfloat '{1@2012-01-01, 2@2012-01-03, 3@2012-01-05}'); -- 1
Get the maximum value
maxValue(torder): base
The function does not take into account whether the bounds are inclusive or not.
SELECT maxValue(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- 5
Get the value range
valueRange(tnumber): numrange
The function does not take into account whether the bounds are inclusive or not.
SELECT valueRange(tfloat '{[2@2012-01-01, 1@2012-01-03), [4@2012-01-03, 6@2012-01-05)}'); -- "[1,6]" SELECT valueRange(tfloat '{1@2012-01-01, 2@2012-01-03, 3@2012-01-05}'); -- "[1,3])"
Get the value at a timestamp
valueAtTimestamp(ttype, timestamptz): base
SELECT valueAtTimestamp(tfloat '[1@2012-01-01, 4@2012-01-04)', '2012-01-02'); -- "2"
Get the timestamp
getTimestamp(ttypeinst): timestamptz
SELECT getTimestamp(tint '1@2012-01-01'); -- "2012-01-01"
Get the time
getTime(ttype): periodset
SELECT getTime(tint '[1@2012-01-01, 1@2012-01-15)'); -- "{[2012-01-01, 2012-01-15)}"
Get the duration
duration(ttype): interval
SELECT duration(period '[2012-01-01, 2012-01-03)'); -- "2 days" SELECT duration(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}'); -- "3 days"
Get the timespan ignoring the potential time gaps
timespan(ttype): interval
SELECT timespan(timestampset '{2012-01-01, 2012-01-03}'); -- "2 days" SELECT timespan(periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}'); -- "4 days"
Get the period on which the temporal value is defined ignoring the potential time gaps
period(ttype): period
SELECT period(tint '{1@2012-01-01, 2@2012-01-03, 3@2012-01-05}'); -- "[2012-01-01, 2012-01-05]" SELECT period(tfloat '{[1@2012-01-01, 1@2012-01-02), [2@2012-01-03, 3@2012-01-04)}'); -- "[2012-01-01, 2012-01-04)"
Get the number of different instants
numInstants(ttype): int
SELECT numInstants(tfloat '{[1@2000-01-01, 2@2000-01-02), (2@2000-01-02, 3@2000-01-03)}'); -- 3
Get the start instant
startInstant(ttype): ttypeinst
The function does not take into account whether the bounds are inclusive or not.
SELECT startInstant(tfloat '{[1@2000-01-01, 2@2000-01-02), (2@2000-01-02, 3@2000-01-03)}'); -- "1@2000-01-01"
Get the end instant
endInstant(ttype): ttypeinst
The function does not take into account whether the bounds are inclusive or not.
SELECT endInstant(tfloat '{[1@2000-01-01, 2@2000-01-02), (2@2000-01-02, 3@2000-01-03)}'); -- "3@2000-01-03"
Get the n-th different instant
instantN(ttype, int): ttypeinst
SELECT instantN(tfloat '{[1@2000-01-01, 2@2000-01-02), (2@2000-01-02, 3@2000-01-03)}', 3); -- "3@2000-01-03"
Get the different instants
instants(ttype): ttypeinst[]
SELECT instants(tfloat '{[1@2000-01-01, 2@2000-01-02), (2@2000-01-02, 3@2000-01-03)}'); -- "{"1@2000-01-01","2@2000-01-02","3@2000-01-03"}"
Get the number of different timestamps
numTimestamps(ttype): int
SELECT numTimestamps(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- 3
Get the start timestamp
startTimestamp(ttype): timestamptz
The function does not take into account whether the bounds are inclusive or not.
SELECT startTimestamp(tfloat '[1@2012-01-01, 2@2012-01-03)'); -- "2012-01-01"
Get the end timestamp
endTimestamp(ttype): timestamptz
The function does not take into account whether the bounds are inclusive or not.
SELECT endTimestamp(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- "2012-01-05"
Get the n-th different timestamp
timestampN(ttype, int): timestamptz
SELECT timestampN(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}', 3); -- "2012-01-05"
Get the different timestamps
timestamps(ttype): timestamptz[]
SELECT timestamps(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- "{"2012-01-01", "2012-01-03", "2012-01-05"}"
Get the number of sequences
numSequences({ttypeseq,ttypes}): int
SELECT numSequences(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- 2
Get the start sequence
startSequence({ttypeseq,ttypes}): ttypeseq
SELECT startSequence(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- "[1@2012-01-01, 2@2012-01-03)"
Get the end sequence
endSequence({ttypeseq,ttypes}): ttypeseq
SELECT endSequence(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- "[3@2012-01-03, 5@2012-01-05)"
Get the n-th sequence
sequenceN({ttypeseq,ttypes}, int): ttypeseq
SELECT sequenceN(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}', 2); -- "[3@2012-01-03, 5@2012-01-05)"
Get the sequences
sequences({ttypeseq,ttypes}): ttypeseq[]
SELECT sequences(tfloat '{[1@2012-01-01, 2@2012-01-03), [3@2012-01-03, 5@2012-01-05)}'); -- "{"[1@2012-01-01, 2@2012-01-03)", "[3@2012-01-03, 5@2012-01-05)"}"
Shift the timespan of the temporal value by an interval
shift(ttype, interval): ttype
SELECT shift(tint '{1@2001-01-01, 2@2001-01-03, 1@2001-01-05}', '1 day'); -- "{1@2001-01-02, 2@2001-01-04, 1@2001-01-06}" SELECT shift(tfloat '[1@2001-01-01, 2@2001-01-03]', '1 day'); -- "[1@2001-01-02, 2@2001-01-04]" SELECT asText(shift(tgeompoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-03], [Point(2 2)@2001-01-04, Point(1 1)@2001-01-05]}', '1 day')); -- "{[POINT(1 1)@2001-01-02, POINT(2 2)@2001-01-04], [POINT(2 2)@2001-01-05, POINT(1 1)@2001-01-06]}"
Scale the time span of the temporal value to an interval. If the time span of the temporal value is zero (for example, for a temporal instant), the result is the temporal value. The given interval must be strictly greater than zero.
tscale(ttype, interval): ttype
SELECT tscale(tint '1@2001-01-01', '1 day'); -- "1@2001-01-01" SELECT tscale(tint '{1@2001-01-01, 2@2001-01-03, 1@2001-01-05}', '1 day'); -- "{1@2001-01-01 00:00:00+01, 2@2001-01-01 12:00:00+01, 1@2001-01-02 00:00:00+01}" SELECT tscale(tfloat '[1@2001-01-01, 2@2001-01-03]', '1 day'); -- "[1@2001-01-01, 2@2001-01-02]" SELECT asText(tscale(tgeompoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03], [Point(2 2)@2001-01-04, Point(1 1)@2001-01-05]}', '1 day')); -- "{[POINT(1 1)@2001-01-01 00:00:00+01, POINT(2 2)@2001-01-01 06:00:00+01, POINT(1 1)@2001-01-01 12:00:00+01], [POINT(2 2) @2001-01-01 18:00:00+01, POINT(1 1)@2001-01-02 00:00:00+01]}" SELECT tscale(tint '1@2001-01-01', '-1 day'); -- ERROR: The duration must be a positive interval: -1 days
Shift and scale the time span of the temporal value to the two intervals. This function combines in a single step the functions shift
and tscale
.
shiftTscale(ttype, interval, interval): ttype
SELECT shiftTscale(tint '1@2001-01-01', '1 day', '1 day'); -- "1@2001-01-02" SELECT shiftTscale(tint '{1@2001-01-01, 2@2001-01-03, 1@2001-01-05}', '1 day', '1 day'); -- "{1@2001-01-02 00:00:00+01, 2@2001-01-02 12:00:00+01, 1@2001-01-03 00:00:00+01}" SELECT shiftTscale(tfloat '[1@2001-01-01, 2@2001-01-03]', '1 day', '1 day'); -- "[1@2001-01-02, 2@2001-01-03]" SELECT asText(shiftTscale(tgeompoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03], [Point(2 2)@2001-01-04, Point(1 1)@2001-01-05]}', '1 day', '1 day')); -- "{[POINT(1 1)@2001-01-02 00:00:00+01, POINT(2 2)@2001-01-02 06:00:00+01, POINT(1 1)@2001-01-02 12:00:00+01], [POINT(2 2) @2001-01-02 18:00:00+01, POINT(1 1)@2001-01-03 00:00:00+01]}"
Does the temporal value intersect the timestamp?
intersectsTimestamp(ttype, timestamptz): boolean
SELECT intersectsTimestamp(tint '[1@2012-01-01, 1@2012-01-15)', timestamptz '2012-01-03'); -- true
Does the temporal value intersect the timestamp set?
intersectsTimestampSet(ttype, timestampset): boolean
SELECT intersectsTimestampSet(tint '[1@2012-01-01, 1@2012-01-15)', timestampset '{2012-01-01, 2012-01-03}'); -- true
Does the temporal value intersect the period?
intersectsPeriod(ttype, period): boolean
SELECT intersectsPeriod(tint '[1@2012-01-01, 1@2012-01-04)', period '[2012-01-01,2012-01-05)'); -- true
Does the temporal value intersect the period set?
intersectsPeriodSet(ttype, periodset): boolean
SELECT intersectsPeriodSet(tbool '[t@2012-01-01, f@2012-01-15]', periodset '{[2012-01-01, 2012-01-03), [2012-01-05, 2012-01-07)}'); -- true
Get the time-weighted average
twAvg(tnumber): float
SELECT twAvg(tfloat '{[1@2012-01-01, 2@2012-01-03), [2@2012-01-04, 2@2012-01-06)}'); -- 1.75