select version();

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

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;