Fabian Sierra - 1 year ago 63
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.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download