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​