Sometimes when you pull back a whole load of results from a database table and order them by their timestamps you might want to either sum any result which happened at a time close to each other or you might want to only bring back one type of something within a period of time to stop that item flooding your results.
I’ve used this technique a couple of time now. The first time was to make lots and lots or data fit onto a relatively small graph, so I needed some form of average. The second time was because if the same event happened in a set period of time I only wanted to know about it once.
The query looks something like this once we include our timeslices / time periods in our select and then follow it up by grouping by them.
SELECT name,
type,
value,
ROUND((CEILING(UNIX_TIMESTAMP(`timestamp`) / 600) * 600)) AS timeslice
FROM reading
GROUP BY timeslice
Here I have selected timeslices of 10 minutes (600 seconds) but you can group larger or shorter periods of time if you require them.
So there you have it, grouping by timeslices for summing readings or limiting duplicates in results.