- I want the Map<String hours, count > from the list of dates
- I also need to put the 1 hour transaction between dates
Ex In DB Value looks like this
2023-07-18 10:10:04.000
2023-07-18 10:20:04.000
2023-07-18 11:10:04.000
2023-07-18 11:30:04.000
2023-07-18 11:35:04.000
2023-07-18 12:10:04.000
2023-07-18 12:30:04.000
My expected result should be as follow as should calculate each 1 hour range count
Map result
<10-11 ,3>
<11-12 ,3>
<12-13 ,1>
I tried lot of examples provide by the forum but does’t work.
>Solution :
In case you are expecting <hour-range, count> in your output, following query might solve your problem –
SELECT group_concat(distinct CONCAT(HOUR(date_time), '-', HOUR(date_time) + 1)) AS hour_range, COUNT(date_time) AS count
FROM your_table
GROUP BY HOUR(date_time)
ORDER BY HOUR(date_time);