Skip to main content

Time Travel

The LIFETIME keyword modifies a data source in a FROM clause and specifies that the entire record history will be accessed by the query.

bSQL Syntax Conventions

Syntax

FROM { [LIFETIME] <blockchain_reference> } [ <join_expression> [,...n] ]

Arguments

LIFETIME
Specifies that we are querying from the history of the data source.

blockchain_name
Is the name of a blockchain.

If the blockchain exists in another database, use a fully qualified name in the form database.object_name.

Conventions

  • Because containers are append-only, all records changed using the AMEND statement persist in the record history.

  • The lifetime of a discontinued record still persists in the container. The record is marked as discontinued by inserting a tombstone record whose primary key and timestamp column (if one was specified) remain unchanged, all other fields are evaluated to NULL.

Permissions

READ permissions required on the target blockchain(s).

READ permissions on all blockchains default to members of the admin fixed database role. READ permissions on user-defined blockchains default to the developer and reader fixed database roles. Members of the admin role can transfer permissions to other users.

Examples

The following examples use the DEMO DATABASE.

A. Using a simple LIFETIME keyword

The following example retrieves the entire record history from the pricing blockchain.

SELECT symbol, price, timestamp
FROM LIFETIME financial.pricing;
SYMBOL [STRING] PRICE [FLOAT32] TIMESTAMP [TIMESTAMP]   
A 58.42 2020-11-13 07:23:01.763922200
A 56.822758 2020-11-13 07:23:03.988756600
A 57.76047 2020-11-13 07:23:13.637172800
A 61.788437 2020-11-13 07:23:22.644295100
A 59.92426 2020-11-13 07:23:31.631817800
A 55.595646 2020-11-13 07:23:40.582445100
A 57.642273 2020-11-13 07:23:49.624436200
A 57.779324 2020-11-13 07:23:58.704664500
A 60.32219 2020-11-13 07:24:07.735123800
A 59.924088 2020-11-13 07:24:16.844482000
A 59.48452 2020-11-13 07:24:26.185206500
A 62.3104 2020-11-13 07:24:35.240699900
A 63.205826 2020-11-13 07:24:44.230546700
A 59.454163 2020-11-13 07:24:53.232524000
A 58.21827 2020-11-13 07:25:02.316335300
A 54.284306 2020-11-13 07:25:11.307512700
A 56.288406 2020-11-13 07:25:20.353887000
A 60.679165 2020-11-13 07:25:29.372656100
A 62.867332 2020-11-13 07:25:38.376930700
A 62.511772 2020-11-13 07:25:47.394033600
A 59.711445 2020-11-13 07:25:56.410734700
A NULL 2020-11-13 07:26:03.650678200
AAPL 530.44 2020-11-13 07:23:01.763922200
AAPL 528.8428 2020-11-13 07:23:04.014760900
AAPL 529.7805 2020-11-13 07:23:13.655172400
AAPL 533.8085 2020-11-13 07:23:22.662292800
AAPL 531.9443 2020-11-13 07:23:31.649848600
AAPL 527.61566 2020-11-13 07:23:40.600445300
AAPL 529.6623 2020-11-13 07:23:49.643431400
.
.
.

B. Using the LIFETIME with DISCONTINUED

When records are DISCONTINUED from a container there lifetimes still persist. Here we can access the tombstone records to see when these records were discontinued.

SELECT * 
FROM LIFETIME financial.pricing
WHERE DISCONTINUED(pricing);
SYMBOL [STRING] PRICE [FLOAT32] DIVIDEND_YIELD [FLOAT32]    PRICE_EARNING [FLOAT32] EARNING_SHARE [FLOAT32] BOOK_VALUE [FLOAT32]    52_WEEK_LOW [FLOAT32]   52_WEEK_HIGH [FLOAT32]  TIMESTAMP [TIMESTAMP]   
A NULL NULL NULL NULL NULL NULL NULL 2020-11-13 07:26:03.650678200
MMM NULL NULL NULL NULL NULL NULL NULL 2020-11-13 07:26:03.670677500

E. Joins with LIFETIME

The following example joins the current state of companies with all states of pricing on symbol. We also group by name, and retrieve the number of versions of each record, as well as the average price across all versions.

SELECT c.name, COUNT(*) AS number_of_versions, AVG(p.price)
FROM LIFETIME financial.pricing AS p JOIN financial.companies AS c ON c.symbol = p.symbol
GROUP BY name
FILTER 10;
C.NAME [STRING] NUMBER_OF_VERSIONS [UINT64] AVG(P.PRICE) [FLOAT64]  
3M Co. 22 133.92297224564985
ACE Limited 21 103.51996685209728
AES Corp 21 18.495395614987327
AFLAC Inc 21 70.05538577125186
AGL Resources Inc. 21 56.701499938964844
AMETEK Inc 21 59.99528685070219
AT&T Inc 21 25.695445378621418
AbbVie Inc. 21 57.20997020176479
Abbott Laboratories 21 45.31997081211635
Accenture 21 89.67997051420666

See Also