Table of Contents
We explain next the semantics of the modification operations (that is, insert, update, and delete) for temporal types. These operations have similar semantics as the corresponding operations for application-time temporal tables introduced in the SQL:2011 standard. The main difference is that SQL uses tuple timestamping (where timestamps are attached to tuples), while temporal values in MobilityDB use attribute timestamping (where timestamps are attached to attribute values).
The insert operation adds to a temporal value the instants of another one without modifying the existing instants, as illustrated in Figure 6.1, “Insert operation for temporal values.”.
As shown in the figure, the temporal values may only intersect at their boundary, and in that case, they must have the same base value at their common timestamps, otherwise an error is raised. The result of the operation is the union of the instants for both temporal values, as shown in the first result of the figure. This is equivalent to a merge operation explained below. Alternatively, as shown in the second result of the figure, the inserted fragments that are disjoint with the original value are connected to the last instant before and the first instant after the fragment. A Boolean parameter connect is used to choose between the two results, and the parameter is set to true by default. Notice that this only applies to continuous temporal values.
The update operation replaces the instants in the first temporal value with those of the second one as illustrated in Figure 6.2, “Update and delete operation for temporal values.”.
As in the case of an insert operation, an additional Boolean parameter determines whether the replaced disconnected fragments are connected in the resulting value, as shown in the two possible results in the figure. When the two temporal values are either disjoint or only overlap at their boundary, this corresponds to an insert operation as explained above. In this case, the update operation behaves as an upsert operation in SQL.
The deleteTime operation removes the instants of a temporal value that intersect a time value. This operation can be used in two different situations, illustrated in Figure 6.2, “Update and delete operation for temporal values.”.
In the first case, shown as the top result in the figure, the meaning of operation is to introduce time gaps after removing the instants of the temporal value intersecting the time value. This is equivalent to the restriction operations (the section called “Restrictions”), which restrict a temporal value to the complement of the time value.
The second case, shown as the bottom result in the figure, is used for removing erroneous values (e.g., detected as outliers) without introducing a time gap, or for removing time gaps. In this case, the instants of the temporal value are deleted and the last instant before and the first instant after a removed fragment are connected. This behaviour is specified by setting an additional Boolean parameter of the operation. Notice that this only applies to continuous temporal values.
Figure 6.3, “Modification operations for temporal tables in SQL” shows the equivalent modification operations for temporal tables in the SQL standard. Intuitively, these figures are obtained by rotating 90 degrees clockwise the corresponding figures for temporal values (Figure 6.1, “Insert operation for temporal values.” and Figure 6.2, “Update and delete operation for temporal values.”). This follows from the fact that in SQL consecutive tuples ordered by time are typically connected through the LEAD and LAG window functions.
Insert a temporal value into another one
insert(ttype,ttype,connect=true) → ttype
SELECT insert(tint '{1@2001-01-01, 3@2001-01-03, 5@2001-01-05}',
tint '{3@2001-01-03, 7@2001-01-07}');
-- {1@2001-01-01, 3@2001-01-03, 5@2001-01-05, 7@2001-01-07}
SELECT insert(tint '{1@2001-01-01, 3@2001-01-03, 5@2001-01-05}',
tint '{5@2001-01-03, 7@2001-01-07}');
-- ERROR: The temporal values have different value at their overlapping instant 2001-01-03
SELECT insert(tfloat '[1@2001-01-01, 2@2001-01-02]',
tfloat '[1@2001-01-03, 1@2001-01-05]');
-- [1@2001-01-01, 2@2001-01-02, 1@2001-01-03, 1@2001-01-05]
SELECT insert(tfloat '[1@2001-01-01, 2@2001-01-02]',
tfloat '[1@2001-01-03, 1@2001-01-05]', false);
-- {[1@2001-01-01, 2@2001-01-02], [1@2001-01-03, 1@2001-01-05]}
SELECT asText(insert(tgeompoint '{[Point(1 1 1)@2001-01-01, Point(2 2 2)@2001-01-02],
[Point(3 3 3)@2001-01-04],[Point(1 1 1)@2001-01-05]}',
tgeompoint 'Point(1 1 1)@2001-01-03'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02, POINT Z (1 1 1)@2001-01-03,
POINT Z (3 3 3)@2001-01-04], [POINT Z (1 1 1)@2001-01-05]} */
Update a temporal value with another one
update(ttype,ttype,connect=true) → ttype
SELECT update(tint '{1@2001-01-01, 3@2001-01-03, 5@2001-01-05}',
tint '{5@2001-01-03, 7@2001-01-07}');
-- {1@2001-01-01, 5@2001-01-03, 5@2001-01-05, 7@2001-01-07}
SELECT update(tfloat '[1@2001-01-01, 1@2001-01-05]',
tfloat '[1@2001-01-02, 3@2001-01-03, 1@2001-01-04]');
-- {[1@2001-01-01, 1@2001-01-02, 3@2001-01-03, 1@2001-01-04, 1@2001-01-05]}
SELECT asText(update(tgeompoint '{[Point(1 1 1)@2001-01-01, Point(3 3 3)@2001-01-03,
Point(1 1 1)@2001-01-05], [Point(1 1 1)@2001-01-07]}',
tgeompoint '[Point(2 2 2)@2001-01-02, Point(2 2 2)@2001-01-04]'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02, POINT Z (2 2 2)@2001-01-04,
POINT Z (1 1 1)@2001-01-05], [POINT Z (1 1 1)@2001-01-07]} */
Delete the instants of a temporal value that intersect a time value
deleteTime(ttype,time,connect=true) → ttype
SELECT deleteTime(tint '[1@2001-01-01, 1@2001-01-03]', timestamptz '2001-01-02', false);
-- {[1@2001-01-01, 1@2001-01-02), (1@2001-01-02, 1@2001-01-03]}
SELECT deleteTime(tint '[1@2001-01-01, 1@2001-01-03]', timestamptz '2001-01-02');
-- [1@2001-01-01, 1@2001-01-03]
SELECT deleteTime(tfloat '[1@2001-01-01, 4@2001-01-02, 2@2001-01-04, 5@2001-01-05]',
tstzspan '[2001-01-02, 2001-01-04]');
-- [1@2001-01-01, 5@2001-01-05]
SELECT asText(deleteTime(tgeompoint '{[Point(1 1 1)@2001-01-01,
Point(2 2 2)@2001-01-02], [Point(3 3 3)@2001-01-04, Point(3 3 3)@2001-01-05]}',
tstzspan '[2001-01-02, 2001-01-04]'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02, POINT Z (3 3 3)@2001-01-04,
POINT Z (3 3 3)@2001-01-05]} */
Append a temporal instant to a temporal value
appendInstant(ttype,ttypeInst) → ttype
appendInstant(ttypeInst,maxdist=NULL,maxt=NULL) → ttypeSeq
The first version of the function returns the result of appending the second argument to the first one. If either input is NULL, then NULL is returned.
The second version of the function above is an aggregate function that returns the result of successively appending a set of rows of temporal values. This means that it operates in the same way the SUM() and AVG() functions do and like most aggregates, it also ignores NULL values. Two optional arguments state a maximum distance and a maximum time interval such that a gap is introduced whenever two consecutive instants have a distance or a time gap greater than these values. For temporal points the distance is specified in units of the coordinate system. If one of the arguments are not given, it is not taken into account for determining the gaps.
SELECT appendInstant(tint '1@2001-01-01', tint '1@2001-01-02');
-- {1@2001-01-01, 1@2001-01-02}
SELECT appendInstant(tint '[1@2001-01-01]', tint '1@2001-01-02');
-- [1@2001-01-01, 1@2001-01-02]
SELECT asText(appendInstant(tgeompoint '{[Point(1 1 1)@2001-01-01,
Point(2 2 2)@2001-01-02], [Point(3 3 3)@2001-01-04, Point(3 3 3)@2001-01-05]}',
tgeompoint 'Point(1 1 1)@2001-01-06'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02],
[POINT Z (3 3 3)@2001-01-04, POINT Z (3 3 3)@2001-01-05,
POINT Z (1 1 1)@2001-01-06]} */
WITH temp(inst) AS ( SELECT tfloat '1@2001-01-01' UNION SELECT tfloat '2@2001-01-02' UNION SELECT tfloat '3@2001-01-03' UNION SELECT tfloat '4@2001-01-04' UNION SELECT tfloat '5@2001-01-05' ) SELECT appendInstant(inst ORDER BY inst) FROM temp; -- [1@2001-01-01, 5@2001-01-05] WITH temp(inst) AS ( SELECT tgeogpoint 'Point(1 1)@2001-01-01' UNION SELECT tgeogpoint 'Point(2 2)@2001-01-02' UNION SELECT tgeogpoint 'Point(3 3)@2001-01-03' UNION SELECT tgeogpoint 'Point(4 4)@2001-01-04' UNION SELECT tgeogpoint 'Point(5 5)@2001-01-05' ) SELECT asText(appendInstant(inst ORDER BY inst)) FROM temp; /* [POINT(1 1)@2001-01-01, POINT(2 2)@2001-01-02, POINT(3 3)@2001-01-03, POINT(4 4)@2001-01-04, POINT(5 5)@2001-01-05] */
Notice that in the first query above with tfloat, the intermediate observations were removed by the normalization process since they were redundant due to linear interpolation. This is not the case for the second query with tgeogpoint since geodetic coordinates are used.
WITH temp(inst) AS (
SELECT tfloat '1@2001-01-01' UNION
SELECT tfloat '2@2001-01-02' UNION
SELECT tfloat '4@2001-01-04' UNION
SELECT tfloat '5@2001-01-05' UNION
SELECT tfloat '7@2001-01-07' )
SELECT appendInstant(inst, 0.0, '1 day' ORDER BY inst) FROM temp;
-- {[1@2001-01-01, 2@2001-01-02], [4@2001-01-04, 5@2001-01-05], [7@2001-01-07]}
WITH temp(inst) AS (
SELECT tgeompoint 'Point(1 1)@2001-01-01' UNION
SELECT tgeompoint 'Point(2 2)@2001-01-02' UNION
SELECT tgeompoint 'Point(4 4)@2001-01-04' UNION
SELECT tgeompoint 'Point(5 5)@2001-01-05' UNION
SELECT tgeompoint 'Point(7 7)@2001-01-07' )
SELECT asText(appendInstant(inst, sqrt(2), '1 day' ORDER BY inst)) FROM temp;
/* {[POINT(1 1)@2001-01-01, POINT(2 2)@2001-01-02],
[POINT(4 4)@2001-01-04, POINT(5 5)@2001-01-05], [POINT(7 7)@2001-01-07]} */
Append a temporal sequence to a temporal value
appendSequence(ttype,ttypeSeq) → {ttypeSeq,ttypeSeqSet}
appendSequence(ttypeSeq) → {ttypeSeq,ttypeSeqSet}
The first version of the function returns the result of appending the second argument to the first one. If either input is NULL, then NULL is returned.
The second version of the function above is an aggregate function that returns the result of successively appending a set of rows of temporal values. This means that it operates in the same way the SUM() and AVG() functions do and like most aggregates, it also ignores NULL values.
SELECT appendSequence(tint '1@2001-01-01', tint '{2@2001-01-02, 3@2001-01-03}');
-- {1@2001-01-01, 2@2001-01-02, 3@2001-01-03}
SELECT appendSequence(tint '[1@2001-01-01, 2@2001-01-02]',
tint '[2@2001-01-02, 3@2001-01-03]');
-- [1@2001-01-01, 2@2001-01-02, 3@2001-01-03]
SELECT asText(appendSequence(tgeompoint '{[Point(1 1 1)@2001-01-01,
Point(2 2 2)@2001-01-02], [Point(3 3 3)@2001-01-04, Point(3 3 3)@2001-01-05]}',
tgeompoint '[Point(3 3 3)@2001-01-05, Point(1 1 1)@2001-01-06]'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02],
[POINT Z (3 3 3)@2001-01-04, POINT Z (3 3 3)@2001-01-05,
POINT Z (1 1 1)@2001-01-06]} */
Merge the temporal values
merge(ttype,ttype) → ttype
merge(ttype[]) → ttype
The temporal values may only intersect at their boundary and in that case, their base values at the common timestamps must be the same, otherwise an error is raised.
SELECT merge(tint '1@2001-01-01', tint '1@2001-01-02');
-- {1@2001-01-01, 1@2001-01-02}
SELECT merge(tint '[1@2001-01-01, 2@2001-01-02]', tint '[2@2001-01-02, 1@2001-01-03]');
-- [1@2001-01-01, 2@2001-01-02, 1@2001-01-03]
SELECT merge(tint '[1@2001-01-01, 2@2001-01-02]', tint '[3@2001-01-03, 1@2001-01-04]');
-- {[1@2001-01-01, 2@2001-01-02], [3@2001-01-03, 1@2001-01-04]}
SELECT merge(tint '[1@2001-01-01, 2@2001-01-02]', tint '[1@2001-01-02, 2@2001-01-03]');
-- ERROR: The temporal values have different value at their common timestamp 2001-01-02
SELECT asText(merge(tgeompoint '{[Point(1 1 1)@2001-01-01,
Point(2 2 2)@2001-01-02], [Point(3 3 3)@2001-01-04, Point(3 3 3)@2001-01-05]}',
tgeompoint '{[Point(3 3 3)@2001-01-05, Point(1 1 1)@2001-01-06]}'));
/* {[POINT Z (1 1 1)@2001-01-01, POINT Z (2 2 2)@2001-01-02],
[POINT Z (3 3 3)@2001-01-04, POINT Z (3 3 3)@2001-01-05,
POINT Z (1 1 1)@2001-01-06]} */
SELECT merge(ARRAY[tint '1@2001-01-01', '1@2001-01-02']);
-- {1@2001-01-01, 1@2001-01-02}
SELECT merge(ARRAY[tint '{1@2001-01-01, 2@2001-01-02}', '{2@2001-01-02, 3@2001-01-03}']);
-- {1@2001-01-01, 2@2001-01-02, 3@2001-01-03}
SELECT merge(ARRAY[tint '{1@2001-01-01, 2@2001-01-02}', '{3@2001-01-03, 4@2001-01-04}']);
-- {1@2001-01-01, 2@2001-01-02, 3@2001-01-03, 4@2001-01-04}
SELECT merge(ARRAY[tint '[1@2001-01-01, 2@2001-01-02]', '[2@2001-01-02, 1@2001-01-03]']);
-- [1@2001-01-01, 2@2001-01-02, 1@2001-01-03]
SELECT merge(ARRAY[tint '[1@2001-01-01, 2@2001-01-02]', '[3@2001-01-03, 4@2001-01-04]']);
-- {[1@2001-01-01, 2@2001-01-02], [3@2001-01-03, 4@2001-01-04]}
SELECT asText(merge(ARRAY[tgeompoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02],
[Point(3 3)@2001-01-03, Point(4 4)@2001-01-04]}', '{[Point(4 4)@2001-01-04,
Point(3 3)@2001-01-05], [Point(6 6)@2001-01-06, Point(7 7)@2001-01-07]}']));
/* {[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02], [Point(3 3)@2001-01-03,
Point(4 4)@2001-01-04, Point(3 3)@2001-01-05],
[Point(6 6)@2001-01-06, Point(7 7)@2001-01-07]} */
SELECT asText(merge(ARRAY[tgeompoint '{[Point(1 1)@2001-01-01, Point(2 2)@2001-01-02]}',
'{[Point(2 2)@2001-01-02, Point(1 1)@2001-01-03]}']));
-- [Point(1 1)@2001-01-01, Point(2 2)@2001-01-02, Point(1 1)@2001-01-03]