I have the following query to show the average temperature and relative humidity of the last two nights from the current date:
SELECT DATE(date) AS Fecha, ROUND(AVG(temperature),2) AS Temperatura, ROUND(AVG(humidity),2) AS Humedad FROM mt_meteorology WHERE DATE(date) BETWEEN '2016-09-27' AND '2016-09-28' AND (TIME(date) > '18:00:00' OR DATE(date) BETWEEN '2016-09-28' AND '2016-09-29' AND TIME(date) < '06:00:00') GROUP BY DATE(date);
For example today is
'2016-09-29' , to consult the data of the previous night I have it in mind in the following way:
The night of
'2016-09-28' (which is the previous one), includes data from
'2016-09-28' and data from
The same with the night of
'2016-09-27' , which includes data from
'2016-09-27' and data between
The problem is that the query does not work because what it does is averaging the data between the same dates regardless of the hours that I am setting.
How can I do to average the data of the last two nights with the previously mentioned requirements?