grouping of data?

[MrNase]

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.

[Boothby]

Try this:

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

[MrNase]

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