MySQL Query to fill a gap of data

Question

I'm managing a DB which had some problems and I was asked to fill the gap where the database wasn't reading with previous data which was already there. So, the table sensors doesn't have data between 14-12-2023 and 19-04-2023. I need to get the data that is in the database X days before 14-12-2023 (X being the difference between 14-12-2023 and 19-04-2023) and replace the 'date' field from that data with the dates after 14-12-2023 up until 19-04-2023, whilst keeping the other fields. I tried to ask for help to our friend ChatGPT and he gave me something like this which is a start but far from solving the problem I have.

-- Step 1: Identify the start and end dates
SET @start_date = '2023-12-13';
SET @end_date = DATE_SUB(@start_date, INTERVAL 127 DAY);

-- Step 2: Retrieve data that exists before the start date
CREATE TEMPORARY TABLE PreviousData AS
SELECT *
FROM sensors
WHERE `date` <= @end_date;

-- Step 3: Construct the dynamic INSERT statement
SET @columns = (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sensors' AND COLUMN_NAME != 'date');
SET @sql = CONCAT('INSERT INTO sensors (`date`, ', @columns, ') ',
                  'SELECT DATE_ADD(\'', @start_date, '\', INTERVAL (t.n - 1) DAY), ', @columns, ' ',
                  'FROM (SELECT 1 AS n ', 
                          'UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION ',
                          'SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS t ',
                  'LEFT JOIN PreviousData pd ON pd.`date` = DATE_ADD(\'', @start_date, '\', INTERVAL (t.n - 1) DAY) ',
                  'WHERE DATE_ADD(\'', @start_date, '\', INTERVAL (t.n - 1) DAY) BETWEEN \'', @start_date, '\' AND \'', DATE_SUB(@start_date, INTERVAL 127 DAY), '\' ',
                  'AND pd.`date` IS NOT NULL;');

-- Step 4: Execute the dynamic SQL statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I believe the UNIONS part doesn't make lots of sense, at least like it is now. Thanks in advance!

Answer 1

I think all you are trying to do is copy the earlier chunk into the missing dates. I don't think you really need a temporary table. You can just do it like this:

insert into yourtable
select dt+127 dt,<all the other columns>
from yourtable
where dt between date'2023-12-14' - 127 and date'2023-12-14' - 1;

Since you are on MySQl dt + interval 127 day is what you would have to do to add days, and similar for the other date arithmetic.