The SQL:2011 standard added system-versioned tables and application-time periods. What do those features give you compared with a hand-rolled history table, and where do they fall short?

## Two periods, two meanings SQL:2011 formalised what temporal-database research had long distinguished: - **System time (transaction time)** — the interval during which the database *held* this row version. Set by the engine, never by the application, and by definition never in the future. - **Application time (valid time)** — the interval during which the fact is *true in the business*. Set by the application; may be in the past (a backdated correction) or the future (a scheduled price change). The standard supports both, independently. A table with only system time gives you an automatic audit of database state; a table with only application time gives you effective dating; a table with both is *bitemporal*. ## System-versioned tables You declare two columns generated by the engine and mark the table versioned. In standard terms: ``` CREATE TABLE employee ( id INT PRIMARY KEY, salary NUMERIC(12,2), row_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START, row_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (row_start, row_end) ) WITH SYSTEM VERSIONING; ``` From then on, `UPDATE` and `DELETE` do not destroy anything: the engine closes the current version's `row_end` and retains it (in a separate history table in SQL Server and DB2, in the same table in MariaDB), inserting the new version with a fresh `row_start`. Ordinary queries see only current rows, so existing application SQL is unaffected. Time travel uses period predicates: ``` SELECT * FROM employee FOR SYSTEM_TIME AS OF TIMESTAMP '2024-03-01 00:00:00'; SELECT * FROM employee FOR SYSTEM_TIME BETWEEN :a AND :b; SELECT * FROM employee FOR SYSTEM_TIME ALL; ``` What this buys over a hand-rolled trigger-based history table: - **Completeness.** No writer can bypass it — not a migration script, not a DBA fixing data by hand, not a bulk load. Triggers can be disabled and application code can be bypassed; system versioning is part of the table. - **Correctness.** Versions are stamped with the transaction's timestamp and are transactionally consistent by construction, with no race between the change and the record of it. - **Ergonomics.** `AS OF` is dramatically simpler than the equivalent hand-written correlated query, and the optimizer understands it. - **Less code.** No triggers, no history DDL to maintain by hand, no test surface for "did we remember to audit this column". ## Application-time periods The second half of the standard applies to business validity: ``` CREATE TABLE product_price ( product_id INT, price NUMERIC(12,2), valid_from DATE, valid_to DATE, PERIOD FOR business_time (valid_from, valid_to), PRIMARY KEY (product_id, business_time WITHOUT OVERLAPS) ); UPDATE product_price FOR PORTION OF business_time FROM DATE '2024-03-01' TO DATE '2024-04-01' SET price = 21.00 WHERE product_id = 42; ``` Two things are notable. `WITHOUT OVERLAPS` makes the engine reject overlapping periods for the same entity — the constraint that hand-rolled effective dating most often lacks. And `FOR PORTION OF` performs the row-splitting that application code normally has to implement: if the existing period is wider than the portion being changed, the engine automatically closes and re-opens the surrounding fragments. Periods here are half-open, matching the convention you would choose anyway. ## Where it falls short **Availability.** This is the big one. SQL Server (2016+, "temporal tables"), MariaDB (10.3+), DB2 and Oracle (Flashback Data Archive plus valid-time support) implement meaningful subsets. PostgreSQL has no system versioning — the usual answer there is an extension or a trigger-based history table, with application-time period features arriving only gradually in recent releases. MySQL has neither. So the honest interview answer names the standard, then names what your engine actually does. **No actor.** System versioning records *when*, never *who*. Compliance auditing almost always needs the application user, so you end up adding a `changed_by` column that the application must populate — which re-introduces the very bypass problem system versioning solved, since a direct SQL update will leave it stale or null. **System time is not truth time.** A salary entered on the 10th, effective the 1st, appears in system time as a change on the 10th. Answering "what was the salary on the 1st" correctly needs application time. Conversely, application time alone cannot tell you what a report run last month would have said. That is the argument for bitemporal design. **Operational weight.** History still grows without bound and still needs retention, partitioning and archival — most implementations give you tools for this, but it is not automatic. Schema changes to a versioned table need care, since the history must accommodate old and new shapes. Some implementations restrict DDL, `TRUNCATE`, partitioning or replication interactions on versioned tables. And history rows are usually protected from modification, which is a feature for auditors and an obstacle when you must genuinely erase personal data. **Granularity.** Versioning is per row, not per column, so a change to one column rewrites the whole row into history — the same storage profile as a snapshot-style history table. ## How to use this in an answer Say that the standard separates system time from application time, that system versioning gives complete engine-enforced history that no writer can bypass, that application-time periods give non-overlapping effective dating plus automatic row splitting, and then be specific about your engine's support and about the two gaps that always remain: identity of the actor, and history retention.