Temporal tables
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
SQL:2011 standardised a feature called temporal validity, which comes in two flavours implemented through temporal tables:
- System versioned tables
- Application versioned tables
A few dialects, including DB2, MariaDB, Oracle, SQL Server implement one or the other, or both types of temporal tables through standard or vendor specific syntax.
System versioned tables
A system versioned table can be used for automatic backups or audit logging of all content in a table. Each "version" of a record has a validity period, until the record is updated or deleted, when a new "version" of the record is created.
Consider the following table (please consider your database manual for actual syntax. There are restrictions on data types and on how the history table is managed.):
CREATE TABLE product_price ( product_id BIGINT NOT NULL PRIMARY KEY, price DECIMAL NOT NULL, start_ts TIMESTAMP GENERATED ALWAYS AS ROW START, end_ts TIMESTAMP GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (start_ts, end_ts) );
A table that is defined using the above (simplified) syntax can now be used in DML statements as follows:
-- At time T1, a new product is created: INSERT INTO product (product_id, price) VALUES (1, 100.00); -- Later, at time T2, the price is updated: UPDATE product SET price = 200.00 WHERE product_id = 1;
create.insertInto(PRODUCT, PRODUCT.PRODUCT_ID, PRODUCT.PRICE)
.values(1, new BigDecimal("100.00"))
.execute();
create.update(PRODUCT)
.set(PRODUCT.PRICE, new BigDecimal("200.00"))
.where(PRODUCT.PRODUCT_ID.eq(1))
.execute();
Thanks to system versioning, the UPDATE statement is no longer "destructive", meaning that the original row containing the (1, 100.00) price information is still available from the archive. We can query the PRODUCT table and its archive as follows (see example query results further down):
-- 1. Get the current version by default SELECT * FROM product; -- 2. Get the version at a given time SELECT * FROM product FOR system_time AS OF :t1; -- 3. Get several versions overlapping a time range [t1, t2] SELECT * FROM product FOR system_time BETWEEN :t1 AND :t2; -- 4. Get several versions overlapping a time range [t1, t2) SELECT * FROM product FOR system_time FROM :t1 TO :t2; -- 5. Get several versions included in a time range [t1, t2] SELECT * FROM product FOR system_time CONTAINED IN (:t1, :t2); -- 6. Get all versions SELECT * FROM product FOR system_time ALL;
// Get the current version by default create.selectFrom(product).fetch(); create.selectFrom(product.for_( systemTime().asOf(t1) )).fetch(); create.selectFrom(product.for_( systemTime().between(t1).and(t2)) )).fetch(); create.selectFrom(product.for_( systemTime().from(t1).to(t2) )).fetch(); create.selectFrom(product.for_( systemTime().containedIn(t1, t2) )).fetch(); create.selectFrom(product.for_( systemTime().all() )).fetch();
The results of the above queries might look like this:
+----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 1 | 1 | 200 | T2 | | +----------+------------+-------+----------+--------+ +----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 2, 4, 5 | 1 | 100 | T1 | T2 | +----------+------------+-------+----------+--------+ +----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 3, 6 | 1 | 100 | T1 | T2 | | 3, 6 | 1 | 200 | T2 | | +----------+------------+-------+----------+--------+
jOOQ 3.13 only supports the above syntaxes if they are natively supported by the underlying dialect as well. Future jOOQ versions may emulate the syntax also in other dialects, or where a specific clause is not supported.
Application versioned tables
While system versioned tables allow for implementing backups and audit logs, some data is naturally versioned from a business perspective as well. Perhaps, instead of just archiving the pricing information, we may wish to specify a validity range for which a given price was valid on a given product. That way, we can accurately restore the old price on an old period in case we need it for reporting or accounting reasons. The (simplified) syntax is almost the same as with system versioned tables, except that instead of using a "magic" SYSTEM_TIME period name, we can now use our own (or in case of DB2, use BUSINESS_TIME). Please look up your dialect's manual again, for exact syntax:
CREATE TABLE product_price ( product_id BIGINT NOT NULL PRIMARY KEY, price DECIMAL NOT NULL, start_ts TIMESTAMP, end_ts TIMESTAMP, PERIOD FOR validity (start_ts, end_ts) );
A table that is defined using the above (simplified) syntax can now be used in DML statements as follows:
-- A new product is created INSERT INTO product (product_id, price) VALUES (1, 100.00); -- For the time between [t1, t2], a discount is applied UPDATE product FOR PORTION OF validity FROM t1 TO t2 SET price = 50.00 WHERE product_id = 1;
create.insertInto(PRODUCT, PRODUCT.PRODUCT_ID, PRODUCT.PRICE)
.values(1, new BigDecimal("100.00"))
.execute();
create.update(PRODUCT.forPortionOf(
period(unquotedName("validity")).from(t1).to(t2)))
.set(PRODUCT.PRICE, new BigDecimal("50.00"))
.where(PRODUCT.PRODUCT_ID.eq(1))
.execute();
If not combined with system versioning, this is again a destructive UPDATE statement, which is effectively transformed into several statements. The resulting table content now looks like this:
+------------+-------+----------+--------+ | PRODUCT_ID | PRICE | START_TS | END_TS | +------------+-------+----------+--------+ | 1 | 100.0 | | T1 | | 1 | 50.0 | T1 | T2 | | 1 | 100.0 | T2 | | +------------+-------+----------+--------+
Depending on your dialect, you can reuse the previous FOR clauses in SELECT statements, for example:
-- 2. Get the version at a given time SELECT * FROM product FOR validity AS OF :t1;
create.selectFrom(product.for_(
period(unquotedName("validity")).asOf(t1)
)).fetch();
Which will produce the value of your attribute(s) given their validity at a given timestamp T1:
+------------+-------+----------+--------+ | PRODUCT_ID | PRICE | START_TS | END_TS | +------------+-------+----------+--------+ | 1 | 50.0 | T1 | T2 | +------------+-------+----------+--------+
Feedback
Do you have any feedback about this page? We'd love to hear it!