Grouping by time periods or timeslices in Mysql

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.

A jQuery plugin for the masses

Every wonder how to hit the ground running with a jQuery plugin? Here is the absolute complete basics for you (and me) to copy and paste…

First we need to define our Class. I’ll call mine Boom.


var Boom = function(element, options){
	this.init('boom', element, options);
};

So now we have something to focus our plugin at we can add a prototype method to it. This makes the ‘new Boom’ code super slick and gives us a better control over the ‘this’ variable because we can whack it inside a function…


Boom.prototype = {

	init:function(type, element, options)
	{
		// do stuff on create

		// makes it available almost anywhere as this.$element
		this.$element = $(element);

		// then maybe do something with the options
	},

	another_one:function()
	{
		// do some other clever stuff.
	}

};

And finally we need to connect it up to the jQuery bit…


$.fn.boom = function (options) {

	// return this makes it chainable
	return this.each(function ()
	{
		new Boom(this, options);
	});
};

$.fn.boom.Constructor = Boom;

Very basic, but worth having here for future use. You can now call it like so…


$('li.bang').boom();

Considering local, dev and live environments

When you develop a website for a client you should usually be considering three or even four (if you also include staging) environments where one code base may be used. Local, development and live.

In this case you may want the code to behave slightly differently depending on where it’s being used.

A good example I regularly come across is when and where to send any emails that a system generates. When you are testing locally or on a development server you certainly don’t want to send out emails to people who have no knowledge of the system.

Continue reading

Code 365

I do some sort of coding pretty much every day. It might be a tweak to a site’s design, some hardcore php Yii coding or even a bit of applescript on my mac, but I usually have something I can write, explain or rant about.

Code 365 is an attempt by me to publish a post to this blog each day through to 1st July 2013. It may be that some posts are very short and I queue several up occasionally, but the plan is generally for one each day.

I’ll be posting them to my @hjbme twitter account if you want to keep up with what I’m banging on about.

So coming up shortly, day 1 of 365. Let me know how I’m getting on.

Move your Sites folder in OS X with a SymLink NOT an Alias

Two hours of my life were wasted on this one, even though I’ve done it before. So…

If you want to move your ~/Sites folder into say Dropbox or AeroFS or Google Drive or SkyDrive and then you still want to point to it from its default location don’t use an alias.

I know an alias is only a ctrl-click away, but it means all sorts of pain.

Instead, move the folder to dropbox by dragging it (yes you can do this), then open Terminal and type:

ln -s ~/Dropbox/Sites ~/Sites

Replace Dropbox with whatever the folder of your service is called.

Restart Apache either by restarting Web Sharing in System Preference -> Sharing or by using Terminal and typing:

apachectl graceful

Cross your fingers and open a virtual host in your web browser. I fought with an Alias and all sorts of folder settings and httpd.conf lines to try and get it working, and then all I needed was to use a SymLink in its place.

Don’t worry about no longer having a ‘real’ ~/Sites folder. You don’t actually need one.

Cheers to James Galley, my desk neighbour for helping my brain to click on this one.