Hay dos conjuntos complementarios de funciones de restricción. El primer conjunto de funciones restringe el valor temporal con respecto a un valor o una extensión de tiempo. Dos ejemplos son atValues o atTime. El segundo conjunto de funciones restringe el valor temporal con respecto al complement de un valor o una extensión de tiempo. Dos ejemplos son minusValues o minusTime
Restringir a (al complemento de) un valor
atValues(ttype,values) → ttypeminusValues(ttype,values) → ttypeSELECT atValues(tint '[1@2001-01-01, 1@2001-01-15)', 1); -- [1@2001-01-01, 1@2001-01-15) 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 atValues(tfloat '[1@2001-01-01, 4@2001-01-4)', floatset '{1, 3, 5}'); -- {[1@2001-01-01], [3@2001-01-03]} 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 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 minusValue(tint '[1@2001-01-01, 2@2001-01-02, 2@2001-01-03)', 1); -- {[2@2001-01-02, 2@2001-01-03)} SELECT asText(minusValue(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 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 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 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)}Restringir al (al complemento del) valor mínimo
atMin(torder) → torderminusMin(torder) → torderLa función devuelve nulo si el valor mínimo sólo ocurre en límites exclusivos.
SELECT atMin(tint '{1@2001-01-01, 2@2001-01-03, 1@2001-01-05}'); -- {1@2001-01-01, 1@2001-01-05} SELECT atMin(tint '(1@2001-01-01, 3@2001-01-03]'); -- {(1@2001-01-01, 1@2001-01-03)} SELECT atMin(tfloat '(1@2001-01-01, 3@2001-01-03]'); -- NULL SELECT atMin(ttext '{(AA@2001-01-01, AA@2001-01-03), (BB@2001-01-03, AA@2001-01-05]}'); -- {(AA@2001-01-01, AA@2001-01-03), [AA@2001-01-05]}SELECT minusMin(tint '{1@2001-01-01, 2@2001-01-03, 1@2001-01-05}'); -- {2@2001-01-03} SELECT minusMin(tfloat '[1@2001-01-01, 3@2001-01-03]'); -- {(1@2001-01-01, 3@2001-01-03]} SELECT minusMin(tfloat '(1@2001-01-01, 3@2001-01-03)'); -- {(1@2001-01-01, 3@2001-01-03)} SELECT minusMin(tint '{[1@2001-01-01, 1@2001-01-03), (1@2001-01-03, 1@2001-01-05)}'); -- NULLRestringir al (al complemento del) valor máximo
atMax(torder) → torderminusMax(torder) → torderLa función devuelve nulo si el valor máximo sólo ocurre en límites exclusivos.
SELECT atMax(tint '{1@2001-01-01, 2@2001-01-03, 3@2001-01-05}'); -- {3@2001-01-05} SELECT atMax(tfloat '(1@2001-01-01, 3@2001-01-03)'); -- NULL SELECT atMax(tfloat '{(2@2001-01-01, 1@2001-01-03), [2@2001-01-03, 2@2001-01-05)}'); -- {[2@2001-01-03, 2@2001-01-05]} SELECT atMax(ttext '{(AA@2001-01-01, AA@2001-01-03), (BB@2001-01-03, AA@2001-01-05]}'); -- {("BB"@2001-01-03, "BB"@2001-01-05)}SELECT minusMax(tint '{1@2001-01-01, 2@2001-01-03, 3@2001-01-05}'); -- {1@2001-01-01, 2@2001-01-03} SELECT minusMax(tfloat '[1@2001-01-01, 3@2001-01-03]'); -- {[1@2001-01-01, 3@2001-01-03)} SELECT minusMax(tfloat '(1@2001-01-01, 3@2001-01-03)'); -- {(1@2001-01-01, 3@2001-01-03)} SELECT minusMax(tfloat '{[2@2001-01-01, 1@2001-01-03), [2@2001-01-03, 2@2001-01-05)}'); -- {(2@2001-01-01, 1@2001-01-03)} SELECT minusMax(tfloat '{[1@2001-01-01, 3@2001-01-03), (3@2001-01-03, 1@2001-01-05)}'); -- {[1@2001-01-01, 3@2001-01-03), (3@2001-01-03, 1@2001-01-05)}Restringir a (al complemento de) una geometría, un rango de Z y/o un período
atGeometry(tgeompoint,geometry[,zspan]) → tgeompointminusGeometry(tgeompoint,geometry[,zspan]) → tgeompointLa geometría debe estar en 2D y el cálculo con respecto a ella se realiza en 2D. El resultado conserva la dimensión Z del punto temporal, si existe.
SELECT asText(atGeometry(tgeompoint '[Point(0 0)@2001-01-01, Point(3 3)@2001-01-04)', geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))')); -- {"[POINT(1 1)@2001-01-02, POINT(2 2)@2001-01-03]"} SELECT astext(atGeometry(tgeompoint '[Point(0 0 0)@2001-01-01, Point(4 4 4)@2001-01-05]', geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))')); -- {[POINT Z (1 1 1)@2001-01-02, POINT Z (2 2 2)@2001-01-03]} SELECT asText(atGeometry(tgeompoint '[Point(1 1 1)@2001-01-01, Point(3 1 1)@2001-01-03, Point(3 1 3)@2001-01-05]', 'Polygon((2 0,2 2,2 4,4 0,2 0))', '[0,2]')); -- {[POINT Z (2 1 1)@2001-01-02, POINT Z (3 1 1)@2001-01-03, POINT Z (3 1 2)@2001-01-04]}SELECT asText(minusGeometry(tgeompoint '[Point(0 0)@2001-01-01, Point(3 3)@2001-01-04)', geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))')); /* {[POINT(0 0)@2001-01-01, POINT(1 1)@2001-01-02), (POINT(2 2)@2001-01-03, POINT(3 3)@2001-01-04)} */ SELECT astext(minusGeometry(tgeompoint '[Point(0 0 0)@2001-01-01, Point(4 4 4)@2001-01-05]', geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))')); /* {[POINT Z (0 0 0)@2001-01-01, POINT Z (1 1 1)@2001-01-02), (POINT Z (2 2 2)@2001-01-03, POINT Z (4 4 4)@2001-01-05]} */ SELECT asText(minusGeometry(tgeompoint '[Point(1 1 1)@2001-01-01, Point(3 1 1)@2001-01-03, Point(3 1 3)@2001-01-05]', 'Polygon((2 0,2 2,2 4,4 0,2 0))', '[0,2]')); /* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 1 1)@2001-01-02), (POINT Z (3 1 2)@2001-01-04, POINT Z (3 1 3)@2001-01-05]} */Restringir a (al complemento de) un valor de tiempo
atTime(ttype,times) → ttypeminusTime(ttype,times) → ttypeSELECT 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)} */Restringir a (al complemento de) un
tboxatTbox(tnumber,tbox) → tnumberminusTbox(tnumber,tbox) → tnumberSELECT atTbox(tfloat '[0@2001-01-01, 3@2001-01-04)', tbox 'TBOXFLOAT XT((0,2),[2001-01-02, 2001-01-04])'); -- {[1@2001-01-02, 2@2001-01-03]}SELECT minusTbox(tfloat '[1@2001-01-01, 4@2001-01-04)', 'TBOXFLOAT XT((1,4),[2001-01-03, 2001-01-04])'); -- {[1@2001-01-01, 3@2001-01-03)} WITH temp(temp, box) AS ( SELECT tfloat '[1@2001-01-01, 4@2001-01-04)', tbox 'TBOXFLOAT XT((1,2),[2001-01-03, 2001-01-04])' ) SELECT minusValues(minusTime(temp, box::tstzspan), box::floatspan) FROM temp; -- {[1@2001-01-01], [2@2001-01-02, 3@2001-01-03)}Observe que cuando el cuadro delimitador tiene dimensiones de valor y de tiempo, la diferencia se calcula restringiendo el número temporal al cuadro usando la función
atTbox, calculando la extensión de tiempo de esta restricción, y restringiendo el número temporal a la diferencia de la extensión del tiempo. Esto es necesario para garantizar quetnumber = merge(atTbox(tnumber,tbox), minusTbox(tnumber,tbox))(ver la funciónmerge). En otras palabras, mientras que la funciónatTboxrestringe el número temporal con respecto al rango y al período que definen el cuadro delimitador, la funciónminusTboxrestringe el número temporal con respecto al rango o al período. Para obtener la restricción usando una semántica y, deben aplicarse las dos funcionesminusValuesyminusTime.Restringir a (al complemento de) un
stbox
atStbox(tgeompoint,stbox,borderInc=true) → tgeompointminusStbox(tgeompoint,stbox,borderInc=true) → tgeompointEl tercer argumento opcional se utiliza para hacer mosaicos multidimensionales (ver “Mosaicos multidimensionales”) de manera a excluir el borde superior de los mosaicos cuando un valor temporal se divide en varios mosaicos, de modo que todos los fragmentos del punto temporal sean exclusivos.
SELECT asText(atStbox(tgeompoint '[Point(0 0)@2001-01-01, Point(3 3)@2001-01-04)', stbox 'STBOX XT(((0,0),(2,2)),[2001-01-02, 2001-01-04])')); -- {[POINT(1 1)@2001-01-02, POINT(2 2)@2001-01-03]} SELECT asText(atStbox(tgeompoint '[Point(1 1 1)@2001-01-01, Point(3 3 3)@2001-01-03, Point(3 3 2)@2001-01-04, Point(3 3 7)@2001-01-09]', stbox 'STBOX Z((2,2,2),(3,3,3))')); /* {[POINT Z (2 2 2)@2001-01-02, POINT Z (3 3 3)@2001-01-03, POINT Z (3 3 2)@2001-01-04, POINT Z (3 3 3)@2001-01-05]} */SELECT asText(atStbox(tgeompoint '[Point(1 1)@2001-01-01, Point(1 0)@2001-01-02, Point(0 0)@2001-01-03, Point(0 1)@2001-01-04, Point(1 1)@2001-01-05]', stbox 'STBOX X((0,0),(2,2))', false)); /* {Point(1 1)@2001-01-01, Point(1 0)@2001-01-02, Point(0 0)@2001-01-03, Point(0 1)@2001-01-04, Point(1 1)@2001-01-05} */ SELECT asText(atStbox(tgeompoint '[Point(1 1)@2001-01-01, Point(1 2)@2001-01-02, Point(2 2)@2001-01-03, Point(2 1)@2001-01-04, Point(1 1)@2001-01-05]', stbox 'STBOX X((0,0),(2,2))', false)); /* {[POINT(1 1)@2001-01-01, POINT(1 2)@2001-01-02), (POINT(2 1)@2001-01-04, POINT(1 1)@2001-01-05]} */SELECT asText(minusStbox(tgeompoint '[Point(1 1)@2001-01-01, Point(4 4)@2001-01-04)', stbox 'STBOX XT(((1,1),(4,4)),[2001-01-03,2001-01-04])')); -- {[POINT(1 1)@2001-01-01), POINT(3 3)@2001-01-03)} SELECT asText(minusStbox(tgeompoint '[Point(1 1 1)@2001-01-01, Point(3 3 3)@2001-01-03, Point(3 3 2)@2001-01-04, Point(3 3 7)@2001-01-09]', stbox 'STBOX Z((2,2,2),(3,3,3))')); /* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02), (POINT Z (3 3 3)@2001-01-05, POINT Z (3 3 7)@2001-01-09]} */ WITH temp(temp, box) AS ( SELECT tgeompoint '[Point(1 1)@2001-01-01, Point(4 4)@2001-01-04)', stbox 'STBOX XT(((1,1),(2,2)),[2001-01-03,2001-01-04])') SELECT asText(minusGeometry(minusTime(temp, box::tstzspan), box::geometry)) FROM temp; -- {(POINT(2 2)@2001-01-02, POINT(3 3)@2001-01-03)}De manera similar a la función
minusTbox, cuando el cuadro delimitador tiene dimensiones de espacio y tiempo, la funciónminusStboxrestringe el punto temporal con respecto a las extensiones de espacio o de tiempo del cuadro. Para obtener la restricción utilizando una semántica y, deben aplicarse las dos funcionesminusGeometryyminusTime.