MySQL – How to Sum Values by Time

dateMySQLtime

id_ |   date_     | hour_   | in_|out_|
|…. |   ….        |   ….    | …. | ….
|19 |   2018-12-12| 18:15:00| 1  |  3 |
|19 |   2018-12-12| 18:30:00| 1  |  0 |
|19 |   2018-12-12| 18:45:00| 2  |  4 |
|19 |   2018-12-12| 19:00:00| 2  |  3 |
|19 |   2018-12-12| 19:15:00| 2  |  0 |
|19 |   2018-12-12| 19:30:00| 2  |  4 |
|19 |   2018-12-12| 19:45:00| 1  |  1 |
|19 |   2018-12-12| 20:00:00| 1  |  1 |
|19 |   2018-12-12| 20:15:00| 0  |  0 |
|19 |   2018-12-12| 20:30:00| 1  |  0 |
|19 |   2018-12-12| 20:45:00| 0  |  2 |
|19 |   2018-12-12| 21:00:00| 1  |  4 |
|19 |   2018-12-12| 21:15:00| 3  |  0 |
|19 |   2018-12-12| 21:30:00| 3  |  2 |
|19 |   2018-12-12| 21:45:00| 0  |  1 |
|19 |   2018-12-12| 22:00:00| 0  |  0 |
|19 |   2018-12-12| 22:15:00| 1  |  3 |
|19 |   2018-12-12| 22:30:00| 1  |  0 |
|19 |   2018-12-12| 22:45:00| 2  |  4 |
|19 |   2018-12-12| 23:00:00| 2  |  3 |
|19 |   2018-12-12| 23:15:00| 0  |  0 |
|19 |   2018-12-12| 23:30:00| 0  |  0 |
|19 |   2018-12-12| 23:45:00| 0  |  0 |
|19 |   2018-12-13| 00:00:00| 1  |  2 |

http://sqlfiddle.com/#!9/9d8554/3

My query

SELECT id_, hour_,
SUM(in_), SUM(out_) FROM Table_ WHERE id_ = '19' AND date_ = '2018-12-07'
GROUP BY id_, HOUR(hour_)
ORDER BY hour_;

Hi db gurus,
Although I am a frequent visitor of stactoverflow, this is my first question as a newbie.
I've come to the realization that my query produces erroneus results because in reality the actual hour period starts at x:00:15 and ends at x+1:00:00.

I have two "problems". I need to add 4 periods of 1/4 hour starting from x:15:00 to x+1:00:00 and at the end of the day I need to include the 00:00:00 which is timestamped with the next day's date. All the help is apreciated.

Example: The results that are written in db at 9:00 am, actually the values belong to the previous period (08:45:00 to 09:00:00) so I can not use the SUM (in_) per hour.

Best Answer

I'd do this using a subquery to combine the date and time, then round down before aggregating the data. This way, it will include the '00:00' hour into the previous day which will prevent you from having to searching for two dates:

SELECT id_, HOUR(date_) + 1 AS hour_, SUM(in_) AS in_, SUM(out_) AS out_
  FROM 
  (
  SELECT id_, DATE_ADD(ADDTIME(date_, hour_), INTERVAL -15 MINUTE) AS date_
       , in_, out_
  FROM Table_ 
  ) x
WHERE id_ = '19'
  AND DATE(date_) = '2018-12-12'
GROUP BY id_, HOUR(date_)
ORDER BY HOUR(date_)

Here is an example of it in action: sqlfiddle.

Related Question