5.6. Restriction Functions

5.6. Restriction Functions
Prev	Chapter 5. Manipulating Temporal Types	Next

5.6.1. Selection Functions

These functions restrict the temporal value with respect to a value or a time extent.

Restrict to a value

atValue(ttype,base): ttype

SELECT atValue(tint '[1@2012-01-01, 1@2012-01-15)', 1);
-- "[1@2012-01-01, 1@2012-01-15)"
SELECT asText(atValue(tgeompoint '[Point(0 0 0)@2012-01-01, Point(2 2 2)@2012-01-03)',
  'Point(1 1 1)'));
-- "{[POINT Z (1 1 1)@2012-01-02]}"

Restrict to an array of values

atValues(ttype,base[]): ttype

SELECT atValues(tfloat '[1@2012-01-01, 4@2012-01-4)', ARRAY[1, 3, 5]);
-- "{[1@2012-01-01], [3@2012-01-03]}"
SELECT asText(atValues(tgeompoint '[Point(0 0)@2012-01-01, Point(2 2)@2012-01-03)',
  ARRAY[geometry 'Point(0 0)', 'Point(1 1)']));
-- "{[POINT(0 0)@2012-01-01 00:00:00+00], [POINT(1 1)@2012-01-02 00:00:00+00]}"

Restrict to a range

atRange(tnumber,numrange): ttype

SELECT atRange(tfloat '[1@2012-01-01, 4@2012-01-4)', floatrange '[1,3]');
-- "[1@2012-01-01, 3@2012-01-03]"

Restrict to an array of ranges

atRanges(tnumber,numrange[]): ttype

SELECT atRanges(tfloat '[1@2012-01-01, 5@2012-01-05)',
  ARRAY[floatrange '[1,2]', '[3,4]']);
-- "{[1@2012-01-01, 2@2012-01-02],[3@2012-01-03, 4@2012-01-04]}"

Restrict to the minimum value

atMin(torder): torder

The function returns null if the minimum value only happens at exclusive bounds.

SELECT atMin(tint '{1@2012-01-01, 2@2012-01-03, 1@2012-01-05}');
-- "{1@2012-01-01, 1@2012-01-05}"
SELECT atMin(tint '(1@2012-01-01, 3@2012-01-03]');
-- "{(1@2012-01-01, 1@2012-01-03)}"
SELECT atMin(tfloat '(1@2012-01-01, 3@2012-01-03]');
-- NULL
SELECT atMin(ttext '{(AA@2012-01-01, AA@2012-01-03), (BB@2012-01-03, AA@2012-01-05]}');
-- "{(AA@2012-01-01, AA@2012-01-03), [AA@2012-01-05]}"

Restrict to the maximum value

atMax(torder): torder

The function returns null if the maximum value only happens at exclusive bounds.

SELECT atMax(tint '{1@2012-01-01, 2@2012-01-03, 3@2012-01-05}');
-- "{3@2012-01-05}"
SELECT atMax(tfloat '(1@2012-01-01, 3@2012-01-03)');
-- NULL
SELECT atMax(tfloat '{(2@2012-01-01, 1@2012-01-03), [2@2012-01-03, 2@2012-01-05)}');
-- "{[2@2012-01-03, 2@2012-01-05]}"
SELECT atMax(ttext '{(AA@2012-01-01, AA@2012-01-03), (BB@2012-01-03, AA@2012-01-05]}');
-- "{("BB"@2012-01-03, "BB"@2012-01-05)}"

Restrict to a geometry

atGeometry(tgeompoint,geometry): tgeompoint

Notice that it is allowed to mix 2D/3D geometries but the computation is only performed on 2D.

SELECT asText(atGeometry(tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)',
  geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))'));
-- "{"[POINT(1 1)@2012-01-02, POINT(2 2)@2012-01-03]"}"
SELECT astext(atGeometry(tgeompoint '[Point(0 0 0)@2000-01-01, Point(4 4 4)@2000-01-05]',
  geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))'));
-- "{[POINT Z (1 1 1)@2000-01-02, POINT Z (2 2 2)@2000-01-03]}"

Restrict to a timestamp

atTimestamp(ttype,timestamptz): ttype_inst

SELECT atTimestamp(tfloat '[1@2012-01-01, 5@2012-01-05)', '2012-01-02');
-- "2@2012-01-02"

Restrict to a timestamp set

atTimestampSet(ttype,timestampset): {ttype_inst,ttype_instset}

