Artful Common Queries

Sum accumulated time by date

from the Artful Common Queries page

You track resource booking periods. You need a query to report daily usage for a given resource.

First the problem of calculating per-diem usage. Call the starting datetime of a booked period pStart, and its ending datetime pEnd. Then for a given date pDate, if the period began before pDate. then pDate usage begins at 00:00:00, otherwise it starts at pStart; likewise if the period extends past pDate, then pDate usage ends at midnight on pDate, otherwise it ends at pEnd. Therefore the period begins at...

IF( pStart < pDate, CAST(pDate AS DATETIME ), pStart )

and ends at...

IF( DATE(pEnd) > pDate, CAST(ADDDATE(pDate, 1) AS DATETIME), pEnd )

so pDate usage in seconds is given by...

UNIX_TIMESTAMP( IF( DATE(pEnd) > pDate, CAST(ADDDATE(pDate, 1) AS DATETIME), pEnd )) -
UNIX_TIMESTAMP( IF( pStart < pDate, CAST(pDate AS DATETIME ), pStart ));

To help keep the query as uncluttered as possible, encapsulate the above calculation in a stored function...

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS DaySeconds;
CREATE FUNCTION DaySeconds( pStart datetime, pEnd datetime, pDate date ) RETURNS INT
 RETURN UNIX_TIMESTAMP( IF( DATE(pEnd) > pDate, CAST(ADDDATE(pDate, 1) AS DATETIME), pEnd )) -
        UNIX_TIMESTAMP( IF( pStart < pDate, CAST(pDate AS DATETIME ), pStart ));

SELECT CEIL(DaySeconds('2008-1-1 10:05:00','2008-1-1 10:59:30','2008-1-1')/60) AS Mins;
+------+
| Mins |
+------+
|   55 |
+------+

Now for the query. To report usage per day over a period, we need a calendar table. For a test, make one for the first 100 days of the year 2008:

DROP TABLE IF EXISTS ints,calendar;
CREATE TABLE INTS(i int);
INSERT INTO ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE calendar(date date);
SET @n=0;
INSERT INTO calendar SELECT ADDDATE('2008-1-1',@n:=@n+1) FROM sys.ints a JOIN sys.ints b;

For a bit more about using a tiny ints table to generate a sequence of values, see the entry "Make a table of sequential ints". Now some test bookings data:

CREATE TABLE bookings( id INT PRIMARY KEY, resourceID int, startdate datetime, enddate datetime );
INSERT INTO bookings VALUES
(1,1,'2008-02-03 17:05','2008-02-03 19:00'),
(2,1,'2008-02-04 17:05','2008-02-04 18:00'),
(3,1,'2008-02-04 19:30','2008-02-04 20:00'),
(4,1,'2008-02-05 23:05','2008-02-06 01:00'),
(5,2,'2008-02-05 14:05','2008-02-05 15:00');

Obtain usage per day for resourceID=1 by running DaySeconds() against a JOIN of calendar and bookings:

SELECT
 c.date AS date,
 SUM( CEIL(( DaySeconds( b.startdate, b.enddate, c.date ) / 60 ))) AS 'Mins Used'
FROM calendar c
JOIN bookings b ON c.date BETWEEN DATE(b.startdate) AND DATE(b.enddate)
WHERE b.resourceID = 1
GROUP BY c.date;
+------------+-----------+
| date       | Mins Used |
+------------+-----------+
| 2008-02-03 |       115 |
| 2008-02-04 |        85 |
| 2008-02-05 |        55 |
| 2008-02-06 |        60 |
+------------+-----------+

To report over a date range, join the above to the calendar table on a date range:

SELECT c.date, IFNULL( sums.N, 0 ) AS 'Mins Used'
FROM calendar AS c
LEFT JOIN (
 SELECT
   c.date AS date,
   SUM( CEIL(( DaySeconds( b.startdate, b.enddate, c.date ) / 60 ))) AS N
 FROM calendar c
 JOIN bookings b ON c.date BETWEEN DATE(b.startdate) AND DATE(b.enddate)
 WHERE b.resourceID = 1
 GROUP BY c.date
) AS sums ON sums.date = c.date
WHERE c.date BETWEEN '2008-02-01' AND '2008-02-10';
+------------+-----------+
| date       | Mins Used |
+------------+-----------+
| 2008-02-01 |         0 |
| 2008-02-02 |         0 |
| 2008-02-03 |       115 |
| 2008-02-04 |        85 |
| 2008-02-05 |        55 |
| 2008-02-06 |        60 |
| 2008-02-07 |         0 |
| 2008-02-08 |         0 |
| 2008-02-09 |         0 |
| 2008-02-10 |         0 |
+------------+-----------+

{based on a contribution by Daniel Patterson]

Last updated 20 Oct 2014