No announcement yet.

grouping of data?

  • Filter
  • Time
  • Show
Clear All
new posts

  • grouping of data?

    Hi there!

    I have one challenging task: For two years now, I'm collecting weather data for my hometown.

    All data is saved to a MySQL database, each entry has an unique ID and a Unix timestamp (and the temperature and so on).

    The script was set to save the data every 15 minutes, some months ago I changed it to 5 minutes. So far, I have a lot of data and I want to group this data.

    Instead of having 100,000 of indivual entries, I want to have the average temperature for each day.

    The question is: How can I group the data? Is there a MySQL query that does the work for me?

    I was thinking about GROUP BY but that would only work if I had unique timestamps.
    That's the end of that!

  • #2
    Try this:

    SELECT * , AVG(temperature) AS avgtemp, FLOOR( timestamp /86400 ) AS days
    FROM wheathertable 
    GROUP BY days

    Scott me up, Beamie!


    • #3
      Thanks, I'll give it a try.

      I came up with:
      SELECT *, DATE_FORMAT(FROM_UNIXTIME(zeit), '%d%m%Y') AS time, 
      AVG(temperatur_echt) AS tempdurchschnitt, 
      MIN(temperatur_echt) AS tempmin,
      MAX(temperatur_echt) AS tempmax
      FROM wetter GROUP BY(time) ORDER BY id ASC
      That's the end of that!


      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.