Split timestamps into 24 hours and get an overview for a week

Question

I am a beginner in programming and especially SQL. I am working with the SQLDeveloper on the AOLDATA-database. The structure of the table I am working on looks like this: AOLDATA.querydata

I did not figured out how to write a statement that provides the timestamps splitted into different parts. I want to know how often was searched for a term, for the whole interval(01.03.2006-28.05.2006). My approach to solve the problem is:

SELECT COUNT (id)
FROM AOLDATA.querydata
WHERE querytime between ('01.03.2006 00:00:00') 
AND ('01.03.2006 00:59:00')
AND QUERY LIKE '%burgerking%';

But when I do it this way, I have to start more than 2000 requests to cover the whole timeframe.As a result the best output would be to have in a format like this.

Term: burgerking

• Monday(00:00-00:59 (40 results),…,23:00-23:59(13 results))
• …
• …
• …
• …
• …
• Sunday(00:00-00:59 (40 results),…,23:00-23:59(13 results))

Is there any good solution like a loop or something else easy way to do it?

Answer 1

Try:

SELECT *
FROM (
      select to_char( querytime, 'Day' ) my_day,
             to_char( querytime, 'D' ) my_day_of_week,
             to_char( querytime, 'hh24') my_hour, 
             id
      from querydata q
)  
pivot  (
  count(id) for (my_hour) in (
     '00' as "00:00-00:59",
     '01' as "01:00-01:59",
     '02' as "02:00-02:59",
     '03' as "03:00-03:59",
     '04' as "04:00-04:59",
     '05' as "05:00-05:59",
     '06' as "06:00-06:59",
     '07' as "07:00-07:59",
     '08' as "08:00-08:59",
     '09' as "07:00-09:59",
     '10' as "10:00-10:59",
     '11' as "11:00-11:59",
     '12' as "12:00-12:59",
     '13' as "13:00-13:59",
     '14' as "14:00-14:59",
     '15' as "15:00-15:59",
     '16' as "16:00-16:59",
     '17' as "17:00-17:59",
     '18' as "18:00-18:59",
     '19' as "19:00-19:59",
     '20' as "20:00-20:59",
     '21' as "21:00-21:59",
     '22' as "22:00-22:59",
     '23' as "23:00-23:59"
  )
)
ORDER BY my_day_of_week