調べた限り未対応
(12cR1)
-- 設定
conn / as sysdba
drop flashback archive fla1;
create flashback archive default fla1 tablespace users quota 1G retention 1 month;
select * from dba_flashback_archive;
select * from dba_flashback_archive_ts;
-- 動作確認
conn test/test
alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';
alter table tab1 no flashback archive;
drop table tab1 purge;
create table tab1 (
col1 int ,
col2 int ,
primary key (col1)
);
alter table tab1 flashback archive fla1;
select sysdate from dual;
insert into tab1 values (1,1);
insert into tab1 values (2,1);
insert into tab1 values (3,1);
insert into tab1 values (4,1);
commit;
select sysdate from dual;
select * from tab1;
select sysdate from dual;
update tab1 set col2=col2*10 where col1=4;
commit;
select sysdate from dual;
select * from tab1;
---- 特定時点のデータを取得
select * from tab1 as of timestamp to_timestamp('2021/02/27 20:37:22','yyyy/mm/dd hh24:mi:ss');
(9.6)
https://blog.dbi-services.com/temporal-tables-with-postgresql/
https://github.com/arkhipov/temporal_tables
-- インストール
yum search pgxn
yum install pgxnclient.x86_64
yum install postgresql96-devel.x86_64
pgxn install temporal_tables --pg_config=/usr/pgsql-9.6/bin/pg_config
su - postgres
psql test
CREATE EXTENSION temporal_tables;
\dx temporal_tables
\dx+ temporal_tables
-- 動作確認
drop table tab1;
drop table tab1_history;
create table tab1 (
col1 int ,
col2 int ,
primary key (col1)
);
alter table tab1 add column a_period tstzrange not null;
create table tab1_history (like tab1);
create trigger tab1_hist_trigger before insert or update or delete on tab1 for each row
execute procedure versioning('a_period', 'tab1_history', true);
insert into tab1 values (1,1);
insert into tab1 values (2,1);
insert into tab1 values (3,1);
insert into tab1 values (4,1);
select * from tab1;
select * from tab1_history;
select now();
update tab1 set col2=col2*10 where col1=4;
select * from tab1;
select * from tab1_history;
---- system_timeとして特定時刻を設定する
select set_system_time('2021-08-01 11:12:13+09');
---- system_timeとして現在時刻に設定を戻す
select set_system_time(null);
---- 特定時点のデータを取得
select * from tab1
where a_period @> '2021-03-01'::timestamp with time zone
union all
select * from tab1_history
where a_period @> '2021-03-01'::timestamp with time zone
;
alter table dbo.tab1 set (system_versioning = off);
drop table dbo.tab1;
drop table dbo.tab1_history;
create table dbo.tab1 (
col1 int not null,
col2 int ,
a_period_start datetime2 generated always as row start not null,
a_period_end datetime2 generated always as row end not null,
primary key (col1),
period for system_time(a_period_start,a_period_end)
)
with
(
system_versioning = on (history_table=dbo.tab1_history)
);
insert into tab1(col1,col2) values (1,1);
insert into tab1(col1,col2) values (2,1);
insert into tab1(col1,col2) values (3,1);
insert into tab1(col1,col2) values (4,1);
select * from tab1;
select * from tab1_history;
select getdate();
update tab1 set col2=col2*10 where col1=4;
select * from tab1;
select * from tab1_history;
---- 特定時点のデータを取得
select * from tab1
for system_time as of '2021-02-27 12:00:33.6617179'
;
※期間列はUTCで格納される
