SQL SYSTEM

What does the SQL SYSTEM keyword do?

SYSTEM is used inside the FOR SYSTEM_TIME clause to query a system-versioned temporal table as it existed at a specific point or period in time.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SYSTEM:

SQL SYSTEM Full Explanation

SYSTEM appears in the ISO/IEC SQL:2011 temporal feature within the clause FOR SYSTEM_TIME. When a table is defined with SYSTEM VERSIONING, the database engine automatically keeps historic versions of each row along with system-generated period columns (typically row start and row end timestamps). The FOR SYSTEM_TIME clause lets you read those historic versions without restoring backups. You can retrieve the state of the data at an exact timestamp (AS OF), within a closed or open interval (FROM … TO / BETWEEN … AND), or across the entire history (ALL). This is invaluable for auditing, debugging, regulatory compliance, and point-in-time reporting. Only SELECT statements are allowed with FOR SYSTEM_TIME; DML against historic versions is blocked. Attempting to use SYSTEM on a non-versioned table raises an error.

SQL SYSTEM Syntax

-- Point-in-time query
SELECT *
FROM   table_name
FOR SYSTEM_TIME AS OF '2024-05-01 10:00:00';

-- Interval query (closed)
SELECT *
FROM   table_name
FOR SYSTEM_TIME FROM '2024-04-01' TO '2024-04-30';

-- Interval query (inclusive)
SELECT *
FROM   table_name
FOR SYSTEM_TIME BETWEEN '2024-04-01' AND '2024-04-30';

-- Full history
SELECT *
FROM   table_name
FOR SYSTEM_TIME ALL;

SQL SYSTEM Parameters

timestamp_expression (TIMESTAMP) - exact point in time for AS OF
start_timestamp (TIMESTAMP) - beginning of interval for FROM / BETWEEN
end_timestamp (TIMESTAMP) - end of interval for TO / AND

Example Queries Using SQL SYSTEM

-- 1. Find the price of a product at a past moment
SELECT product_id, price
FROM   products
FOR SYSTEM_TIME AS OF '2024-01-15 09:30:00'
WHERE  product_id = 42;

-- 2. Retrieve all versions changed in April
SELECT *
FROM   orders
FOR SYSTEM_TIME BETWEEN '2024-04-01' AND '2024-04-30'
WHERE  order_id = 9001;

-- 3. Audit the entire history of an employee row
SELECT *
FROM   employees
FOR SYSTEM_TIME ALL
WHERE  employee_id = 7;

Expected Output Using SQL SYSTEM

The queries return historic rows exactly as they existed during the specified time period
No current data outside the time boundary is shown
The result is read-only

Use Cases with SQL SYSTEM

Auditing changes for compliance
Debugging data regressions after a deployment
Generating point-in-time financial statements
Answering legal or customer inquiries about historic values
Validating ETL processes by comparing snapshots

Common Mistakes with SQL SYSTEM

Running FOR SYSTEM_TIME on a table that is not SYSTEM VERSIONED
Forgetting to quote timestamps in ISO format, causing implicit conversions or errors
Assuming FOR SYSTEM_TIME works with INSERT/UPDATE/DELETE (it is read-only)
Mixing FROM … TO with BETWEEN … AND incorrectly (they have different inclusivity rules)

First Introduced In

SQL:2011 temporal specification

Frequently Asked Questions

How does SYSTEM_TIME differ from application time?

SYSTEM_TIME tracks when rows changed according to the database system clock. Application time (also called VALID_TIME) represents when a fact is valid in the business domain. SYSTEM_TIME is automatic; application time is user-defined.

Can I update rows using FOR SYSTEM_TIME?

No. The clause is strictly for historical reads. Attempting DML on the results will throw an error such as "FOR SYSTEM_TIME table reference is read-only".

Does using SYSTEM_TIME impact performance?

Querying history tables adds I/O because past versions are scanned. Index the period columns and filter as narrowly as possible to minimize overhead.

What happens if my table is not versioned?

The database raises an error like "The table does not have SYSTEM_VERSIONING enabled". Enable SYSTEM VERSIONING first or remove the FOR SYSTEM_TIME clause.