Average data from database

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

In my previous post I created a chart showing the hourly average temperature. The average was calculated from measurements taken every 5 seconds. These measurements are stored in a mySQL database together with a timestamp.

The question was how to extract the hourly average from this table to feed to the pChart scripting or to whatever code you like. I found the SQL solution below to make it happen.

    SELECT
      AVG(`value`) AS `Average`,
      YEAR(`timestamp`) AS `Year`,
      MONTH(`timestamp`) AS `Month`,
      Week(`timestamp`) AS `Week`,
      DAY(`timestamp`) AS `Day`,
      HOUR(`timestamp`) AS `Hour`,
    FROM `temperature`
    GROUP BY 'Year',`Month`,`Week`,`Day`,'Hour'`;
Extract average per hour
    SELECT
      AVG(`value`) AS `Average`,
      YEAR(`timestamp`) AS `Year`,
      MONTH(`timestamp`) AS `Month`,
      Week(`timestamp`) AS `Week`,
      DAY(`timestamp`) AS `Day`,
      HOUR(`timestamp`) AS `Hour`,
      Minute(`timestamp`) AS `Minute`
    FROM `temperature`
    GROUP BY 'Year',`Month`,`Week`,`Day`,'Hour', `Minute`;
Extract average per minute

It is possible to create a VIEW in your database which can be queried in your code. A VIEW looks like a table but it is the result of a SQL statement is given a name and can be used as a table in queries.

 

 

Leave a Reply