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.