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.

NB: This is a really really old post. See a newer better one about inserting and updating with random string.

Adding a new column to a table which needs to contain a different random hash for each row?

Mysql can easily generate md5 strings and update all rows in the table for you

UPDATE thetable
SET thecolumn = MD5(RAND())
WHERE thecolumn IS NULL

You can delete from multiple tables (which don’t have foreign keys set up) using one query if you create it using the following syntax:

UPDATE `table_name` 
SET `field_name` = (SELECT CASE `field_name` WHEN 1 THEN 0 ELSE 1 END) 
WHERE `id_column` = 1

Or with text strings

UPDATE `table_name` 
SET `field_name` = (SELECT CASE `field_name` WHEN 'foo' THEN 'bar' ELSE 'foo' END) 
WHERE `id_column` = 1