Restriction Functions

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]}"
    

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)}"