Group and count data by date and time with intervals of one hour

Question

I'm saving (PHP) user accesses to a mySQL dB as follows

|    IP Number    |     date    |   time  |
-------------------------------------------
|  193.12.143.145 |  2017-03-05 | 15:02   |
|  121.144.98.201 |  2017-03-05 | 15:38   |
|  188.34.12.167  |  2017-05-18 | 09:13   |
|  191.13.122.201 |  2017-05-18 | 09:51   |
|  167.98.101.157 |  2017-06-22 | 11:44   |
-------------------------------------------

I would like to group and count IPs by date and time with intervals of one hour so that the expected output would be:

|     date    |   time  | count |
---------------------------------
|  2017-03-05  | 15:00  |   2   |
|  2017-05-18  | 09:00  |   2   |
|  2017-06-22  | 11:00  |   1   |
---------------------------------

I tried the answers found on Stackoverflow but none produce the desired result. Notice infact that second table prints the hours as 15:00, 9:00, 11:00.

To who says that this is a duplicate question, I did tried the answer you linked but it did not output the asked result as instead it did the answer of Raymond Nijland below. — Nicero, Aug 07 '17 at 20:46

score 3 · Accepted Answer · answered Aug 07 '17 at 15:16

Create table/insert data

CREATE TABLE Table1
    (`IP Number` VARCHAR(14), `date` DATE, `time` VARCHAR(5))
;

INSERT INTO Table1
    (`IP Number`, `date`, `time`)
VALUES
    ('193.12.143.145', '2017-03-05', '15:02'),
    ('121.144.98.201', '2017-03-05', '15:38'),
    ('188.34.12.167', '2017-05-18', '09:13'),
    ('191.13.122.201', '2017-05-18', '09:51'),
    ('167.98.101.157', '2017-06-22', '11:44')
;

Query

SELECT 
   DATE
 , CONCAT(TIME_FORMAT(TIME, "%H"), ":00") AS TIME
 , COUNT(*) AS COUNT
FROM table1 
GROUP BY
   DATE
 , CONCAT(TIME_FORMAT(TIME, "%H"), ":00")

Result

date        time     count  
----------  ------  --------
2017-03-05  15:00          2
2017-05-18  09:00          2
2017-06-22  11:00          1

Group and count data by date and time with intervals of one hour

1 Answers1