Problems to consult PHP night data

Question

Problems to consult PHP night data

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

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 18:00:00 to 23:59:59 of '2016-09-28' and data from 00:00:00 to 06:00:00 of '2016-09-29'

The same with the night of '2016-09-27' , which includes data from 18:00:00 to 23:59:59 of '2016-09-27' and data between 00:00:00 to 06:00:00 of '2016-09-28'

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?

Thank you.

mysql sql php datetime date

asked by Fabian Sierra 29.09.2016 в 14:25

source

2 answers

1

So if the column is called date you must add single quotes, as in the following example:

SELECT Date('date') AS Fecha
FROM stack
WHERE 
    'date' BETWEEN '2016-09-27 18:00:00' AND '2016-09-28 06:00:00'
GROUP BY DATE(Fecha);

Do an exercise, but now the problem is that if your field is not timestamp or datetime , you can not get data with the time.

answered by 30.09.2016 в 00:42

Distribute List in list tuple (Haskell) Concatenate a string with a C ++ float

score 3 · Accepted Answer

As indicated by Luis in a comment , the parentheses are wrong. As the WHERE clause is now, you are getting the values that meet these conditions:

The date is between '2016-09-27' and '2016-09-28'

And
The time is greater than 6pm OR the date between '2016-09-28' and '2016-09-29' AND the time is lower that 6am.

I am going to put your question here with format so that the problem is better seen:

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);

When really what you want is this:

The date is between '2016-09-27' and '2016-09-28' AND the time is greater than 6pm;

OR
The date is between '2016-09-28' and '2016-09-29' AND the time is less than 6am.

So you need to move the parentheses a bit:

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);