SELECT * FROM <table_name> AS OF TIMESTAMP (SYSDATE-<interval>);

--一小時(shí)是一天的1/24 ，故<interval>參數(shù)為1/24，如果想查四小時(shí)前的數(shù)據(jù)，<interval>參數(shù)可以是4/24或1/6
SELECT * FROM USER_INFO AS OF TIMESTAMP (SYSDATE-1/24);

SELECT * FROM USER_INFO AS OF TIMESTAMP (SYSDATE-1);

SELECT * FROM USER_INFO AS OF TIMESTAMP (SYSDATE-3);

CREATE TABLE TABNAME_TEMP AS SELECT * FROM TABNAME AS OF TIMESTAMP (SYSDATE-<interval>);

 -- SELECT * FROM TABNAME_TEMP 語(yǔ)句 可以帶where條件，看您需要插入所有數(shù)據(jù)還是選擇性插入
 INSERT INTO TABNAME SELECT * FROM TABNAME_TEMP ;

DROP TABLE TABNAME_TEMP;--注意這里刪除的是臨時(shí)表，您剛剛創(chuàng)建的表，別刪錯(cuò)了。

CREATE TABLE USER_INFO_TEMP AS SELECT * FROM USER_INFO AS OF TIMESTAMP (SYSDATE-1);
INSERT INTO USER_INFO SELECT * FROM USER_INFO_TEMP where username in ('Tom','Jerry');
DROP TABLE USER_INFO_TEMP;

//一月之內(nèi)
SELECT * FROM ESS.E_LOG WHERE E_LOG."ETime" BETWEEN sysdate-interval '7' month AND SYSDATE;
 
SELECT * FROM ESS.E_LOG
WHERE to_char(E_LOG."ETime",'yyyy-mm-dd hh24:mi:ss') between to_char(sysdate-225,'yyyy-mm-dd hh24:mi:ss') and to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
 
//一周之內(nèi)
select * from ESS.E_LOG where E_LOG."ETime" > sysdate - interval '7' day;
 
 
//一年之內(nèi)
select count(*) from ESS.E_LOG WHERE E_LOG."ETime" BETWEEN sysdate-interval '1' year AND SYSDATE;
 
//sysdate獲取的時(shí)間是Oracle數(shù)據(jù)庫(kù)系統(tǒng)時(shí)間
select sysdate from dual;