Group results from two different tables on same hours

Question

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

Answer 1

Because both your queries return the same number (and type) of fields, the can be combinied with UNION ALL. The ORDER BY is only needed once at then end of the query.

To see the difference between indoor and outdoor this could be added to your query.

SELECT 'out' as door,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')
UNION ALL
SELECT 'in' as door,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;

Answer 2

Mysql has no full out join, this is why it hast to be simulated. So iz os quit ugly.

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;

I removed the sorting from Select from indoor and outdoor, because it is not needed. herer you find an examle

Of course, because of your where clause you have to have timestamps that are not older than one day, so the select query won't return nothing in the exqamle