title: Oracle query for time range
body: <p>The below is a practice for myself:</p>
  
  <p>I've created a table with some values, as:</p>
  
  <pre><code>CREATE TABLE my_test AS
  SELECT ROWNUM ID, TRUNC(SYSDATE)+(LEVEL*5/24/60/60)date_time , 111 person_id
  FROM dual CONNECT BY LEVEL &lt;= (24*60*60)/5 
  ORDER BY 1;
  </code></pre>
  
  <p>Now, I updated table , <code>person_id = 222</code> for <code>date_time</code> between <code>5:30 am</code> and <code>6:30 am</code></p>
  
  <pre><code>UPDATE my_test
  SET  person_id   = 222
  WHERE date_time &gt;= trunc(SYSDATE)+ 5/24 + 30/24/60
   AND DATE_TIME  &lt;= trunc(sysdate)+ 6/24 + 30/24/60;
  </code></pre>
  
  <p>I need to take the number of persons in each hour, so I've written a code like:</p>
  
  <pre><code>SELECT time_range,count(distinct person_id) PERSON_COUNT
  FROM (
  select person_id , date_time,
  case TO_CHAR(date_time,'hh24') when '00' then '12 AM - 01 AM' 
               when '01' then '01 AM - 02 AM'
               when '02' then '02 AM - 03 AM'
               when '03' then '03 AM - 04 AM'
               when '04' then '04 AM - 05 AM'
               when '05' then '05 AM - 06 AM'
               when '06' then '06 AM - 07 AM'
               when '07' then '07 AM - 08 AM'
               when '08' then '08 AM - 09 AM'
               when '09' then '09 AM - 10 AM'
               when '10' then '10 AM - 11 AM'
               when '11' then '11 AM - 12 PM'
               when '12' then '12 PM - 01 PM'
               when '13' then '01 PM - 02 PM'
               when '14' then '02 PM - 03 PM'
               when '15' then '03 PM - 04 PM'
               when '16' then '04 PM - 05 PM'
               when '17' then '05 PM - 06 PM'
               when '18' then '06 PM - 07 PM'
               when '19' then '07 PM - 08 PM'
               when '20' then '08 PM - 09 PM'
               when '21' then '09 PM - 10 PM'
               when '22' then '10 PM - 11 PM'
               WHEN '23' THEN '11 PM - 12 AM' end time_range
  FROM my_test
  WHERE date_time &gt;= trunc(SYSDATE)
  AND date_time &lt; trunc(SYSDATE)+1
  ORDER BY date_time)
  GROUP BY time_range;
  </code></pre>
  
  <p>Its working fine without expected ordering. ( The order by time_range not gives the expected as it is string in the outer query).</p>
  
  <p>I need to order by time_range, and any simple and easy codes for the above are appreciated.
  Thanks in advance.</p>