clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1582768 fiddles created (20261 in the last week).

select version();
version()
5.7.27
 hidden batch(es)


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
 hidden batch(es)


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
20:00 21.0
21:00 21.0 16.0
22:00 20.0 16.0
23:00 20.0 16.0
 hidden batch(es)