MySQL- fetch the records count per minute

Question

I need to fetch the records count per minute from the MySQL logging table. Please help me to achieve it.

The count should be consider for every one minute. If there are no records logged in that minute it should display the count as 0.

Please find the below table and records set expecting as below.

Table : Table Name: tlogging

|----------------------|---------------|-----------------|
|  InTime              |   Employee    |    Country      |
|----------------------|---------------|-----------------|
|  2019-09-18 10:00:01 |     A         |        MY       |
|  2019-09-18 10:00:10 |     B         |        UK       |
|  2019-09-18 10:00:44 |     C         |        US       |
|  2019-09-18 10:00:53 |     D         |        CN       |
|  2019-09-18 10:02:22 |     E         |        JP       |
|  2019-09-18 10:03:04 |     F         |        SG       |
|----------------------|---------------|-----------------|

Record Set

|----------------------|---------------|
|  InTime              |   Count(*)    |
|----------------------|---------------|
|  2019-09-18 10:00:00 |     4         |
|  2019-09-18 10:01:00 |     0         |
|  2019-09-18 10:02:00 |     1         |
|  2019-09-18 10:03:00 |     1         |
|----------------------|---------------|

Well, it is also important to read [How To Ask](https://stackoverflow.com/help/how-to-ask). A good question attracts good (and experienced) users to answer/suggest efficient and practical solutions. Especially questions which show some genuine efforts by the asker, and not just a dump to do things for him. SO is a collaborative platform meant to encourage learning. Nevermind though, since you want me to "stop teaching", I will stay away (even if there is an answer). — Madhur Bhaiya, Sep 18 '19 at 14:03
See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Sep 18 '19 at 17:28

score 0 · Answer 1 · answered Sep 18 '19 at 13:36

0

Here is one attempt:

SELECT TIME_FORMAT(SEC_TO_TIME(((TIME_TO_SEC(toStampActual)) DIV 60) * 60), '%H:%i:%s') AS InTime
    , COUNT(1) AS RecordCount
FROM tlogging
GROUP BY TIME_FORMAT(SEC_TO_TIME(((TIME_TO_SEC(toStampActual)) DIV 60) * 60), '%H:%i:%s')

Assist from:

Rounding a DATETIME value in MySQL to nearest minute.

answered Sep 18 '19 at 13:36

kjmerf

4,275
3
21
29

Thanks for the answer @kjmerf. This query is giving the total records per minute but if there are no records for a minute, it should give the value as 0. but it's not giving like that. For example from the above record set, for 2019-09-18 10:01:00 since there are no records, it should display the count as 0. But it's not displaying with your query. – Suryateja Sep 18 '19 at 14:27
For that you'd have to create a date dimension like the one described here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/. Then write your query as a left join from the date dimension table to the tlogging table. – kjmerf Sep 18 '19 at 18:09

score 0 · Answer 2 · answered Sep 18 '19 at 13:36

0

You can try this query for mysql

SELECT
    InTime,  -- not sure about that
    count(*)
FROM table
WHERE your_condition
GROUP BY 
UNIX_TIMESTAMP(InTime) DIV 60, Employee

answered Sep 18 '19 at 13:36

ANIK ISLAM SHOJIB

3,002
1
27
36

Instead of giving me the per minute count, this query is giving me the total count logged for that minute. – Suryateja Sep 18 '19 at 14:34

score 0 · Answer 3 · answered Sep 18 '19 at 17:58

0

I think you need something like this

select date_format(InTime, '%Y-%m-%d %H:%i:00') as InTime, count(*)
from tlogging
group by date_format(InTime, '%Y-%m-%d %H:%i:00');

answered Sep 18 '19 at 17:58

Radagast

5,102
3
12
27

MySQL- fetch the records count per minute

3 Answers3