SQL: Group datetime by an arbitrary time interval

Yesterday, a co-worker came to me with a puzzle, he wanted to write a query that would group records by intervals of 5, 10, 15 minutes, ideally just passing a parameter to the query to specify the time interval, I sat down and after 5 minutes I came up with the solution:

declare @interval int

set @interval = 5

select datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

, count(*)

from thetable

group by datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

The trick is highlighted, though it looks like the division and multiplication eliminate each other, what is really happening is an integer division, which, multiplied by the same number, gives you the right intervals:

here's the (minutes) values pattern for an interval of 5


minute divided by 5 multiplied by 5
0 0 0
1 0 0
...
5 1 5
6 1 5
...
10 2 10
11 2 10
...
from there you can include more fields in your select criteria, make it prettier, etc

declare @interval int

set @interval = 5

select convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

, count(*)

from the_table

group by convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

which produces something like:

11/12/10 10:10    28

11/12/10 10:15    11

11/12/10 10:20    57

hope this is useful

VirtualBox VM won't release the mouse

Just a quick post to share this, I'm playing with VirtualBox on my OpenSuse 11.3, installing Windows XP on a VM, I decided to click inside the VM while it was still installing, hoping that the right [ctrl] key would release the mouse, but that didn't work, Googled a bit with no good results, there's many related articles about this issue, but no solution, I was ready to give up so I hit ctrl+alt+del and what do you know, that released the mouse :)

hope this helps someone out there

Apple intuitiveness: Upgrading OS X results in downgraded components

I just upgraded my OS X to Snow Leopard (yeah, a bit late), first of all I just can't wrap my head around the idea that OS X runs on a single hardware configuration (ok, maybe 3) yet it took over an hour to install, while Windows runs on millions of hardware configurations and can install in 15 mins (as in, ready to start using it), I just can't find that intuitive, it doesn't make sense; but anyway, one of the 3? 'big' features in this update is Safari 4, before installing I noticed I already had Safari 5, so I went ahead and did the install, to my surprise when it finally finished I had now Safari 4!! WTF? over an hour upgrading and they couldn't check if the components had a higher version than the one being installed? To be honest I was afraid to check if there was something else that got downgraded. Upgrading OS X results in a downgrade of it's components! how's that intuitive?
To top if off, after upgrading I went to check for updates, there were 776MB of updates (and these are the people who make fun of Windows updates?), and sure enough, Safari 5 was on the list.

So, to recap: I had Safari 5, I upgraded OS X, it got downgraded to Safari 4, then I had to get a 776MB update to get back to where I was before the upgrade. Intuitive or not, I'll let you be the judge xD.

Update. after another 10 minutes, more updates came up, this time an additional 200MB, requiring, of course, that I reboot again.

MissingManifestResourceException was unhandled

Could not find any resources appropriate for the specified culture or the neutral culture.  Make sure "PostProcessing.ConfigurationForm.resources" was correctly embedded or linked into assembly "PostProcessing" at compile time, or that all the satellite assemblies required are loadable and fully signed.

There are many articles related to this one but they all point to a different solution than the problem I had, so I figured someone else might run into this

you can get this exception if you declare a class before the code of one of your forms

This will generate the error:

public class SomeTestClass {...}
...
public partial class ConfigurationForm : Form ...


The fix is simple, either move your class after the Form class, or (better yet) move it out to it's own file