I need Time Wise summary Report in mysql

Question

hai I have some data like Date Amount 30-01-2017 19:07:46 10 30-01-2017 19:08:52 20 30-01-2017 19:15:10 65 30-01-2017 19:16:58 85 30-01-2017 19:17:13 48 30-01-2017 19:20:05 165 30-01-2017 19:20:29 15 30-01-2017 19:38:53 98 30-01-2017 19:40:15 35 31-01-2017 21:45:18 56 31-01-2017 21:46:22 58 31-01-2017 22:05:27 12 31-01-2017 23:54:49 5 31-01-2017 23:55:53 84 31-01-2017 23:56:49 41 31-01-2017 23:59:01 65 And I need result using mysql in this format Time Intervel Amount 19:00:00-20:00:00 541 20:00:00-21:00:00 0 21:00:00-22:00:00 114 22:00:00-23:00:00 12 23:00:0-00:00:00 195 Please Help

Nitin Sontakke · Answer

In the output end time is coming again as start time of next row which would be incorrect. You are better of having output as 19:00:00 - 541 20:00:00 - 0 etc. For display purpose you can print it as follows: 19:00:00 - 19:59:59 - 541 20:00:00 - 20:59:59 - 0 If this is acceptable you are better of just by grouping by the hour part of the day and you are done.

Suvendu Shekhar Giri · Answer

Have you already tried any query for this?

Date	Amount
30-01-2017 19:07:46	10
30-01-2017 19:08:52	20
30-01-2017 19:15:10	65
30-01-2017 19:16:58	85
30-01-2017 19:17:13	48
30-01-2017 19:20:05	165
30-01-2017 19:20:29	15
30-01-2017 19:38:53	98
30-01-2017 19:40:15	35
31-01-2017 21:45:18	56
31-01-2017 21:46:22	58
31-01-2017 22:05:27	12
31-01-2017 23:54:49	5
31-01-2017 23:55:53	84
31-01-2017 23:56:49	41
31-01-2017 23:59:01	65

Time Intervel	Amount
19:00:00-20:00:00	541
20:00:00-21:00:00	0
21:00:00-22:00:00	114
22:00:00-23:00:00	12
23:00:0-00:00:00	195