0

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
  • 1
    Specify MySQL version – Akina Sep 15 at 12:09
  • Is it possible to have a row with 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 at 18:18
  • @RickJames yes it's possible, in this case it can be skipped. – elementstyle Sep 15 at 18:20
  • @elementstyle - Or you can LEFT JOIN from a table with 24 hours to nulls (or zeros, via COALESCE). – Rick James Sep 15 at 18:56
0

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.