Temporal Databases: SQL:2011 Standards and Implementations in DB2, SQL Server, MariaDB

1. Temporal Concepts Overview

Temporal data captures information that changes over time, such as weather conditions or work schedules. In temporal databases, time is categorized into three dimensions: user‑defined time, valid time (the period during which data is true in the real world), and transaction time (the period during which the database records a change).

User‑Defined Time is defined and interpreted by applications, e.g., "20180101" for January 1, 2018. The database stores the value without interpreting it.

Valid Time (also called application or business time) denotes the period when a fact is true in reality. It can represent past, present, or future intervals, e.g., "2014‑2018" for an undergraduate study period.

Transaction Time (system time) is managed by the database, recording when a row was inserted, updated, or deleted. It is read‑only for users and can only represent past moments.

Temporal databases are classified into three types based on the time dimensions they support:

Historical Database – stores valid time.

Rollback Database – stores transaction time.

Bitemporal Database – stores both valid and transaction time.

2. SQL:2011 Temporal Standard

The SQL:2011 standard introduces temporal extensions for both valid and transaction time. It defines a PERIOD syntax to declare a pair of DATE/TIMESTAMP columns representing a time interval, typically using a closed‑open model (start inclusive, end exclusive).

Valid‑Time Operations include creation, INSERT, UPDATE/DELETE, and SELECT. Creation requires two date columns and a named period (e.g., EPeriod). INSERT follows the regular syntax; UPDATE/DELETE can target specific portions of the period using the FOR PORTION OF clause. SELECT supports predicates such as CONTAINS, OVERLAPS, EQUALS, PRECEDES, and SUCCEEDS. The standard also defines a uniqueness constraint on the period to ensure a single true state at any moment.

Transaction‑Time Operations require two generated columns (row start and row end) and a named period SYSTEM_TIME. INSERT automatically assigns the current timestamp to the start column and a maximum value to the end column. UPDATE and DELETE generate a new row with an updated end timestamp, preserving the old version as a history row. SELECT can query a specific point in time ( FOR SYSTEM_TIME AS OF) or a range ( FOR SYSTEM_TIME FROM … TO …, BETWEEN … AND …).

3. DB2: Temporal Data Management

DB2 implements bitemporal tables by separating current and history data into two physical tables. The user table defines an APPLICATION_TIME period (named BUSINESS_TIME) and an additional TRANSACTION START ID to track version provenance. A matching history table is created manually, and a VERSIONING relationship links them, allowing automatic archiving of historical rows while keeping the history table hidden from users.

Typical operations:

CREATE – two tables (current and history) with matching schemas; the current table includes the business‑time period.

INSERT – writes only to the current table; the history table remains empty until updates or deletes occur.

UPDATE/DELETE – modify the current table and automatically write the previous version to the history table.

SELECT – DB2 supports AS OF, FROM … TO …, and BETWEEN … AND … for both valid and transaction time.

4. SQL Server 2016: Temporal Tables

SQL Server 2016 supports only transaction‑time (rollback) tables. It creates a system‑versioned table by adding SYSTEM_VERSIONING = ON to a user table, which automatically creates and binds a hidden history table. No manual history table definition is required.

Key points:

CREATE – enable system versioning; the history table can reside in a separate database for security.

INSERT/UPDATE/DELETE – behave like the transaction‑time operations defined in SQL:2011.

SELECT – supports AS OF, FROM … TO …, BETWEEN … AND …, and CONTAINED IN queries, with slight semantic differences from the standard.

Additional Features – point‑in‑time comparison (vertical data comparison) and system consistency checks for schema and data integrity.

5. MariaDB: System‑Versioned Tables

MariaDB does not support valid time but provides flexible transaction‑time tables. Three creation styles are offered:

Standard – follows SQL:2011 with generated ROW START and ROW END columns; current and history rows share the same table.

Simplified – omits explicit row‑start/end definitions; the system still records versioning, accessible via special syntax.

Transaction‑Precise – uses transaction IDs as the time metric, suitable for scenarios where commit time matters more than write time.

MariaDB also supports partitioned storage of current and historical rows, allowing separate or combined partitions for better performance and manageability.

Dynamic system versioning lets administrators add or remove versioning from existing tables, enabling gradual adoption as historical data needs grow.

6. Comparison

The table below summarizes support across the examined products and T‑TDSQL (a Tencent implementation). A check (√) indicates full support or compliance with SQL:2011; a cross (×) indicates lack of support.

Overall, SQL:2011 provides a minimal yet powerful extension to existing relational systems, and major vendors have adopted its concepts to varying degrees, enabling practical temporal data management in production environments.