Hindsight 20/20: Querying Any Point in Time

Today I'll be showing you how to use transactions to simulate snapshots in bSQL. Because of its multi-version protocols users can access old data by running queries with a set query time. By setting a query time all subsequent queries run as if they had been run at that time. This allows for analysis of old versions of the database, giving you rich data insights. The following diagram illustrates snapshotting in traditional databases and setting the query time with blockpoint's immutable database.

Syntax

SET TRANSACTION 
    QUERY TIME <query_time>

<query_time> ::=
    timestamp
    | STATE

QUERY TIME
Sets queries to run in a snapshot-like environment at the time specified. Values that were AMENDED or DISCONTINUED after the specified time will be restored and relational entities are guaranteed to hold.

[!NOTE]
The query time is currently specified in UTC time format.

STATE
Specifies that queries should operate normally, on the current state of the system.

• Limitations
  • LIFETIME queries and QUERY TIME are mutually exclusive. An error is thrown if a lifetime query is run on any version other than STATE.
  • Queryies run on a previous database version always use the READ UNCOMMITTED isolation level.

timestamp
The time to read from. timestamp can be specified as a timestamp in quotations or an expression that computes to a valid timestamp.

Setting the query time.

We can use the QUERY TIME keyword to simulation a snapshot at the time specified. Because the system continuously tracks changes setting the transaction time should have only a small effect on performance. Although all the statements below were technically committed, we can still view previous states.

CREATE BLOCKCHAIN users TRADITIONAL (id UINT64 AUTO INCREMENT PRIMARY, name STRING PACKED);

INSERT users (name) VALUES ("john"), ("jimmy"), ("jeff");

--Assume Time = "2021-02-26 00:07:10.000000000"

DISCONTINUE users (id, name) VALUES (1, "jimmy");

SET TRANSACTION QUERY TIME "2021-02-26 00:07:10.000000000";

SELECT * FROM users;

OUTPUT

Since "jimmy" was inserted into the blockchain before the specified transaction time. The record persists in that state of the database.

ID	NAME
0	john
1	jimmy
2	jeff

Conclusion

Although this example is trivial, the same logic is used for audits and data analysis. It uses the database structure in a unique way to give users access to data evolution over time.