Accessor Functions

Accessor Functions
Prev	Chapter 5. Manipulating Temporal Types	Next

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

Prev	Up	Next
Transformation Functions	Home	Spatial Functions