2
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.

1

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.

1
  • 1
    Hey @Mr.Brownstone it seems you've really nailed it. Can't thank you enough. Elegant and simple! – JBZ Dec 14 '18 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.