[SQL123] Oracle SQL 统计一定时间间隔内的数据

--Group by State_ts "per X minutes"
select to_char(state_ts, 'hh24')||':'||floor(to_char(state_ts, 'mi')/15)*15 tm, report_type, count(report_type)
from PETRA_ODRC_PP_01.tru_Reg_txn_report
where state_ts > to_date('15-Mar-19 08.00.00 AM','DD-MON-YY hh12.mi.ss AM') and state_ts < to_date('15-Mar-19 12.00.00 PM','DD-MON-YY hh12.mi.ss AM')
group by to_char(state_ts, 'hh24')||':'||floor(to_char(state_ts, 'mi')/15)*15, report_type;

 

https://www.cnblogs.com/yanghj010/p/5109714.html

天：
ROUND(TO_NUMBER(END_DATE - START_DATE))
小时：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒：
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)

 

取得与系统时间相差某一个范围的时间值的记录的写法，举例：

select * from TELEPHONE_BOOK where ROUND(TO_NUMBER(SYSDATE - BOOK_DATE) * 24) between 72 and 96

获得与系统时间相差大于72小时小于96小时的记录。