Skip to content
XTDB

Control the timeline

Everything demonstrated so far only scratches the surface of what XTDB can do, given that XTDB is a full SQL implementation with all the implications that has, however there is one further aspect where XTDB is very different to most databases: ubiquitous 'Valid-Time' versioning.

Valid-Time is for advanced time-travel

In addition to system-time versioning, SQL:2011 also defines 'application-time' versioning. XTDB applies this versioning to all tables and refers to it as valid-time.

Valid-time is a key tool for developers who need to offer time-travel functionality within their applications. It is a rigourously defined model that can help avoid cluttering schemas and queries with bespoke updated_at, deleted_at and effective_from columns (…​and all the various TRIGGERs that typically live alongside those).

Developers who try to build useful functionality on top of system-time directly will likely encounter issues with migrations, backfill, and out-of-order ingestion. Valid-time solves these challenges head-on whilst also enabling other advanced usage scenarios:

  1. corrections - curate a timeline of versions with an ability to correct data - an essential capability for applications where recording the full context behind critical decisions is needed

  2. global scheduling - control exactly when data is visible to as-of-now queries by loading data with future valid-time timestamps, without needing to complicate your schema or queries - data will 'appear' and 'disappear' automatically as wall-clock time progresses

Note that valid-time as provided by XTDB is specifically about the validity (or "effective from" time) of a given row in the table, and not necessarily (unless you carefully map it 1:1) some other domain conception of time.

INSERT into the past

We can specify the xt$valid_from column during an INSERT statement to record when the organization (i.e. thinking beyond this particular database!) first became aware of carol:

INSERT INTO people (xt$id, name, favorite_color, xt$valid_from)
  VALUES (2, 'carol', 'blue', DATE '2024-01-01');

What did you know?

We can now show that we knew carol existed in the company records before this database was even created:

SELECT * FROM people FOR VALID_TIME AS OF DATE '2024-02-01';

When did you know it?

The 'bitemporal' combination of valid-time and system-time columns allows us to readily prove an auditable history about what we claimed to have known in the past:

SELECT people.xt$id, people.xt$valid_from, people.xt$system_from
  FROM people FOR ALL VALID_TIME FOR ALL SYSTEM_TIME;

Next steps!

You have now learned the essentials of using XTDB!

Looking for more? Please have a browse around the docs, try building something, and feel very welcome to say hello 👋