MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.7.39


CREATE TABLE indoor
    (`Id` int, `timestamp` timestamp, `temp` Decimal, `Humi` decimal)
;
  
INSERT INTO indoor
    (`Id`, `timestamp`, `temp`, `humi`)
VALUES
    (1, TIMESTAMP( '2019-09-14 18:11:07'), '21.1', '50.9'),
    (2, TIMESTAMP( '2019-09-14 19:11:07'), '20.9', '50.9'),
    (3, TIMESTAMP( '2019-09-14 20:11:07'), '20.9', '50.9'),
    (4, TIMESTAMP( '2019-09-14 21:11:07'), '20.7', '50.9'),
    (6, TIMESTAMP( '2019-09-14 22:11:07'), '19.7', '50.9'),
    (6, TIMESTAMP( '2019-09-14 23:11:07'), '19.6', '50.9')
    
;
CREATE TABLE outdoor
    (`Id` int, `timestamp` timestamp, `temp` Decimal)
;
  
INSERT INTO outdoor
    (`Id`, `timestamp`, `temp`)
VALUES
    (1, TIMESTAMP( '2019-09-14 18:11:07'), '17.1'),
    (2, TIMESTAMP( '2019-09-14 19:11:07'), '16.9'),
    (4, TIMESTAMP( '2019-09-14 21:11:07'), '15.8'),
    (6, TIMESTAMP( '2019-09-14 22:11:07'), '15.7'),
    (6, TIMESTAMP( '2019-09-14 23:11:07'), '15.6')
    
;
th sql_mode=only_full_group

Records: 6  Duplicates: 0  Warnings: 12



Records: 5  Duplicates: 0  Warnings: 5



You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'th sql_mode=only_full_group' at line 1


SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp
FROM 
((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) ti LEFT JOIN 
(SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon 
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) tou on ti.timecon = tou.timecon)
union
SELECT IFNULL(ti.time,tou.time) time,ti.avg_in_temp,tou.avg_out_temp
FROM 
((SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) ti RIGHT JOIN 
(SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d %H') timecon 
,MIN(DATE_FORMAT(`timestamp`, '%H:00')) AS time
, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE `timestamp` >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H')
) tou on ti.timecon = tou.timecon)
ORDER BY time ASC;


time
avg_in_temp
avg_out_temp