SELECT atTimestampSet(tint '[1@2012-01-01, 1@2012-01-15)',
  timestampset '{2012-01-01, 2012-01-03}');
-- "{1@2012-01-01, 1@2012-01-03}"

Restrict to a period

atPeriod(ttype,period): ttype

SELECT atPeriod(tfloat '{[1@2012-01-01, 3@2012-01-03), [3@2012-01-04, 1@2012-01-06)}',
  '[2012-01-02,2012-01-05)');
-- "{[2@2012-01-02, 3@2012-01-03), [3@2012-01-04, 2@2012-01-05)}"

Restrict to a period set

atPeriodSet(ttype,periodset): ttype

SELECT atPeriodSet(tint '[1@2012-01-01, 1@2012-01-15)',
  periodset '{[2012-01-01, 2012-01-03), [2012-01-04, 2012-01-05)}');
-- "{[1@2012-01-01, 1@2012-01-03),[1@2012-01-04, 1@2012-01-05)}"

Restrict to a tbox

atTbox(tnumber,tbox): tnumber

SELECT atTbox(tfloat '[0@2012-01-01, 3@2012-01-04)',
  tbox 'TBOX((0, 2012-01-02), (2, 2012-01-04))');
-- "{[1@2012-01-02, 2@2012-01-03]}"

Restrict to an stbox

atStbox(tgeompoint,stbox): tgeompoint

SELECT asText(atStbox(tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)',
  stbox 'STBOX T((0, 0, 2012-01-02), (2, 2, 2012-01-04))'));
-- "{[POINT(1 1)@2012-01-02, POINT(2 2)@2012-01-03]}"

5.6.2. Difference Functions

These functions restrict the temporal value with respect to the complement of a value or a time extent.

Difference with a value

minusValue(ttype,base): ttype

SELECT minusValue(tint '[1@2012-01-01, 2@2012-01-02, 2@2012-01-03)', 1);
-- "{[2@2012-01-02, 2@2012-01-03)}"
SELECT asText(minusValue(tgeompoint '[Point(0 0 0)@2012-01-01, Point(2 2 2)@2012-01-03)',
  'Point(1 1 1)'));
-- "{[POINT Z (0 0 0)@2012-01-01, POINT Z (1 1 1)@2012-01-02),
  (POINT Z (1 1 1)@2012-01-02, POINT Z (2 2 2)@2012-01-03)}"

Difference with an array of values

minusValues(ttype,base[]): ttype

SELECT minusValues(tfloat '[1@2012-01-01, 4@2012-01-4)', ARRAY[2, 3]);
-- "{[1@2012-01-01, 2@2012-01-02), (2@2012-01-02, 3@2012-01-03),
  (3@2012-01-03, 4@2012-01-04)}"
SELECT asText(minusValues(tgeompoint '[Point(0 0 0)@2012-01-01, Point(3 3 3)@2012-01-04)',
  ARRAY[geometry 'Point(1 1 1)', 'Point(2 2 2)']));
-- "{[POINT Z (0 0 0)@2012-01-01, POINT Z (1 1 1)@2012-01-02),
  (POINT Z (1 1 1)@2012-01-02, POINT Z (2 2 2)@2012-01-03),
  (POINT Z (2 2 2)@2012-01-03, POINT Z (3 3 3)@2012-01-04)}"

Difference with a range

minusRange(tnumber,numrange): ttype

SELECT minusRange(tfloat '[1@2012-01-01, 4@2012-01-4)', floatrange '[2,3]');
-- "{[1@2012-01-01, 2@2012-01-02), (3@2012-01-03, 4@2012-01-04)}"

Difference with an array of ranges

minusRanges(tnumber,numrange[]): ttype

SELECT minusRanges(tfloat '[1@2012-01-01, 5@2012-01-05)',
  ARRAY[floatrange '[1,2]', '[3,4]']);
-- "{(2@2012-01-02, 3@2012-01-03), (4@2012-01-04, 5@2012-01-05)}"

Difference with the minimum value

minusMin(torder): torder

SELECT minusMin(tint '{1@2012-01-01, 2@2012-01-03, 1@2012-01-05}');
-- "{2@2012-01-03}"
SELECT minusMin(tfloat '[1@2012-01-01, 3@2012-01-03]');
-- "{(1@2012-01-01, 3@2012-01-03]}"
SELECT minusMin(tfloat '(1@2012-01-01, 3@2012-01-03)');
-- "{(1@2012-01-01, 3@2012-01-03)}"
SELECT minusMin(tint '{[1@2012-01-01, 1@2012-01-03), (1@2012-01-03, 1@2012-01-05)}');
-- NULL

