Geek asks and answers

Counting the number of hours records between two dates in oracle

I need a SINGLE request that executes this sequence in oracle.

select count(*) from table1
where request_time < timestamp'2012-05-19 12:00:00' and (end_time > timestamp'2012-05-19 12:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 13:00:00' and (end_time > timestamp'2012-05-19 13:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 14:00:00' and (end_time > timestamp'2012-05-19 14:00:00' or end_time=null);

select count(*) table1
where request_time < timestamp'2012-05-19 15:00:00' and (end_time > timestamp'2012-05-19 15:00:00' or end_time=null);

select count(*) from table1
where request_time < timestamp'2012-05-19 16:00:00' and (end_time > timestamp'2012-05-19 16:00:00' or end_time=null);

As you can see, the hour is increasing one after another. displayed here

COUNT(*)               
1085                   

COUNT(*)               
1233                   

COUNT(*)               
1407                   

COUNT(*)               
1322                   

COUNT(*)               
1237

I wrote a request, but it does not give me the correct answer!

select col1, count(*) from
(select TO_CHAR(request_time, 'YYYY-MM-DD HH24') as col1 from table1
 where request_time <= timestamp'2012-05-19 12:00:00' and (end_time >= timestamp'2012-05-19 12:00:00' or end_time=null))
group by col1 order by col1;

this query gives me a result set that the sum of its count (*) is equal to the first query written above! here is the result:

COL1          COUNT(*)               
------------- ---------------------- 
2012-05-19 07      22                     
2012-05-19 08      141                    
2012-05-19 09      322                    
2012-05-19 10      318                    
2012-05-19 11      282
+3
source share
4 answers

Note the use of a truncdate expression . You can omit alter sessionif you do not execute the query in sql * plus.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT 
       trunc(created,'HH'), 
       count(*) 
     FROM 
       test_table 
     WHERE 
       created > trunc(SYSDATE -2) 
     group by trunc(created,'HH');


TRUNC(CREATED,'HH')   COUNT(*)
------------------- ----------
2012-05-21 09:00:00        748
2012-05-21 16:00:00         24
2012-05-21 17:00:00         12
2012-05-21 22:00:00        737
2012-05-21 23:00:00        182
2012-05-22 20:00:00         16
2012-05-22 21:00:00        293
2012-05-22 22:00:00        610

8 ROWS selected.
+15
source

, , . , , ...

, , end_time = null request_time = 2012-05-19 13:30:00, , ; "" .

, request_time , request_time < timestamp'2012-05-19 12:00:00'?

+1

Oracle .

SELECT      to_char (, "DD-MM-YYYY HH" ),      -(*)                     trunc () >= to_Char ('02 -JUL-2017 ')    trunc () <= to_Char ('02 -JUL-2017 ')    group by to_char (, "DD-MM-YYYY HH" )

+1
source

try it

select TO_CHAR(request_time, 'HH24') as "hourOfDay",count(*)as
"numOfLogin", TO_CHAR(request_time, 'DD') as "date" from table1 
where request_time<= timestamp'2017-08-04 23:59:59' and
(request_time>= timestamp'2017-08-03 00:00:01' ) group by
TO_CHAR(request_time, 'HH24'),TO_CHAR(request_time, 'DD');
0
source

More articles:


All Articles