Grouping by hour

I have a table containing a start_time column. This column contains both date and time information (eg 29/08/2000 14:24:03). I need to be able to retrieve information from the database for between the hours of 6am to 8pm for each day. I then need to further breakdown the information into hours ie. 6am, 7am, etc. Can you please give me a suggestion on how I might do this?
 

To illustrate this solution, let's create a very simple Sales table and populate it with sample data:

create table Sales
( DateTimeStamp date,
  Amount dec(11,2)
);

insert into Sales values 
  (to_date('01-JAN-2002 08:12','DD-MON-YYYY HH24:MI'), 42);
insert into Sales values 
  (to_date('01-JAN-2002 08:56','DD-MON-YYYY HH24:MI'), 7);
insert into Sales values 
  (to_date('01-JAN-2002 09:32','DD-MON-YYYY HH24:MI'), 63);
insert into Sales values 
  (to_date('01-JAN-2002 09:41','DD-MON-YYYY HH24:MI'), 50);
insert into Sales values 
  (to_date('01-JAN-2002 10:03','DD-MON-YYYY HH24:MI'), 14);
insert into Sales values 
  (to_date('01-JAN-2002 10:46','DD-MON-YYYY HH24:MI'), 97);
insert into Sales values 
  (to_date('01-JAN-2002 10:57','DD-MON-YYYY HH24:MI'), 52);

In Oracle, you can simply group by the hour, obtained using the TRUNC function. TRUNC sets to their base values the time segments smaller than the indicated segment. For example, TRUNC('17-JAN-2002 10:30:00 PM', 'MONTH') would return 01-JAN-2002 12:00:00 AM. Here's our SQL:

select trunc(DateTimeStamp,'HH') "HOUR", sum(Amount) 
  from Sales
  group by trunc(DateTimeStamp,'HH')

The results look like this:

HOUR               SUM(AMOUNT)
------------------ -----------
1/1/02 8:00:00 AM          49
1/1/02 9:00:00 AM         113
1/1/02 10:00:00 AM        163

Other databases have similar functions. Even with MS SQL Server, which does not have a date function equivalent to TRUNC, you can obtain similar results by using CAST and DATEPART:

select cast( floor( cast( DateTimeStamp as float ) ) 
             as datetime ) "DAY", 
       datepart(hour, DateTimeStamp) "HOUR",
       sum(Amount) "TOTAL"
  from Sales
  group by cast( floor( cast( DateTimeStamp as float ) ) 
                 as datetime ), 
       datepart(hour, DateTimeStamp);

DAY        HOUR    TOTAL
---------- ---- --------
2002-01-01    8    49.00
2002-01-01    9   113.00
2002-01-01   10   163.00
2002-01-01   14    52.00

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.