我们如何获得 15 分钟的时间间隔 [英] How can we get the 15 minutes time interval

查看:156
本文介绍了我们如何获得 15 分钟的时间间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图以这样的方式获取每 15 分钟的数据,如果当前时间是 23-10-19 11:11:30 那么我需要从 23-10-19 10:30:59 获取数据到 23-10-19 10:45:59 以同样的方式如果时间是 23-10-19 11:15:30 那么我需要从 23-10-19 10:45:59 到 23-10-19 11:00:59.

我曾尝试忘记 15 分钟的延迟,但不是我想要的方式.请建议我如何处理这个场景

select concat(to_char(current_timestamp - numtodsinterval(30,'MINUTE'),'yyyy-mm-dd hh24:mi'),':59') A,concat(to_char(current_timestamp - numtodsinterval(15,'MINUTE'),'yyyy-mm-dd hh24:mi'),':59') B,to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss') C 来自双

下面是我得到的输出.

A B C------------------- ------------------- -------------------2019-10-23 13:03:59 2019-10-23 13:18:59 2019-10-23 13:33:22

解决方案

您可以截断到最接近的分钟以将秒数归零,然后减去分钟数以回到最接近的 15 分钟间隔,然后应用您的偏移量:

SELECT TRUNC( current_timestamp, 'MI' )- MOD(EXTRACT(MINUTE FROM current_timestamp), 15) * INTERVAL '1' MINUTE- 间隔30"分钟+ 间隔'59'秒作为开始时间,TRUNC(当前时间戳,'MI')- MOD(EXTRACT(MINUTE FROM current_timestamp), 15) * INTERVAL '1' MINUTE- 间隔15"分钟+ INTERVAL '59' SECOND AS end_time,当前_时间戳从双

输出:

<块引用><前>START_TIME |END_TIME |CURRENT_TIMESTAMP:------------------ |:------------------ |:----------------------------2019-10-23 09:00:59 |2019-10-23 09:15:59 |2019-10-23 09:42:53.742684000

db<>fiddle 这里

I am trying to fetch every 15min data in such a way that, if the current time is 23-10-19 11:11:30 then I need to get the data from 23-10-19 10:30:59 to 23-10-19 10:45:59 in the same way if the time is 23-10-19 11:15:30 then I need to get the data from 23-10-19 10:45:59 to 23-10-19 11:00:59.

I have tried forgetting the 15min delay but not the way I want to approach. Please suggest me how can we approach the scenario

select concat(to_char(current_timestamp - numtodsinterval(30,'MINUTE'),'yyyy-mm-dd hh24:mi'),':59') A, 
concat(to_char(current_timestamp - numtodsinterval(15,'MINUTE'),'yyyy-mm-dd hh24:mi'),':59') B, 
to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss') C from dual 

below is the output that I was getting.

A                   B                   C                  
------------------- ------------------- -------------------
2019-10-23 13:03:59 2019-10-23 13:18:59 2019-10-23 13:33:22

解决方案

You can truncate to the nearest minute to zero the seconds and then subtract the number of minutes to get back to the nearest 15 minute interval past the hour and then apply your offsets:

SELECT TRUNC( current_timestamp, 'MI' )
         - MOD( EXTRACT( MINUTE FROM current_timestamp ), 15 ) * INTERVAL '1' MINUTE
         - INTERVAL '30' MINUTE
         + INTERVAL '59' SECOND AS start_time,
       TRUNC( current_timestamp, 'MI' )
         - MOD( EXTRACT( MINUTE FROM current_timestamp ), 15 ) * INTERVAL '1' MINUTE
         - INTERVAL '15' MINUTE
         + INTERVAL '59' SECOND AS end_time,
       current_timestamp
FROM   DUAL

Outputs:

START_TIME          | END_TIME            | CURRENT_TIMESTAMP            
:------------------ | :------------------ | :----------------------------
2019-10-23 09:00:59 | 2019-10-23 09:15:59 | 2019-10-23 09:42:53.742684000

db<>fiddle here

这篇关于我们如何获得 15 分钟的时间间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