Ask the Expert

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?
 

    Requires Free Membership to View

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 editor@searchDatabase.com 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.


 

This was first published in April 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: