MySQL: GROUP BY custom date interval

Question

Situation: a table where every minute X energy device saves its consumption. I have to calculate the daily consumption (at hourly intervals, 00 - 01 - 02 - 03 - 04 ..... 23) of a single device on a certain day (to create a simple consumption hourly chart).

id | date                |  total | id_device
---------------------------------------------
0  | 2018-10-01 00:01:00 | 100    | 1
---------------------------------------------
1  | 2018-10-01 00:01:00 | 101    | 2
---------------------------------------------
2  | 2018-10-01 00:02:00 | 110    | 1
---------------------------------------------
3  | 2018-10-01 00:02:00 | 105    | 2
---------------------------------------------
.. | 2018-10-01 23:59:00 | 200    | 1
---------------------------------------------
.. | 2018-10-01 23:59:00 | 1000   | 2

I did this to calculate the hourly consumption

SELECT CONCAT(IF(HOUR(`date`) < 10 , '0','') , HOUR(`date`)) AS `HH`, (MAX(`total`) - MIN(`total`)) AS `total` 
FROM `mytable`
WHERE `date` BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND DATE_FORMAT(?, '%Y-%m-%d 23:59:59') AND id_device = ?
GROUP BY `HH`

Result

HH | total
----------
00 | 100
01 | ...
.. | ...
23 | ...

This query correctly returns the totals (if an hour has no save it is not displayed in the query, no problem).

But the GROUP BY works in the following way

• 00 interval : 00:00 - 00:59
• 01 interval : 01:00 - 01:59
• 02 interval : 02:00 - 02:59
• ....
• 23 interval : 23:00 - 23:59

But I need this, otherwise the time calculation is incorrect.

• 00 interval : 23:59 (previous day) - 00:59
• 01 interval : 00:59 - 01:59
• 02 interval : 01:59 - 02:59
• ....
• 23 interval : 22:59 - 23:59

Is it possible to have this kind of intervals?

---

PS

For the case 00, I know I have to change the search starting from the last value recorded the day before, but that's not my problem now. I would do it that way:

WHERE 'date' BETWEEN 
  COALESCE((SELECT 'date' FROM 'mytable' WHERE 'date' < DATE_FORMAT(?, '%Y-%m-%d 00:00:00') ORDER BY 'date' DESC LIMIT 1), DATE_FORMAT(?, '%Y-%m-%d 00:00:00')) 
  AND DATE_FORMAT(?, '%Y-%m-%d 23:59:59')

---

UPDATE

DB Fiddle example. There are 3 devices, every single device has 5 days of record.

https://www.db-fiddle.com/f/ddvVguupi74TQjQ6yWJUzB/3

Actual result (id_device 1 , date 2018-10-03):

HH  total
00  354
01  354
02  354
03  354
04  354
05  354
06  354
07  354
08  354
09  354
10  354
11  354
12  354
13  354
14  354
15  354
16  354
17  354
18  354
19  354
20  354
21  354
22  354
23  354

Expected result :

HH  total
00  360
01  360
02  360
03  360
04  360
05  360
06  360
07  360
08  360
09  360
10  360
11  360
12  360
13  360
14  360
15  360
16  360
17  360
18  360
19  360
20  360
21  360
22  360
23  360

Answer 1

This is a lot simpler:

SELECT 
  DATE_ADD(DATE(`date`), INTERVAL HOUR(`date`) HOUR) as date_hour,
  MAX(`value`) -
  COALESCE((SELECT MAX(ta.`value`) FROM test ta WHERE ta.id < MIN(t.id)),0) as this_hour_consumption
FROM
  test t
GROUP BY
  DATE_ADD(DATE(`date`), INTERVAL HOUR(`date`) HOUR);

How it works:

• Strips off the minutes and seconds from the time, reducing every date to just the date and the hour

• Finds the max consumption in that hour

