Let me describe this in a short and simple way. Here is the thing, I have a table in MySql it has 8 columns. The 1st column is "TimeStamp" which has some time readings of interval 10mins(say 8.00pm,8.10pm, 8.20pm and so on) for a chosen date range. The rest of the columns are giving datas of that particular timestamp. All these timestamp and datas are generated through ASP.Net Microservices and it is hosted through IIS.

Ok, now if some time intervals are missed by the service then the entire row is not shown.
Example: After the reading of 8.00pm,directly there is the reading of 9.00pm, that means we dont have the middle datas of 8.10pm, 8.20pm, 8.30pm and so on.

Now the thing is, I need to write a stored procedure in SQL that can generate all the missing timestamps in the same table, as well as which can fill the other columns as Null.

Here is a table for better understanding:

1. What I have:

2. What I want to do:

Can Any one please Help me out ? I know you guys can.

CodePudding user response：

I suggest that you create a table with all the timeslots available each day.

create table apts (
tslot varchar(10),    
Data1 int,    Data2   int);
insert into apts values
('08:00', 1234,   6758),  
('08:10', 5768,   4658),  
('09:00', 6754,   8674);

✓

3 rows affected

create table slots( tim varchar(10));
insert into slots values
('08:00'),('08:10'),('08:20'),
('08:30'),('08:40'),('08:50'),
('09:00');

✓

7 rows affected

select tim slot,data1,data2
from apts 
right join slots on tslot = tim;

slot  | data1 | data2
:---- | ----: | ----:
08:00 |  1234 |  6758
08:10 |  5768 |  4658
08:20 |  null |  null
08:30 |  null |  null
08:40 |  null |  null
08:50 |  null |  null
09:00 |  6754 |  8674

db<>fiddle here