Search code examples
mysqlaveragetemperature

MySQL Average temperature specifying number of days

I have a table called data with columns:

  • date [timestamp]
  • temperature [int(11)]
  • humidity [int(11)]

(for example ... date: 2016-06-11 10:07:22, temperature: 22, humidity: 50)

I get a new entry every 60 seconds.

Im wondering if its possible to define a query that will return the average temperature and humidity of every hour for a specified number of days. I need the hour average for yesterday (1 day), last week (last 7 days) and last month (last 30 days).

Solution

  • To get an average value per each hour, we need to GROUP BY the rows by the day + hour.

    Sample query:

     SELECT AVG(temperature), DATE(date), HOUR(date) FROM data GROUP BY DATE(date), HOUR(date)