Fabian Sierra Fabian Sierra - 2 months ago 8
MySQL Question

Problems obtaining data night in PHP

I have the following query to display the average temperature and relative humidity of the last two nights from the current date:

SELECT Date(date) AS DateTemp,
Round(Avg(temperature), 2) AS Temperature,
Round(Avg(humidity), 2) AS Humedidity
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 obtain the data from the night before I do it as follows:

Night
'2016-09-28'
(the previous), comprise data between
18:00:00
and
23:59:59
from
'2016-09-28'
and data between
00:00:00
and
6:00:00
from
'2016-09-29'


The same with the night
'2016-09-27'
, comprise data between
18:00:00
and
23:59:59
from
'2016-09-27'
and data between
00:00:00
and
6:00:00
from
'2016-09-28'


The problem is that the query does not work because average data between the same dates regardless of the hours I'm by setting.

How I can do to average the data of the last two nights with the information given above.

Answer

The easiest way is probably to create a grouping column with its time shifted to make the night on the same 'day'

This effectively makes all times occur between 00:00 and 12:00 on the day you're querying by shifting it 6 hours forward.

Depending on how it executes, it may also be required to initially restrict with a WHERE clause directly on the date field.

SELECT 
    DATE(`date`) AS DateTemp,
    ROUND(AVG(`temperature`), 2) AS Temperature, 
    ROUND(AVG(`humidity`), 2) AS Humidity,
    `date` + INTERVAL 6 HOUR AS Grouping
FROM `mt_meteorology`
GROUP BY DATE(Grouping)
HAVING DATE(Grouping) IN ('2016-09-28', '2016-09-29')
    AND TIME(Grouping) < '12:00:00'

An equivalent, but probably faster query would be:

SELECT 
    DATE(`date`) AS DateTemp,
    ROUND(AVG(`temperature`), 2) AS Temperature, 
    ROUND(AVG(`humidity`), 2) AS Humidity 
FROM `mt_meteorology`
WHERE DATE(`date` + INTERVAL 6 HOUR) IN ('2016-09-28', '2016-09-29')
    AND TIME(`date` + INTERVAL 6 HOUR) < '12:00:00' 
GROUP BY DATE(`date` + INTERVAL 6 HOUR)

Since this one has a direct WHERE clause.