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

CodePudding user response：

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.