Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
If you find this useful please vote.
FOR SYSTEM_TIME HISTORY is similar to FOR SYSTEM_TIME ALL except the current data is not included.
Currently there is no obvious way to select all history. The workarounds are:
SELECT *, row_start, row_end FOR SYSTEM_TIME ALL WHERE row_end < '2038-01-01';
or even
set @@session.time_zone='+00:00';
set @MAX_TIMESTAMP= TIMESTAMP'2038-01-19 03:14:07.999999';
SELECT *, row_start, row_end FROM t1 FOR SYSTEM_TIME ALL WHERE row_end < @MAX_TIMESTAMP;
Which is non-friendly because it depends on some constant knowledge which is about to change in the future.
The another way is
SELECT *, row_start, row_end FROM t1 FOR SYSTEM_TIME ALL WHERE row_end < NOW(6);
Which is somehow non-unversal as it doesn't work correctly with modified current_timestamp. This is not user-friendly as well because of the need to type additional WHERE-condition (row_end is the arbitrary name specific to the table).
The suggested way for doing that is:
SELECT *, row_start, row_end FROM t1 FOR SYSTEM_TIME HISTORY;
The use-case for such kind of query might be to search whole history for the certain criteria (by specifying WHERE conditions, etc).