I have two simple tables:
indoor
id | timestamp | temp | humi
outdoor
id | timestamp | temp
And two selects which give me time and average temperature grouped by same hour for the last 24 hours:
SELECT 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')
ORDER BY timestamp ASC;
SELECT 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')
ORDER BY timestamp ASC;
And now what I need to do is to group those two results by same hour, with respect to possibility that there can be no records in indoor or outdoor table for whole hour, so I need to get:
time | avg_out_temp | avg_in_temp
11:00 | 12.5 | 21.4
12:00 | 13.9 | null
13:00 | null | 22.4
14:00 | 14.0 | 22.5
I'm working with MariaDB:
mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2
NULL
in both temp columns? That is, no data indoor or outdoor for that hour? If so, is it OK to skip that row in the output? – Rick James Sep 15 '19 at 18:18LEFT JOIN
from a table with 24 hours to nulls (or zeros, viaCOALESCE
). – Rick James Sep 15 '19 at 18:56