• Finds the max consumption value whose id is less than the min id in the hour (i.e. the max consumption value from the previous hour

• Subtracts the max consumption this hour from the max consumption last hour to give the consumption for this hour

There are other ways to skin this cat:

SELECT 
  DATE_ADD(DATE(a.`date`), INTERVAL HOUR(a.`date`) HOUR) the_hour,
  SUM(a.`value` - b.`value`) sum_consumption_this_hour
FROM 
  test a INNER JOIN test b on a.id = b.id + 1
GROUP BY
  DATE_ADD(DATE(a.`date`), INTERVAL HOUR(a.`date`) HOUR);

This works by:

• join the data to itself on id = id-1 (gets you a "this" table and a "previous" table)
• work out the change in consumption between successive records (this.value - previous.value)
• and then group them up by the hour (same technique as above, reduce the datetime to a date, and add on the HOUR()s ) and sum them

Edit:

Modified the first query to work with your revised sample data:

SELECT 
  LPAD(HOUR(`date`), 2, '0') as date_hour,
  MAX(`total`) -
  COALESCE((SELECT MAX(ta.`total`) FROM test ta WHERE ta.id < MIN(t.id) and ta.id_device = t.id_device),0) as this_hour_consumption
FROM
  test t
WHERE 
  DATE(`date`) = '2018-10-03' and id_device = 1 --or DATE BETWEEN x AND y if this form doesn't use your index...
GROUP BY
  LPAD(HOUR(`date`), 2, '0');

The significant change is: added a WHERE clause to restrict the rows to just the subset day and device. Coordinated the select max() to consider the device too

Also reformatted the hour output, but that's cosmetic

Edit2: Here's another way to do it, simulating the LAG function (upgrade to MySQL 8!)

SET @prev=(SELECT MAX(`total`) FROM test WHERE id_device = 1 and DATE(`date`) < '2018-10-03');
SELECT
  date_hour,
  SUM(curr_tot - prev_tot) as hour_consumption
FROM
(
  SELECT 
    LPAD(HOUR(`date`), 2, '0') as date_hour,
    @prev as prev_tot,
    @prev:=`total` as curr_tot
  FROM
    test t
  WHERE 
    DATE(`date`) = '2018-10-03' and id_device = 1 /*or DATE BETWEEN x AND y if it uses your index...*/
  ORDER BY
    `date`
) a
GROUP BY
  date_hour;

• We get the max total from the previous date for that device and store it to a variable

• We pull the records from the table for the desired date, order them, then skip through them first outputting the variable contents (which for any given row is the previous row's value) then updating the variable to this row total (and it gets output after assignment)

• This gives a subquery that for every row shows the current total and the previous total. We subtract them to give the delta, and group/sum the deltas, per hour

Answer 2

• Using MINUTE() function, you can determine if the minute value is 59.
• If it is equal to 59, you can increment the hour value to consider by 1.
• Use LPAD() function instead to add leading '0', upto maximum string size of 2.

You can use the following to determine HH:

LPAD(IF(
        MINUTE(`date`) = 59, 
        HOUR(`date`) + 1, 
        HOUR(`date`)
       ), 
     2, 
     '0'
    ) AS `HH`

But, the problem in hand is that you have clashing rows for two groups at 59 minutes. For eg: a row at 01:59:23 has to be considered in the 01 as well as 02 group. With a simple group by it is not possible. So, one approach is consider two different Select statements to get HH value for a row. One select will consider the original hour value, and another would consider hour + 1 for 59 minute case. But then, we will have duplicates for all the other minutes (except 59th minute). This duplicate problem can be solved by utilizing Union statement.

Afterwards, you can use the Unionized result-set as a Derived table, and do a simple Group By.

So, you can try the following (you will still have to handle the edge cases when a day changes):

SELECT 
  dt.HH, 
  (MAX(dt.total) - MIN(dt.total)) AS total 
FROM
    (
      SELECT LPAD(IF(MINUTE(t1.date) = 59, HOUR(t1.date) + 1, HOUR(t1.date)), 2, '0') AS HH, 
             t1.total 
      FROM mytable AS t1
      WHERE t1.date BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND
                            DATE_FORMAT(?, '%Y-%m-%d 23:59:59')

      UNION 

      SELECT LPAD(HOUR(t2.date), 2, '0') AS HH, 
             t2.total 
      FROM mytable AS t2
      WHERE t2.date BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND
                            DATE_FORMAT(?, '%Y-%m-%d 23:59:59')
    ) AS dt
GROUP BY dt.HH

Answer 3

Just shift your date column by one minute:

SELECT CONCAT(IF(HOUR(`date`) < 10 , '0','') , HOUR(`date`)) AS `HH`, (MAX(`total`) - MIN(`total`)) AS `total` 
FROM (
    SELECT DATE_ADD(`date`, INTERVAL 1 MINUTE) `date`, `total`
    FROM `mytable`
    WHERE `date` BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND DATE_FORMAT(?, '%Y-%m-%d 23:59:59')
) a GROUP BY `HH`