Q

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 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 last published in April 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close