Fill in the missing hours with insert and recursive left join

I have a table with temperatures.
Sometimes the message was not received and the information is missing.

I need to fill the missing rows with NULL for every hour.

CREATE TABLE temp_total(
  id int(6) NOT NULL PRIMARY KEY,
  stamp timestamp NOT NULL,
  room_temp decimal(3,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE temp_total
  ADD UNIQUE KEY stamp(stamp),
  MODIFY id int(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO temp_total(stamp, room_temp) VALUES
('2019-07-21 19:00:00', '23.4'),
('2019-07-21 22:00:00', '22.7'),
('2019-07-23 02:00:00', '22.5'),
('2019-07-23 06:00:00', '22.4');

The expected result is an array of 36 rows.

I found this query to work fine.

SELECT stamp INTO @deb FROM temp_total ORDER BY stamp ASC LIMIT 1;
SELECT stamp INTO @fin FROM temp_total ORDER BY stamp DESC LIMIT 1;

WITH RECURSIVE all_hours(dt) AS (
  SELECT @deb dt
  UNION ALL
    SELECT dt + INTERVAL 1 HOUR FROM all_hours
    WHERE dt + INTERVAL 1 HOUR < @fin + INTERVAL 1 HOUR
)
-- INSERT IGNORE INTO temp_total(stamp, room_temp) 
SELECT d.dt stamp, t.room_temp
  FROM all_hours d
  LEFT JOIN temp_total t ON t.stamp = d.dt
  ORDER BY d.dt;

I want to use the result of SELECT with INSERT but I get this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO temp_total(stamp, room_temp)
SELECT d.dt stamp, t.room_temp
  ...' at line 7

When I uncomment the line.

DbFiddle

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You are almost there. With a small change in the syntax the query works as expected:

INSERT IGNORE INTO temp_total(stamp, room_temp) 
WITH RECURSIVE all_hours(dt) AS (
  SELECT @deb dt
  UNION ALL
    SELECT dt + INTERVAL 1 HOUR FROM all_hours
    WHERE dt + INTERVAL 1 HOUR < @fin + INTERVAL 1 HOUR
)
SELECT d.dt stamp, t.room_temp
  FROM all_hours d
  LEFT JOIN temp_total t ON t.stamp = d.dt
  ORDER BY d.dt;

See running example at db<>fiddle.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating