I’m not sure if this fits in Rails’ core but since I spent sometime trying to solve a problem and couldn’t find any solution, I decided to share this method I’ve created to extend ActiveRecord::Relation.
Here’s a Gist of it: Adds time zone aware data grouping method to ActiveRecord. If you want to group a large set of database rows by date using a timestamp as reference, the optimal way is to do it right in the database. When doing it though there are some issues with time zones that #group_date solves. For example, if a UTC timestamp is set to 2013-12-01 01:00 and you're in Brazil (= 2013-11-30 22:00) you'd want date() to return 2013-11-30 but database will actually return 2013-12-01 as it doesn't know anything about time zones. · GitHub
The problem is that when we try grouping timestamps by date, SQL returns unexpected results as it’s not aware of time zones.
Rails though is aware of them so I believe it should be used to handle this issue properly.
By using #group_date one can get any set of records grouped by date from a timestamp column.
This is very common to generate statistics.
What do you guys think of it?
Have you ever stumbled on a similar problem?
How have you dealt with it?
I have run into that problem multiple times, and I have used this gem to deal with it: GitHub - ankane/groupdate: The simplest way to group temporal data
It only supports MySQL and PostgreSQL, so a solution that works across different databases would be welcome, in my view.