Answer

SQL for hourly totals for the last 48 hours

SQL expert Rudy Limeback explains to an Oracle user how to user asks how to generate datetimes or use left outer join to get hourly totals for the last 48 hours in SQL.

Rudy Limeback
By
Published: 20 May 2008

I have a table with a datetime field. I would like to get an hourly summary from that table, which is easily achieved by using a 'group by'.

However, I would like the summary to include every hour within the last 48, even if no records exist for that hour. In this case it would simply return a zero for that hour. Any ideas?

We've seen this question--or ones very similar to it--before. However, it's been quite a while, so let's look at it again.

The solution relies on two techniques:

  1. Generate the datetimes

    This is done by using a numbers table. The query will use a table with the numbers from 0 to 47 in it. Each number is used as an interval value in a date expression, subtracting from a base datetime value. This generates the 48 datetime values that mark the groups for the purposes of the join.

  2. LEFT OUTER JOIN to your data

    The LEFT OUTER JOIN uses the 48 hourly datetime values as the "left" table, with your data as the "right" table, so that if no rows exist for a given hour, the aggregate functions will return zero because they ignore NULLs, and NULLs are what a LEFT OUTER JOIN produces for unmatched rows.

We start by taking the current datetime value and applying a "floor" or "truncate" technique to round it down to the nearest previous hour. In SQL, it can be done with an expression like this:

SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR

The EXTRACT function returns the current hour as an integer, e.g. 16 for 4 p.m. This number is then added, as an interval of hours, to the value of the current date, e.g. '2008-06-22'. The result is a timestamp for the beginning of the current hour, '2008-06-22 16:00:00'. Unfortunately, implementation varies from one DBMS to the next. In Oracle, you can instead simply TRUNC the SYSDATE value to the hour. In SQL Server, which has the GETDATE function for current timestamp but no equivalent CURRENT_DATE function, you can round down to the nearest previous hour like this:

DATEADD(HH,DATEDIFF(HH,'2000-01-01',GETDATE()),'2000-01-01')

Here '2000-01-01' is some arbitrary early "base date." The difference between this date and the current timestamp given by GETDATE(), as a number of whole hours, is added back to the base date, effectively rounding down to the nearest previous hour.

One satisfactory way to generate the 48 required numbers is to have an actual table of numbers:

SELECT n FROM numbers WHERE n BETWEEN 0 AND 47

Another way is to generate the numbers "on the fly" from a cross join of the integers table (the values 0 through 9) with itself:

SELECT 10 * t.i + u.i AS n FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47

Now we can use these 48 numbers as intervals to subtract from the starting hourly value, producing 48 hourly values. This is done in a subquery, which produces a derived table. The derived table becomes the left table in our LEFT OUTER JOIN to the data being summarized:

SELECT h.start_hour , SUM(mytable.amount) AS sum_amount FROM ( SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR - ( 10 * t.i + u.i ) HOUR AS start_hour FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47 ) AS h LEFT OUTER JOIN mytable ON mytable.datetimefield >= h.start_hour AND mytable.datetimefield < h.start_hour + 1 HOUR GROUP BY h.start_hour ORDER BY h.start_hour DESC

Dig Deeper on Oracle development languages