Use SQL hints to configure and query data versions by timestamp - Lindorm

LindormTable stores a timestamp alongside every value written to a non-primary key column. By default, that timestamp is the server time at write and doubles as the version ID — a larger timestamp means a newer version. Using SQL hints, you can override this timestamp at write time and filter by version at query time. This makes hints the building block for point-in-time queries, audit trails, and historical data retrieval in wide-column tables.

Applicable engines and versions

Hints for data versioning require LindormTable 2.3.1 or later.

To check or upgrade your LindormTable version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

How it works

Each column in LindormTable stores data as versioned cells. When you write a value, LindormTable records the server time as the cell's timestamp, which serves as its version ID. Higher timestamps represent newer versions.

To store more than one version per column, set VERSIONS='n' when creating the table, where n is the maximum number of versions to retain. You can also change this limit later with ALTER TABLE.

At query time, use hints in your SELECT statements to retrieve specific versions or version ranges. To display the timestamp alongside a column value in query results, append _l_ts to the column name (for example, c2_l_ts).

The following hints are available:

Hint	Description	Applicable statement
`_l_ts_(N)`	Reads or writes data at the specified timestamp N	UPSERT and SELECT
`_l_versions_(N)`	Returns the latest N versions	SELECT
`_l_ts_min_(N)`	Returns versions with timestamps larger than N	SELECT
`_l_ts_max_(N)`	Returns versions with timestamps smaller than N	SELECT

Important

If you include a {column}_l_ts suffix in a SELECT statement without also specifying a version hint (such as _l_versions_(1) or _l_ts_min_(N)), the timestamp column returns null in the result.

Write data with custom timestamps

Use _l_ts_(N) in UPSERT statements to assign a specific timestamp to each write. The hint applies to all columns in that statement.

All examples on this page use the following test table, which retains up to five versions per column:

CREATE TABLE t_test_versions_2 (c1 INT, c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1)) WITH(VERSIONS='5');

To change the version limit on an existing table, run ALTER TABLE table_name SET 'VERSIONS' = 'num';, where table_name is the table name and num is the new limit.

Insert data at specific timestamps:

UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c3) values (1, '11');
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c3) values (1, '22');
UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c2) values (1, 1);
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c2) values (2, 1);
UPSERT /*+ _l_ts_(2002)  */ INTO t_test_versions_2(c1, c2) values (2, 2);
UPSERT /*+ _l_ts_(2003)  */ INTO t_test_versions_2(c1, c2) values (2, 3);
UPSERT /*+ _l_ts_(2004)  */ INTO t_test_versions_2(c1, c2) values (2, 4);
UPSERT /*+ _l_ts_(2005)  */ INTO t_test_versions_2(c1, c2) values (2, 5);
UPSERT /*+ _l_ts_(2006)  */ INTO t_test_versions_2(c1, c2) values (2, 6);

Query versioned data

All query examples use the table and data created above.

Query data at a specific timestamp

Use _l_ts_(N) to retrieve the version written at exactly timestamp N.

SELECT /*+ _l_ts_(1000) */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;

Result:

+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 11 | 1000    |
+----+----+---------+

Query data within a timestamp range

Combine _l_ts_min_(N) and _l_ts_max_(N) to select versions within a timestamp range. In the following example, _l_ts_min_(1000) and _l_ts_max_(2001) together filter the range [1000, 2001).

SELECT /*+ _l_ts_min_(1000), _l_ts_max_(2001)  */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;

Result:

+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 11 | 1000    |
+----+----+---------+

Query the latest N versions

Use _l_versions_(N) to return the most recent N versions, ordered from newest to oldest.

Example: latest 1 version

SELECT /*+ _l_versions_(1) */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;

Result:

+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 22 | 2001    |
+----+----+---------+

Example: latest 2 versions across all columns

When querying multiple columns with _l_versions_, each row in the result represents one version of one column. Columns that share the same timestamp appear in the same row; columns with no data at that version appear as null.

SELECT /*+ _l_versions_(2)  */ c1, c2, c3, c2_l_ts, c3_l_ts FROM t_test_versions_2;

Result:

+----+------+------+---------+---------+
| c1 |  c2  |  c3  | c2_l_ts | c3_l_ts |
+----+------+------+---------+---------+
| 1  | null | 22   | null    | 2001    |
| 1  | 1    | 11   | 1000    | 1000    |
| 2  | 6    | null | 2006    | null    |
| 2  | 5    | null | 2005    | null    |
+----+------+------+---------+---------+

Example: requesting more versions than the table limit

If _l_versions_(N) requests more versions than the table's VERSIONS setting allows, the result is capped at the table limit. The table was created with VERSIONS='5', so requesting six versions returns five.

SELECT /*+ _l_versions_(6)  */ c1, c2, c2_l_ts FROM t_test_versions_2 WHERE c1=2;

Result:

+----+----+---------+
| c1 | c2 | c2_l_ts |
+----+----+---------+
| 2  | 6  | 2006    |
| 2  | 5  | 2005    |
| 2  | 4  | 2004    |
| 2  | 3  | 2003    |
| 2  | 2  | 2002    |
+----+----+---------+