FOR SYSTEM_TIME AS OF otime语法的含义

1. 背景

在测试FlinkSQL时，遇到FOR SYSTEM_TIME AS OF语法，研究下。
FOR SYSTEM_TIME AS OF otime指的是在otime时间点有效的数据。

2. 实践过程

时态数据库测试选择了SQL-server2017

-- 创建 时态表
-- DBO是每个数据库的默认用户，具有所有者权限，即DbOwner 
create table dbo.Emp (
[eno] integer not null primary key,
[Sys_start] datetime2(2) generated always as row start,
[Sys_end] datetime2(2) generated always as row end,
[ename] varchar(50),
period FOR SYSTEM_TIME (Sys_start, Sys_end)
) with (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpHistory) );

select * from dbo.Emp ;

-- 2022-01-24 05:58:13.71 插入一条数据
insert into dbo.Emp values('1', DEFAULT  ,  DEFAULT , 'ALIAS') ;

-- 2022-01-24 05:59:12.66 更新值
UPDATE dbo.Emp SET ENAME = 'BOB' ;
-- 查询2022-01-24 05:59:11.66时刻的表数据
select * from dbo.Emp 
FOR SYSTEM_TIME AS OF '2022-01-24 05:59:11.66' ;

-- 2022-01-24 06:01:43.88
UPDATE dbo.Emp SET ENAME = 'CANDY' ;
-- 查询2022-01-24 05:59:59.66时刻的表数据
select * from dbo.Emp 
FOR SYSTEM_TIME AS OF '2022-01-24 05:59:59.66' ;

-- 查询当前时间的表数据
select * from dbo.Emp ;

3. 引用

TDSQL-时态数据库
SQL-server2017安装
sql server语法(基础整理）
SqlServer中的dbo是什么意思

4. 拓展

Oracle中查看指定时间的表快照

# 注意oracle只保留近5天的快照
select * from xxx.test_table as of timestamp to_date('2022-01-21 11:19','yyyy-mm-dd hh24:mi');

oracle的闪回操作及一些相关知识点