There are two complementary sets of restriction functions. The first set functions restricts the temporal value with respect to a value or a time extent. Examples are atValues
or atTime
. The second set functions restricts the temporal value with respect to the complement of a value or a time extent. Examples are minusValues
or minusTime
.
Restrict to (the complement of) a set of values
atValues(ttype,values) → ttype
minusValues(ttype,values) → ttype
SELECT atValues(tint '[1@2001-01-01, 1@2001-01-15)', 1); -- [1@2001-01-01, 1@2001-01-15) SELECT atValues(tfloat '[1@2001-01-01, 4@2001-01-4)', floatset '{1, 3, 5}'); -- {[1@2001-01-01], [3@2001-01-03]} SELECT atValues(tfloat '[1@2001-01-01, 4@2001-01-4)', floatspan '[1,3]'); -- [1@2001-01-01, 3@2001-01-03] SELECT atValues(tfloat '[1@2001-01-01, 5@2001-01-05)', floatspanset '{[1,2], [3,4]}'); -- {[1@2001-01-01, 2@2001-01-02],[3@2001-01-03, 4@2001-01-04]} SELECT asText(atValues(tgeompoint '[Point(0 0 0)@2001-01-01, Point(2 2 2)@2001-01-03)', geometry 'Point(1 1 1)')); -- {[POINT Z (1 1 1)@2001-01-02]} SELECT asText(atValues(tgeompoint '[Point(0 0)@2001-01-01, Point(2 2)@2001-01-03)', geomset '{"Point(0 0)", "Point(1 1)"}')); -- {[POINT(0 0)@2001-01-01], [POINT(1 1)@2001-01-02]} SELECT asText(atValues(tgeometry '[Point(0 0)@2001-01-01, Linestring(0 0,2 2)@2001-01-02, Linestring(0 0,2 2)@2001-01-03]', geometry 'Linestring(0 0,2 2)')); -- {[LINESTRING(0 0,2 2)@2001-01-02, LINESTRING(0 0,2 2)@2001-01-03]}
SELECT minusValues(tint '[1@2001-01-01, 2@2001-01-02, 2@2001-01-03)', 1); -- {[2@2001-01-02, 2@2001-01-03)} SELECT minusValues(tfloat '[1@2001-01-01, 4@2001-01-4)', floatset '{2, 3}'); /* {[1@2001-01-01, 2@2001-01-02), (2@2001-01-02, 3@2001-01-03), (3@2001-01-03, 4@2001-01-04)} */ SELECT minusValues(tfloat '[1@2001-01-01, 4@2001-01-4)', floatspan '[2,3]'); -- {[1@2001-01-01, 2@2001-01-02), (3@2001-01-03, 4@2001-01-04)} SELECT minusValues(tfloat '[1@2001-01-01, 5@2001-01-05)', floatspanset '{[1,2], [3,4]}'); -- {(2@2001-01-02, 3@2001-01-03), (4@2001-01-04, 5@2001-01-05)} SELECT asText(minusValues(tgeompoint '[Point(0 0 0)@2001-01-01, Point(2 2 2)@2001-01-03)', geometry 'Point(1 1 1)')); /* {[POINT Z (0 0 0)@2001-01-01, POINT Z (1 1 1)@2001-01-02), (POINT Z (1 1 1)@2001-01-02, POINT Z (2 2 2)@2001-01-03)} */ SELECT asText(minusValues(tgeompoint '[Point(0 0 0)@2001-01-01, Point(3 3 3)@2001-01-04)', geomset '{"Point(1 1 1)', 'Point(2 2 2)"}')); /* {[POINT Z (0 0 0)@2001-01-01, POINT Z (1 1 1)@2001-01-02), (POINT Z (1 1 1)@2001-01-02, POINT Z (2 2 2)@2001-01-03), (POINT Z (2 2 2)@2001-01-03, POINT Z (3 3 3)@2001-01-04)} */ SELECT asText(minusValues(tgeometry '[Point(0 0)@2001-01-01, Linestring(0 0,2 2)@2001-01-02, Linestring(0 0,2 2)@2001-01-03]', geometry 'Linestring(0 0,2 2)')); -- {[POINT(0 0)@2001-01-01, POINT(0 0)@2001-01-02)}
Restrict to (the complement of) a time value
atTime(ttype,times) → ttype
minusTime(ttype,times) → ttype
SELECT atTime(tfloat '[1@2001-01-01, 5@2001-01-05)', timestamptz '2001-01-02'); -- 2@2001-01-02 SELECT atTime(tint '[1@2001-01-01, 1@2001-01-15)', tstzset '{2001-01-01, 2001-01-03}'); -- {1@2001-01-01, 1@2001-01-03} SELECT atTime(tfloat '{[1@2001-01-01, 3@2001-01-03), [3@2001-01-04, 1@2001-01-06)}', tstzspan '[2001-01-02,2001-01-05)'); -- {[2@2001-01-02, 3@2001-01-03), [3@2001-01-04, 2@2001-01-05)} SELECT atTime(tint '[1@2001-01-01, 1@2001-01-15)', tstzspanset '{[2001-01-01, 2001-01-03), [2001-01-04, 2001-01-05)}'); -- {[1@2001-01-01, 1@2001-01-03),[1@2001-01-04, 1@2001-01-05)}
SELECT minusTime(tfloat '[1@2001-01-01, 5@2001-01-05)', timestamptz '2001-01-02'); -- {[1@2001-01-01, 2@2001-01-02), (2@2001-01-02, 5@2001-01-05)} SELECT minusTime(tint '[1@2001-01-01, 1@2001-01-15)', tstzset '{2001-01-02, 2001-01-03}'); /* {[1@2001-01-01, 1@2001-01-02), (1@2001-01-02, 1@2001-01-03), (1@2001-01-03, 1@2001-01-15)} */ SELECT minusTime(tfloat '{[1@2001-01-01, 3@2001-01-03), [3@2001-01-04, 1@2001-01-06)}', tstzspan '[2001-01-02,2001-01-05)'); -- {[1@2001-01-01, 2@2001-01-02), [2@2001-01-05, 1@2001-01-06)} SELECT minusTime(tint '[1@2001-01-01, 1@2001-01-15)', tstzspanset '{[2001-01-02, 2001-01-03), [2001-01-04, 2001-01-05)}'); /* {[1@2001-01-01, 1@2001-01-02), [1@2001-01-03, 1@2001-01-04), [1@2001-01-05, 1@2001-01-15)} */