oracle数据库按照时间查询数据

数据库时间类型为timstamp，按照时间进行数据查询

 

select * from table1 
where 
TS between TO_DATE('2017-7-25 16:30:00','yyyy-mm-dd hh24:mi:ss') 
and TO_DATE('2017-7-25 19:30:00','yyyy-mm-dd hh24:mi:ss'

 

查看两个时间差之前大于5秒的数据

 

select count(*) from 
(select * from table1 
where 
TS between TO_DATE('2017-7-25 16:30:00','yyyy-mm-dd hh24:mi:ss') 
and TO_DATE('2017-7-25 19:30:00','yyyy-mm-dd hh24:mi:ss') order by (ORACLE_TS) desc) tb
where extract(second from (tb.ORACLE_TS-tb.TS)) > 5

注：oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分

 

 

select extract(year from systimestamp) year    
      ,extract(month from systimestamp) month    
      ,extract(day from systimestamp) day    
      ,extract(minute from systimestamp) minute    
      ,extract(second from systimestamp) second    
      ,extract(timezone_hour from systimestamp) th    
      ,extract(timezone_minute from systimestamp) tm    
      ,extract(timezone_region from systimestamp) tr    
      ,extract(timezone_abbr from systimestamp) ta    
from dual；
结果如下：
     YEAR      MONTH        DAY     MINUTE     SECOND         TH         TM TR         TA    
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------    
      2011          5         17          7     14.843          8          0 UNKNOWN   UNK