Difference with the maximum value

minusMax(torder): torder

SELECT minusMax(tint '{1@2012-01-01, 2@2012-01-03, 3@2012-01-05}');
-- "{1@2012-01-01, 2@2012-01-03}"
SELECT minusMax(tfloat '[1@2012-01-01, 3@2012-01-03]');
-- "{[1@2012-01-01, 3@2012-01-03)}"
SELECT minusMax(tfloat '(1@2012-01-01, 3@2012-01-03)');
-- "{(1@2012-01-01, 3@2012-01-03)}"
SELECT minusMax(tfloat '{[2@2012-01-01, 1@2012-01-03), [2@2012-01-03, 2@2012-01-05)}');
-- "{(2@2012-01-01, 1@2012-01-03)}"
SELECT minusMax(tfloat '{[1@2012-01-01, 3@2012-01-03), (3@2012-01-03, 1@2012-01-05)}');
-- "{[1@2012-01-01, 3@2012-01-03), (3@2012-01-03, 1@2012-01-05)}"

Difference with a geometry

minusGeometry(tgeompoint,geometry): tgeompoint

Notice that it is allowed to mix 2D/3D geometries but the computation is only performed on 2D.

SELECT asText(minusGeometry(tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)',
  geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))'));
-- "{[POINT(0 0)@2012-01-01, POINT(1 1)@2012-01-02), (POINT(2 2)@2012-01-03,
  POINT(3 3)@2012-01-04)}"
SELECT astext(minusGeometry(tgeompoint '[Point(0 0 0)@2000-01-01,
  Point(4 4 4)@2000-01-05]', geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))'));
-- "{[POINT Z (0 0 0)@2000-01-01, POINT Z (1 1 1)@2000-01-02),
  (POINT Z (2 2 2)@2000-01-03, POINT Z (4 4 4)@2000-01-05]}"

Difference with a timestamp

minusTimestamp(ttype,timestamptz): ttype

SELECT minusTimestamp(tfloat '[1@2012-01-01, 5@2012-01-05)', '2012-01-02');
-- "{[1@2012-01-01, 2@2012-01-02), (2@2012-01-02, 5@2012-01-05)}"

Difference with a timestamp set

minusTimestampSet(ttype,timestampset): ttype

SELECT minusTimestampSet(tint '[1@2012-01-01, 1@2012-01-15)',
  timestampset '{2012-01-02, 2012-01-03}');
-- "{[1@2012-01-01, 1@2012-01-02), (1@2012-01-02, 1@2012-01-03),
(1@2012-01-03, 1@2012-01-15)}"

Difference with a period

minusPeriod(ttype,period): ttype

SELECT minusPeriod(tfloat '{[1@2012-01-01, 3@2012-01-03), [3@2012-01-04, 1@2012-01-06)}',
  '[2012-01-02,2012-01-05)');
-- "{[1@2012-01-01, 2@2012-01-02), [2@2012-01-05, 1@2012-01-06)}"

Difference with a period set

minusPeriodSet(ttype,periodset): ttype

SELECT minusPeriodSet(tint '[1@2012-01-01, 1@2012-01-15)',
  periodset '{[2012-01-02, 2012-01-03), [2012-01-04, 2012-01-05)}');
-- "{[1@2012-01-01, 1@2012-01-02), [1@2012-01-03, 1@2012-01-04),
  [1@2012-01-05, 1@2012-01-15)}"

Difference with a tbox

minusTbox(tnumber,tbox): tnumber

SELECT minusTbox(tfloat '[0@2012-01-01, 3@2012-01-04)',
  tbox 'TBOX((0, 2012-01-02), (2, 2012-01-04))');
-- "{[0@2012-01-01, 1@2012-01-02), (2@2012-01-03, 3@2012-01-04)}"

Difference with an stbox

minusStbox(tgeompoint,stbox): tgeompoint

SELECT asText(minusStbox(tgeompoint '[Point(0 0)@2012-01-01, Point(3 3)@2012-01-04)',
  stbox 'STBOX T((0, 0, 2012-01-02), (2, 2, 2012-01-04))'));
-- "{[POINT(0 0)@2012-01-01, POINT(1 1)@2012-01-02),
  (POINT(2 2)@2012-01-03, POINT(3 3)@2012-01-04)}"

Prev	Up	Next
5.5. Modification Functions	Home	5.7. Comparison Operators