DATETIME value missing in query output then autofill that missing values and get correct output

Question

When I take the data from a database with a one-second interval that time only available DateTime interval output count gets and missing some DateTime interval from the output.

I fill all missing DateTime intervals with count 0.

Example:

if start date: 2019-10-15 02:36:23 then

end date : 2019-10-15 03:36:22

get one-hour data with one-second intervals if that time not available any count then get 0 counts.

mysql> select start_date,count(id) as count from live_login where start_date >= '2019-10-15 02:36:23' GROUP BY UNIX_TIMESTAMP(start_date) DIV 1;
+---------------------+-------+
| start_date           | count |
+---------------------+-------+
| 2019-10-15 02:36:23 |     1 |
| 2019-10-15 02:36:24 |     1 |
| 2019-10-15 02:36:26 |     1 |
| 2019-10-15 02:36:55 |     1 |
| 2019-10-15 02:36:57 |     1 |
| 2019-10-15 02:37:08 |     1 |

Output need like this:

| start_date           | count |
+---------------------+-------+
| 2019-10-15 02:36:23 |     1 |
| 2019-10-15 02:36:24 |     1 |
| 2019-10-15 02:36:25 |     0 |
| 2019-10-15 02:36:26 |     1 |
| 2019-10-15 02:36:27 |     0 |
| 2019-10-15 02:36:28 |     0 |
| 2019-10-15 02:36:29 |     0 |
| 2019-10-15 02:36:30 |     0 |
| 2019-10-15 02:36:31 |     0 |
| 2019-10-15 02:36:32 |     0 |
| 2019-10-15 02:36:33 |     0 |
| 2019-10-15 02:36:34 |     0 |
| 2019-10-15 02:36:35 |     0 |

You can modify the function available in this answer - https://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates and join with it to get the required data. — Sudipta Mondal, Oct 16 '19 at 07:57

score 0 · Answer 1 · answered Oct 16 '19 at 10:44

You could make a virtual table containing all the seconds in the appropriate range, and then join them to the summary.
Try to modify the query like this :

    SELECT COUNT(id) AS count,
        listofseconds.login_date
    FROM (
        SELECT mintime + INTERVAL seq.seq SECOND AS login_date
        FROM (
                SELECT '2019-10-15 02:36:23' AS mintime,
                (SELECT DATE_ADD('2019-10-15 02:36:23', INTERVAL '59:59' MINUTE_SECOND)) AS maxtime
                FROM live_login
            ) AS minmax
        JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(SECOND,mintime,maxtime)
        ) AS listofseconds

    LEFT JOIN live_login ON listofseconds.login_date = SECOND(live_login.start_date)
    GROUP BY SECOND(listofseconds.login_date)
    ORDER BY SECOND(listofseconds.login_date)

This query will get an end_date of 2019-10-15 03:36:22 :

SELECT DATE_ADD('2019-10-15 02:36:23', INTERVAL '59:59' MINUTE_SECOND)

This query will get the seconds in the range :

        SELECT mintime + INTERVAL seq.seq SECOND AS login_date
        FROM (
                SELECT '2019-10-15 02:36:23' AS mintime,
                (SELECT DATE_ADD('2019-10-15 02:36:23', INTERVAL '59:59' MINUTE_SECOND)) AS maxtime
                FROM live_login
            ) AS minmax
        JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(SECOND,mintime,maxtime)

DATETIME value missing in query output then autofill that missing values and get correct output

1 Answers1