oracle将时间段分割，按小时分组，求各个子时间段的分钟数

最新推荐文章于 2024-06-21 08:00:00 发布

鑫郎

最新推荐文章于 2024-06-21 08:00:00 发布

阅读量1.2w

点赞数 1

本文链接：https://blog.csdn.net/u010966868/article/details/78654658

版权

  今天遇到这么个问题，给定了两个时间串，要求是求这两个时间串的每个小时分隔里面的分钟数。 

  比如20171103092800和20171103115300这两个时间点之间的每个小时的分钟数，那么可以知道， 

  9:00-10:00之间，是从9:28-10:00 32分钟 

  10：00-11:00 60分钟 

  11:00-12:00 是从11:00-11:53 53分钟 

  那么如何才能求出这个结果集呢？ 

  当时有两个想法，一个是用connect by，另一个是过程 

  但是plsql写起来总觉得麻烦。 

  想了一会，还是用connect by更直接 

  于是调了一会，sql如下 

 
 select 

 
 group_time, 

 
 start_time, 

 
 end_time, 

 
 (end_time-start_time)* 
 24 
 * 
 60 
   
 as 
  minutes 

 
 from 

(

 
    
 select 

 
   group_time, 

 
    
 case 
   
 when 
  start_time>=group_time  
 then 
  start_time 

 
      
 else 
  group_time  
 end 
   
 as 
  start_time, 

 
    
 case 
   
 when 
  end_time<=group_time+ 
 1 
 / 
 24 
   
 then 
  end_time 

 
      
 else 
  group_time+ 
 1 
 / 
 24 
   
 end 
   
 as 
  end_time 

 
    
 from 

(

 
      
 select 

 
      
 trunc 
 (start_time, 
 'hh24' 
 )+( 
 level 
 - 
 1 
 )/ 
 24 
   
 as 
  group_time, 

 
     start_time, 

 
     end_time 

 
      
 from 

(

 
        
 select 
  to_date( 
 '20171103115300' 
 , 
 'yyyy-mm-dd hh24:mi:ss' 
 )  
 as 
  end_time, 

 
       to_date( 
 '20171103092800' 
 , 
 'yyyy-mm-dd hh24:mi:ss' 
 )  
 as 
  start_time 

 
 from 
  dual

)

 
      
 connect 
   
 by 
   
 level 
 <=( 
 trunc 
 (end_time, 
 'hh24' 
 )- 
 trunc 
 (start_time, 
 'hh24' 
 ))* 
 24 
 + 
 1 

)

)

 
 where 
  start_time!=end_time 

 
 在这个例子里面，其实有两个重要的步骤 

 
 1、就是利用connect by 得到按小时的分组，这里的分组是9：00 代表9-10 以此类推 

 
 2、判断最起始时间和最末尾时间是否是整点，如果不是整点，就保留这两个时间点作为分组的起始时间和末尾时间 

 
 处理过程 

 
 1、内层的connect by 分组： 

 
 这里start_time和end_time保留一下，为了下一步进行整点判断 

 
 2、外面判断整点之后的结果： 

 
 这样就可以得到各个时间段了 

 
 3、最后求得每个时间段之间的分钟数 

 
 最后之所以要加一个start_time!=end_time的条件，是因为，在内层的处理里面，如果end_time是一个整点的话，由于用的是end_time<=group_time+1/24，这里会保留一个start_time=end_time的情况，所以加了这个条件。 

 
  不加的时候会这样： 
 
  最后一条记录没必要

关注