Updating the past

In part 2, we queried the historical timeline, to understand what changes were made.

In this part, we will understand how to insert historical data into XTDB.

How does this work with an immutable database!? Let’s find out together.

Let’s pretend the day today is 2024-01-01, and we insert a product:

2024-01-01

Run

Open in xt-fiddle

Let’s query the day after this insert:

Run

Open in xt-fiddle

Now, let’s query against the past, in 2023

We should NOT see any data, because the product was inserted into the database on 2024-01-01:

Run

Open in xt-fiddle

Inserting historical data

But let’s say, we want to insert some historical data into our database, all the way back in 2022.

This could be an import of historical product prices from another system into our XTDB golden store.

We achieve this in XT by setting the xt$valid_from and xt$valid_to column

2024-01-01

Run

Open in xt-fiddle

Now if we query in 2024, we still get the 2024 value

Run

Open in xt-fiddle

But if we query in 2023, we should see the older 2022 value:

Run

Open in xt-fiddle

If we query in 2020, we should see nothing:

Run

Open in xt-fiddle

We’ve shown that it’s possible to insert records into the past.

What about if we want to update historical data? How does this work with an immutable database?

Let’s find out in part 4