Hourly counting MySQL-CodePudding

Since few days, I am trying to count records per hour from the MySQL database. I have a table with a lot of records and I have column DATE and column TIME where in DATE I have the date of the record in the format 2022-05-19, and in the column TIME, I have the time of the record in the format 14:59:38. What I am trying is to count every single day how many records per hour I have. Something like this:

DATE       HOUR     PCS
22-05-18   06-07    11
22-05-18   08-09    20
.........  .....    ..
.......    21-22    33

I have tried many different ways but no success. For example:

SELECT 'Date', count(*) FROM `root4` 
where 
    DATE between '2022-05-01' and '2022-05-1' AND 
    TIME BETWEEN '06:11:05' AND '07:11:05'

Any help is highly evaluated.

CodePudding user response：

I would recommend not using reserved words for columns, as you will have to escape them a lot. https://dev.mysql.com/doc/refman/8.0/en/keywords.html

If you stored TIME as a timestamp, you can extract the hour using the HOUR() function and group by that:

SELECT
  `DATE`, 
  HOUR(`TIME`) AS `HOUR`, 
  COUNT(1)
FROM your_table
GROUP BY 
  `DATE`, 
  HOUR(`TIME`)

If you happened to store it as text you can use REGEXP_SUBSTR to get the hour value from your time string.

SELECT
  `DATE`, 
  CAST(REGEXP_SUBSTR(`TIME`, '[0-9] ') AS UNSIGNED) AS `HOUR`, 
  COUNT(1)
FROM your_table
GROUP BY 
  `DATE`, 
  CAST(REGEXP_SUBSTR(`TIME`, '[0-9] ') AS UNSIGNED)

You can format your HOUR column how you want, like displaying 01-02 instead of 1 by using CONCAT, but this is your basic setup.