Calculon: Aggregate Time Functions in ActiveRecord
While Rails does have the ability to run aggregate functions over certain columns in ActiveRecord (like sum, average, min, max, etc), there’s no way to do this easy while grouping by time buckets. For instance, it’s often the case that you want to know not just the sum of some value between two points in type, but also bucketed by minute (or hour/day/week/month/year/etc).
Using just active record, this can be a bit nasty looking and error prone. For instance, if I want to get the sum over a column named ‘a column’ by hour for today, the code would end up looking something like this:
Which is a PITA. ActiveRecord gives so many shortcuts for doing all kinds of things - why not time based groupings?
I think I know the answer to that question now - after going through the trouble of implementing a gem to do this. The gem is named calculon, after the famous actor. It’s even more of a PITA to deal with all of the various time manipulation functions in each database (for instance, sqlite has only 5 date and time functions) - so I understand why no one has seemingly tackled this problem before.
I started with only MySQL support, and it’s made my life quite a bit easier. Using calculon, the example above becomes:
You can even make shortcuts if there are certain values you want to perform a given aggregate function over routinely. For instance, let’s say you have a class named Game that has points for team a and team b, and you want to get their average scores.
Check out the code/further docs at github.com/opbandit/calculon.