Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26044

FOR SYSTEM_TIME HISTORY syntax extension

    XMLWordPrintable

    Details

      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).

        Attachments

          Activity

            People

            Assignee:
            midenok Aleksey Midenkov
            Reporter:
            midenok Aleksey Midenkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.