5.5. Funciones de transformación

5.5. Funciones de transformación
Anterior	Capítulo 5. Manipulación de tipos temporales	Siguiente

Un valor temporal se puede transformar en otro subtipo. Se genera un error si los subtipos son incompatibles.

Transformar un tipo temporal a otro subtipo

ttype_inst(ttype): ttype_inst

ttype_instset(ttype): ttype_instset

ttype_seq(ttype): ttype_seq

ttype_seqset(ttype): ttype_seqset

SELECT tbool_inst(tbool '{[true@2001-01-01]}');
-- "t@2001-01-01 00:00:00+00"
SELECT tbool_inst(tbool '{[true@2001-01-01, true@2001-01-02]}');
-- ERROR: Cannot transform input to a temporal instant
SELECT tbool_instset(tbool 'true@2001-01-01');
-- "{t@2001-01-01}"
SELECT tint_seq(tint '1@2001-01-01');
-- "[1@2001-01-01]"
SELECT tfloat_seqset(tfloat '2.5@2001-01-01');
-- "{[2.5@2001-01-01]}"
SELECT tfloat_seqset(tfloat '{2.5@2001-01-01, 1.5@2001-01-02, 3.5@2001-01-02}');
-- "{[2.5@2001-01-01],[1.5@2001-01-02],[3.5@2001-01-03]}"

Transformar un valor temporal con tipo de base continuo de interpolación escalonada a lineal

toLinear(ttype): ttype

SELECT toLinear(tfloat 'Interp=Stepwise;[1@2000-01-01, 2@2000-01-02,
  1@2000-01-03, 2@2000-01-04]');
-- "{[1@2000-01-01, 1@2000-01-02), [2@2000-01-02, 2@2000-01-03),
  [1@2000-01-03, 1@2000-01-04), [2@2000-01-04]}"
SELECT asText(toLinear(tgeompoint 'Interp=Stepwise;{[Point(1 1)@2000-01-01,
  Point(2 2)@2000-01-02], [Point(3 3)@2000-01-05, Point(4 4)@2000-01-06]}'));
-- "{[POINT(1 1)@2000-01-01, POINT(1 1)@2000-01-02), [POINT(2 2)@2000-01-02],
  [POINT(3 3)@2000-01-05, POINT(3 3)@2000-01-06), [POINT(4 4)@2000-01-06]}"

Anexar un instante temporal a un valor temporal

appendInstant(ttype,ttype_inst): ttype

SELECT appendInstant(tint '1@2000-01-01', tint '1@2000-01-02');
-- "{1@2000-01-01, 1@2000-01-02}"
SELECT appendInstant(tint_seq(tint '1@2000-01-01'), tint '1@2000-01-02');
-- "[1@2000-01-01, 1@2000-01-02]"
SELECT asText(appendInstant(tgeompoint '{[Point(1 1 1)@2000-01-01,
  Point(2 2 2)@2000-01-02], [Point(3 3 3)@2000-01-04, Point(3 3 3)@2000-01-05]}',
  tgeompoint 'Point(1 1 1)@2000-01-06'));
-- "{[POINT Z (1 1 1)@2000-01-01, POINT Z (2 2 2)@2000-01-02],
  [POINT Z (3 3 3)@2000-01-04, POINT Z (3 3 3)@2000-01-05, POINT Z (1 1 1)@2000-01-06]}"

Fusionar los valores temporales

merge(ttype,ttype): ttype

merge(ttype[]): ttype

Los valores pueden compartir una sola marca de tiempo, en ese caso, los valores temporales se unen en el resultado si su valor en la marca de tiempo común es el mismo; de lo contrario, se genera un error.

SELECT merge(tint '1@2000-01-01', tint '1@2000-01-02');
-- "{1@2000-01-01, 1@2000-01-02}"
SELECT merge(tint '[1@2000-01-01, 2@2000-01-02]', tint '[2@2000-01-02, 1@2000-01-03]');
-- "[1@2000-01-01, 2@2000-01-02, 1@2000-01-03]"
SELECT merge(tint '[1@2000-01-01, 2@2000-01-02]', tint '[3@2000-01-03, 1@2000-01-04]');
-- "{[1@2000-01-01, 2@2000-01-02], [3@2000-01-03, 1@2000-01-04]}"
SELECT merge(tint '[1@2000-01-01, 2@2000-01-02]', tint '[1@2000-01-02, 2@2000-01-03]');
-- ERROR:  Both arguments have different value at their overlapping timestamp
SELECT asText(merge(tgeompoint '{[Point(1 1 1)@2000-01-01,
  Point(2 2 2)@2000-01-02], [Point(3 3 3)@2000-01-04, Point(3 3 3)@2000-01-05]}',
  tgeompoint '{[Point(3 3 3)@2000-01-05, Point(1 1 1)@2000-01-06]}'));
-- "{[POINT Z (1 1 1)@2000-01-01, POINT Z (2 2 2)@2000-01-02],
  [POINT Z (3 3 3)@2000-01-04, POINT Z (3 3 3)@2000-01-05, POINT Z (1 1 1)@2000-01-06]}"

SELECT merge(ARRAY[tint '1@2000-01-01', '1@2000-01-02']);
-- "{1@2000-01-01, 1@2000-01-02}"
SELECT merge(ARRAY[tint '{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}"
SELECT merge(ARRAY[tint '{1@2000-01-01, 2@2000-01-02}', '{3@2000-01-03, 4@2000-01-04}']);
-- "{1@2000-01-01, 2@2000-01-02, 3@2000-01-03, 4@2000-01-04}"
SELECT merge(ARRAY[tint '[1@2000-01-01, 2@2000-01-02]', '[2@2000-01-02, 1@2000-01-03]']);
-- "[1@2000-01-01, 2@2000-01-02, 1@2000-01-03]"
SELECT merge(ARRAY[tint '[1@2000-01-01, 2@2000-01-02]', '[3@2000-01-03, 4@2000-01-04]']);
-- "{[1@2000-01-01, 2@2000-01-02], [3@2000-01-03, 4@2000-01-04]}"
SELECT merge(ARRAY[tgeompoint '{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02],
  [Point(3 3)@2000-01-03, Point(4 4)@2000-01-04]}', '{[Point(4 4)@2000-01-04,
  Point(3 3)@2000-01-05], [Point(6 6)@2000-01-06, Point(7 7)@2000-01-07]}']);
-- "{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02], [Point(3 3)@2000-01-03,
  Point(4 4)@2000-01-04, Point(3 3)@2000-01-05],
  [Point(6 6)@2000-01-06, Point(7 7)@2000-01-07]}"
SELECT merge(ARRAY[tgeompoint '{[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02]}',
  '{[Point(2 2)@2000-01-02, Point(1 1)@2000-01-03]}']);
-- "[Point(1 1)@2000-01-01, Point(2 2)@2000-01-02, Point(1 1)@2000-01-03]"

Desplazar el intervalo de tiempo del valor temporal con un intervalo

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

Escalear el intervalo de tiempo del valor temporal a un intervalo. Si el intervalo de tiempo del valor temporal es cero (por ejemplo, para un instante temporal), el resultado es el valor temporal. El intervalo dado debe ser estrictamente mayor que cero.

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

Desplazar y escalear el intervalo de tiempo del valor temporal a los dos intervalos. Esta función combina en un solo paso las funciones shift y 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]}"

Anterior	Subir	Siguiente
5.4. Funciones de accesor	Inicio	5.6. Funciones de restricción