I’ve been writing a lot of statistics queries in Active Record/MySQL lately and noticed some possibly non-obvious things.
Our database is configured to use UTC time. In Rails 2.1, we use
config.active_record.default_timezone = :utc. In 2.3, I believe it’s
config.time_zone = 'UTC', and set that way by default.
A lot of my statistics queries involved time ranges, grouping per day or only using data from a certain period. I found that a query like
did not do any time zone conversions as you may expect. If
some_day.beginning_of_day is a non-UTC local time (e.g. 00:00 on March 26th CET), that date and time is passed into the query as “2009-03-26 00:00:00” and compared to the UTC datetimes in the database.
Instead, you should do
In this case, “2009-03-25 23:00:00” would be passed into the query.
If you want to do that another way, or to group on local-time dates, you could instead do something like
Without the conversion, it’d group on UTC dates instead of local CET dates. That would mean a customer that was created at 00:59 CET on March 26 would be grouped under March 25 instead.
Note that you need to set up the MySQL time zone tables with something like
On OS X with MySQL 5 from MacPorts, the invocation was
Warnings about time zones that couldn’t be loaded are fine as long as they’re not the ones you’re using.
You can convert time zones without those tables, but then you need to specify the UTC offset manually, and since it changes with daylight saving, I wouldn’t recommend it.
Another thing I noticed was that Active Record calculation queries (using e.g.
.count) don’t seem to support grouping by multiple columns at once. It’s easy to do with
1 2 3 4