How to find all the timestamp values interval by each minute between the two timestamp records in my sql database

Question

I have a table having three fields Id (Integer) - Unique, Open Date (Datetime), Close Date(DateTime) in my mysql database:

Id  Open Date                  Close Date
1   2019-07-03 16:28:39.497    2019-07-04 16:28:39.497
2   2019-07-04 15:28:39.497    2019-07-05 19:28:39.497
…..N        

I want to calculate the all the timestamps between open date and close date with an interval of each minute.

So the final output I want is like this:

Id  Open Date                 Close Date                TimeStamp Range
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:29:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:30:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:31:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   …..........................
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-04 16:27:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:29:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:30:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:31:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   ….................................
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-05 19:27:00.0000
N   …............................   …...........................    …......................................

Would someone help me to write the query for this that will be supported in my sql?

Answer 1

With the help of https://stackoverflow.com/a/45992247/7616138 to generate a series in MySQL I fiddled around to produce this:

Assuming your table is called entries, the following query produces the result you asked for. Please not, that this query is not very efficient as it uses cross joins to build up the series of minutes. If your intervals are bigger, you may need to extend the cross joins.

SELECT 
    a.*, 
    DATE_ADD(DATE_SUB(a.open_date, INTERVAL SECOND(a.open_date) SECOND), INTERVAL gen_time MINUTE) AS gen_date_time
FROM 
    entries AS a
    LEFT JOIN
        (
            SELECT
                m3 * 1000 + m2 * 100 + m1 * 10 + m0 AS gen_time
            FROM
                (SELECT 0 m0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m0,
                (SELECT 0 m1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m1,
                (SELECT 0 m2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m2,
                (SELECT 0 m3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS m3
            ORDER BY
                gen_time asc
        ) AS b ON (DATE_ADD(a.open_date, INTERVAL gen_time MINUTE) <= a.close_date)
ORDER BY
    a.id,
    gen_date_time

Explanation:

Each line like SELECT 0 m0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 produces the numbers from 0 to 9. Cross joining several of these lines together gives every possible combination of these numbers. We use each line to produce a specific digit of the number of minutes to add to open_date (m3 * 1000 + m2 * 100 + m1 * 10 + m0). This series of minutes is joined to the entries table using only as many minutes as fit in the interval (DATE_ADD(a.open_date, INTERVAL gen_time MINUTE) <= a.close_date). In the SELECT the open_date is rounded to the minute (DATE_SUB(a.open_date, INTERVAL SECOND(a.open_date) SECOND)) and the number of minutes is added (DATE_ADD(..., INTERVAL gen_time MINUTE)).

Assumed schema and sample data:

CREATE TABLE entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  open_date TIMESTAMP,
  close_date TIMESTAMP
);

INSERT INTO entries (open_date, close_date) VALUES
  ("2019-07-03 16:28:39.497", "2019-07-04 16:28:39.497"),
  ("2019-07-04 15:28:39.497", "2019-07-05 19:28:39.497");