5.5. Modification Functions

5.5. Modification Functions
Prev	Chapter 5. Manipulating Temporal Types	Next

A temporal value can be transformed to another subtype. An error is raised if the subtypes are incompatible.

Transform a temporal value to another subtype

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

Transform a temporal value with continuous base type from stepwise to linear interpolation

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

Append a temporal instant to a temporal value

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

Merge the temporal values

merge(ttype,ttype): ttype

merge(ttype[]): ttype

The values may share a single timestamp, in that case the temporal values are joined in the result if their value at the common timestamp is the same, otherwise an error is raised.

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

Shift the timespan of the temporal value by an interval

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

Scale the time span of the temporal value to an interval. If the time span of the temporal value is zero (for example, for a temporal instant), the result is the temporal value. The given interval must be strictly greater than zero.

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

Shift and scale the time span of the temporal value to the two intervals. This function combines in a single step the functions shift and 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]}"

Prev	Up	Next
5.4. Accessor Functions	Home	5.6. Restriction Functions