Oracle 查询向上或向下舍入到最接近的 15 分钟间隔 [英] Oracle query Round up or down to nearest 15 minute interval
本文介绍了Oracle 查询向上或向下舍入到最接近的 15 分钟间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
08-SEP-20 08:55:0508-SEP-20 15:36:13
下面的查询在 15:36:13 正常工作,因为它四舍五入到 15:30,但 8:55:05 向下舍入到 08:45,而它应该四舍五入到 09:00
select event_date,trunc(event_date,'mi') - numtodsinterval( mod(to_char(event_date,'mi'),15), 'minute' ) asnearest_quarter来自 time_source_in_all,其中 empno = '002307718' 和 event_date 介于 '8-sep-2020' 和 '9-sep-2020'
解决方案
您可以使用:
SELECT event_date,TRUNC(事件日期,'HH')+ ROUND(EXTRACT(MINUTE FROM CAST(event_date AS TIMESTAMP))/15)* 间隔15"分钟AS rounded_15_event_date从表名
或:
SELECT event_date,TRUNC(事件日期,'HH')+ ROUND(( event_date - TRUNC( event_date, 'HH' ) ) * 24 * 4 )* 间隔15"分钟AS rounded_15_event_date从表名
对于您的示例数据:
CREATE TABLE table_name ( event_date ) AS选择日期 '2020-09-08' + 时间间隔 '08:55:05' 小时到第二个双联所有选择日期 '2020-09-08' + 间隔 '15:36:13' 小时到第二个
两个输出:
<块引用><前>EVENT_DATE |ROUNDED_15_EVENT_DATE:------------------ |:--------------------2020-09-08 08:55:05 |2020-09-08 09:00:002020-09-08 15:36:13 |2020-09-08 15:30:00db<>fiddle 这里
08-SEP-20 08:55:05
08-SEP-20 15:36:13
The query below is working correctly for 15:36:13 in that it rounds to 15:30 but the 8:55:05 is rounding down to 08:45 when it should be rounding to 09:00
select event_date,trunc(event_date,'mi') - numtodsinterval( mod(to_char(event_date,'mi'),15), 'minute' ) as nearest_quarter
from time_source_in_all where empno = '002307718' and event_date between '8-sep-2020' and '9-sep-2020'
解决方案
You can use:
SELECT event_date,
TRUNC( event_date, 'HH' )
+ ROUND( EXTRACT( MINUTE FROM CAST( event_date AS TIMESTAMP ) ) / 15 )
* INTERVAL '15' MINUTE
AS rounded_15_event_date
FROM table_name
or:
SELECT event_date,
TRUNC( event_date, 'HH' )
+ ROUND( ( event_date - TRUNC( event_date, 'HH' ) ) * 24 * 4 )
* INTERVAL '15' MINUTE
AS rounded_15_event_date
FROM table_name
Which, for your sample data:
CREATE TABLE table_name ( event_date ) AS
SELECT DATE '2020-09-08' + INTERVAL '08:55:05' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2020-09-08' + INTERVAL '15:36:13' HOUR TO SECOND FROM DUAL
Both output:
EVENT_DATE | ROUNDED_15_EVENT_DATE :------------------ | :-------------------- 2020-09-08 08:55:05 | 2020-09-08 09:00:00 2020-09-08 15:36:13 | 2020-09-08 15:30:00
db<>fiddle here
这篇关于Oracle 查询向上或向下舍入到最接近的 15 分钟间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文