Transaction time
What was the
ACID state of the data as of <time>?
Thus, it only stores past data states.
To be maintained by the system (»system versioned tables«).
Useful for auditing.
Records for which the current time falls into their period are called system rows. Other records are called historical system rows.
Only system rows can be updated or deleted. Similarly, constraints are enforced on system rows only.
Syntax
The create table statement contains a period clause whose name is system_time.
create table t (
col_one …,
…
validFrom timestamp(12) generated always as row begin not null,
validTo timestamp(12) generated always as row end not null,
trxId timestamp(12) generated always as transaction start id,
period system_time (vaildFrom, validTo)
)
[ with (system_versioning = on) ] -- ???
;
create table t_hist like t;
alter table t add versioning use history table t_hist;
select … from t as of system time '2019-04-03 09:52:18' where …;
select … from t as of system time timestamp '2019-04-03';
select … from t as of system time current_timestamp;
select … from t as of system time current_timestamp - 10 days;
select … from t as of system time current_timestamp - 7 minutes;
select • from t for system_time as of '2019-04-03 09:52:18' where …
select • from t as of timestamp '2019-04-03 09:52:18' where …
select * from t as of timestamp '2019-04-03 09:52:18' where …
-- Pseude columns
select
version_starttime,
version_endtime,
version_xid
…
Business Time (aka Application time)
Maintained by user/applications (not by system).
Possibility of entering future dates (I'll change my address on 1st of November of 2019). Such an update statement is called proactive update.
The create table statement also contains a period clause, however, for business time tables, the name of the period is user-definable.
Syntax
The standard does (not yet?) propose a syntax for querying Business Time.
select * from t for business_time as of '2019-04-03 09:52:18';