Time Travel — Bitemporal Queries

“What was true back then?” and “What did we know back then?” are different questions. Learn the two time axes, and ask the same Cypher again with only the point in time changed.

Why you need two times#

Say HR recorded Haneul Kim's salary like this:

1. 4,000 from January 2021 — she joins the company.
2. 4,600 from January 2024 — a raise. Her actual salary changed (a change).
3. A few months later, HR finds that the 2024 raise was actually to 4,700 and fixes it. Her actual salary didn't change; the recorded number was wrong (a correction).

Now an auditor asks: “What was her salary in June 2025?” The answer is 4,700. But if they ask, “Why did the report written before the correction say 4,600?”, you must be able to reproduce the value we knew at that time.

Data that has both axes is called bitemporal.

Same query, different point in time#

In Ontological DB, you set the point in time separately from the query statement. You keep the Cypher as it is and change only “as of when” you look.

• The same point in time applies to relationships too. If you ask as of 2022, the query follows only the relationships that existed in 2022.
• When you ask about the present, only current rows are read; records from before a correction are searched only when you set a point in time. So everyday queries don't slow down even with a lot of history.

The time travel bar in the portal#

Click the clock button (⏱) to the right of the portal's input box to open the TIME TRAVEL bar.

When you set a point in time, the input box border turns yellow, and the result card is marked with the point in time, such as ⏱ valid 2022-06-01. To turn it off, click clear.

Try it (advanced, optional) — build a salary history#

In the current version, turning on time versioning and writing data with a “valid from” date is done in an SQL session. If you have used psql in a database course, give it a try. (These steps are for macOS. You can skip this section and still continue with the next chapter.)

1. Find the connection details. In the app's data folder (~/Library/Application Support/Ontological Studio/), look up the pg port in ports.json and pgPassword in secrets.json. The user is og and the database is ogstudio.

APP="/Applications/Ontological Studio.app/Contents/Resources/runtime/pg/bin/psql"
DATA="$HOME/Library/Application Support/Ontological Studio"
PORT=$(python3 -c "import json;print(json.load(open('$DATA/ports.json'))['pg'])")
export PGPASSWORD=$(python3 -c "import json;print(json.load(open('$DATA/secrets.json'))['pgPassword'])")
"$APP" -h 127.0.0.1 -p $PORT -U og -d ogstudio

2. Create a graph and a type, and turn on time versioning.

SELECT og_create_graph('hr');
SELECT og_create_type('hr', 'Employee', 'entity');
SELECT og_add_property('hr', 'Employee', 'name', 'string'),
       og_add_property('hr', 'Employee', 'salary', 'int');
SELECT og_enable_temporal('hr', 'Employee');

3. Write three records. SET LOCAL og.valid_from says “from when this is true”. It disappears when the transaction ends, so set it for each record.

BEGIN;  -- joined in 2021
SET LOCAL og.valid_from = '2021-01-01';
SELECT og_cypher('hr', $$CREATE (:Employee {name: 'Haneul Kim', salary: 4000})$$);
COMMIT;

BEGIN;  -- raise from 2024 (change)
SET LOCAL og.valid_from = '2024-01-01';
SELECT og_cypher('hr', $$MATCH (e:Employee {name: 'Haneul Kim'}) SET e.salary = 4600$$);
COMMIT;

BEGIN;  -- it was 4700, not 4600 (correction)
SET LOCAL og.valid_from = '-infinity';
SELECT og_cypher('hr', $$MATCH (e:Employee {name: 'Haneul Kim'}) SET e.salary = 4700$$);
COMMIT;

The '-infinity' in the correction is the key. It means “keep the valid period (from 2024) as it is and fix only the value”. If you set it to '2024-01-01', it becomes another change instead.

4. Ask in the portal. Refresh the portal and switch the graph to hr. The query is always the same:

MATCH (e:Employee) RETURN e.name, e.salary

The fourth result shows the strength of bitemporal data. In an ordinary database that overwrites values, you can't go back and see “why we decided that way back then”.

The schema keeps history too#

Not only data but also the schema keeps history. A schema change, such as changing a property's type, also goes through plan → impact review → staged execution → validation → completion, and leaves a history. If even one value can't be converted, the change doesn't complete. You can plan a change and see its progress and history in the portal's schema designer.

For more, see the Ontological DB introduction.