CODEWITHSUNDEEP
phpmysqlsqldatedatetime
Fabian Sierra
Fabian Sierra

Reputation: 766

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.

Upvotes: 0

Views: 67

Answers (2)

calcinai
calcinai

Reputation: 2617

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.

Upvotes: 1

ivant87
ivant87

Reputation: 70

What if you use the WHERE statement like this one: EDIT: Perhaps because your date columns is with other name ?

WHERE DateTemp BETWEEN '2016-09-27 18:00:00' AND '2016-09-28 06:00:00'
AND   DateTemp BETWEEN '2016-09-28 18:00:00' AND '2016-09-29 06:00:00'

Upvotes: 1

Related Questions