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.


Dig Deeper on Oracle development languages

Data Management
Business Analytics
  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

  • How developers can avoid remote work scams

    Software developers can find good remote programming jobs, but some job offers are too good to be true. Follow these tips to spot...

Data Center
Content Management