Harry Bailey

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 6 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.

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:

Continue Reading… »

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

Some database management tools refuse to admit you can add multiple primary key columns to one table, so you might have to do it with direct sql syntax ( deep intake of breath ).

Multiple primary keys make ‘ON DUPLICATE KEY UPDATE column = column + 100′ type queries a shed load more flexible.

Anyway, on with the code:

ALTER TABLE tablename ADD PRIMARY KEY (column_one, column_two)

And there you have it. Robert’s your fathers brother.