Filling gaps between start end timestamp

Question

I'm trying to find a query to fill gaps between a start and end timestamp. The gaps/intervals should be minute wise. My goal is to get a time-series to create a diagram based on the results to visualize a time period for each record.

Sample date

    create table tbl(
      id int, value int, dateStart timestamp, dateEnd timestamp
    );

    insert into tbl values
    (1, 4, '2020-01-04 16:29:00', '2020-01-04 16:33:00'),
    (2, 3, '2020-01-04 18:29:00', '2020-01-04 18:33:00'),
    (3, 3, '2020-07-16 09:10:00', '2020-07-16 09:12:00');


| id  | value    | dateStart           | dateEnd             |
| --- | -------- | ------------------- | ------------------- |
| 1   | 4        | 2020-01-04 16:29:00 | 2020-01-04 16:33:00 |
| 2   | 3        | 2020-01-04 18:29:00 | 2020-01-04 18:30:00 |
| 3   | 3        | 2020-07-16 09:10:00 | 2020-07-16 09:12:00 |
| .   | .        | .                   | .                   |
| .   | .        | .                   | .                   |
| .   | .        | .                   | .                   |

Expected Output

| id  | value    | date                |
| --- | -------- | ------------------- | 
| 1   | 4        | 2020-01-04 16:29:00 |
| 2   | 4        | 2020-01-04 16:30:00 |
| 3   | 4        | 2020-01-04 16:31:00 |
| 4   | 4        | 2020-01-04 16:32:00 |
| 5   | 4        | 2020-01-04 16:33:00 |
| 6   | 3        | 2020-01-04 18:29:00 |
| 7   | 3        | 2020-01-04 18:30:00 |
| 8   | 3        | 2020-07-16 09:10:00 |
| 9   | 3        | 2020-07-16 09:11:00 |
| 10  | 3        | 2020-07-16 09:12:00 |

I'm not very familiar with CTE and cursors, yet. Any suggestion/help how to solve it best?

MariaDB 10.4.11, but no problem to use mysql or any other mariaDB/mysql version. — sherwood, Feb 03 '20 at 15:32
Seriously consider handling issues of data display in application code — Strawberry, Feb 03 '20 at 15:48
Data display is handled by a third party solution, the current data needs to be transformed. — sherwood, Feb 03 '20 at 16:18

score 1 · Accepted Answer · answered Feb 03 '20 at 16:19

Here is one (rather inefficient) way to do it is to use a recursive query:

with recursive cte as (
    select id, value, dateStart, dateEnd from tbl
    union all
    select id, value, dateStart + interval 1 minute, dateEnd 
    from cte
    where dateStart < dateEnd
)
select row_number() over(order by id) id, value, dateStart date 
from cte
order by id

Demo on DB Fiddle:

id | value | date               
-: | ----: | :------------------
 1 |     4 | 2020-01-04 16:29:00
 2 |     4 | 2020-01-04 16:30:00
 3 |     4 | 2020-01-04 16:31:00
 4 |     4 | 2020-01-04 16:32:00
 5 |     4 | 2020-01-04 16:33:00
 6 |     3 | 2020-01-04 18:29:00
 7 |     3 | 2020-01-04 18:30:00
 8 |     3 | 2020-07-16 09:10:00
 9 |     3 | 2020-07-16 09:11:00
10 |     3 | 2020-07-16 09:12:00

Awesome, thanks! What would be a more efficient way, stored procedures? — sherwood, Feb 03 '20 at 18:35

Rick James · Answer 2 · 2020-02-10T04:16:03.217

Much more efficient is to use MariaDB's "seq" pseudo tables:

( SELECT '2000-01-01' + INTERVAL seq DAY AS dy FROM seq_0_to_36524 )

will generate a century's worth dates in 3 ms. Yes, milliseconds.

SELECT s.dy
    FROM ( SELECT '2020-01-04 18:29:00' + INTERVAL seq MINUTE AS dy
                                      FROM seq_0_to_1440 ) AS s
    WHERE s.dy <= '2020-01-04 18:33:00';

+---------------------+
| dy                  |
+---------------------+
| 2020-01-04 18:29:00 |
| 2020-01-04 18:30:00 |
| 2020-01-04 18:31:00 |
| 2020-01-04 18:32:00 |
| 2020-01-04 18:33:00 |
+---------------------+
5 rows in set (0.00 sec)

That generates a day's worth of dates (in 1ms), then limits to the desired range. In your case, you would LEFT JOIN to the your sparse table.

Filling gaps between start end timestamp

2 Answers2