Q54375596, Oracle - rextester

Language: Layout:

SELECT sum(transactioncount) as transactioncount, transactiondate 
      FROM
      (
       with "TABLE"(date_modified) as
       (
         SELECT timestamp'2019-01-23 08:00:00' FROM dual union all
         SELECT timestamp'2019-01-23 08:30:00' FROM dual union all
         SELECT timestamp'2019-01-23 09:00:00' FROM dual union all
         SELECT timestamp'2019-01-24 05:01:00' FROM dual   
       )   
      SELECT nvl(count(*),0) AS transactioncount, trunc(date_modified, 'HH24') as transactiondate
        FROM "TABLE" t 
       GROUP BY trunc(date_modified, 'HH24')
      UNION ALL
      SELECT 0, timestamp'2019-01-23 07:00:00' + ( level - 1 )/24
        FROM dual    
     CONNECT BY level <=  24 * extract( day  from 
                                timestamp'2019-01-24 06:59:59'-
                                timestamp'2019-01-23 07:00:00') +
                               extract( hour from 
                                timestamp'2019-01-24 06:59:59'-
                                timestamp'2019-01-23 07:00:00') + 1      
    )    
     GROUP BY transactiondate
     ORDER BY transactiondate


absolute service time: 1,03 sec