MySQL, Rails ActiveRecord grouping date and time zones
I want to count users by creation date. When I ask my last user, I have:
> User.last.created_at
=> Thu, 07 Aug 2014 21:37:55 BRT -03:00
When I count users per date, I get this:
> User.group("date(created_at)").count
=> {Fri, 08 Aug 2014=>1}
Creation date is August 7, but the result is August 8. This is because the group condition is in UTC and my timezone is Brasilia. I have this in my application.rb
:
config.time_zone = 'Brasilia'
config.active_record.default_timezone = :local
How to solve this?
source to share
Try convert_tz first:
User.group("date(convert_tz(created_at,'UTC','[your_time_zone]'))").count
If convert_tz returns null, you may need to load the timezone tables using this command line:
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Link to mysql convert_tz .
Edit 1:
If you are using Rackspace MySQL you need to enable root access to the database and run timezone queries as root. Here you can find instructions on how to install trove
and allow root access using the rackspace APIs.
source to share
Your database is always stored in UTC (unless you change it), even if your application is configured to use Brazil Local Time. When you use the where clause, Rails gives you the ability to specify the timezone you are in. But a group statement is not such a thing. One solution is to use a database specific function (like @ JaugarChang's answer). Another does this:
group = User.group("date(created_at)").count
results = group.map{|date, count| {Time.zone.utc_to_local(DateTime.parse(date)).to_date => count } }
- Pro: you do not depend on specific database functions to convert time zone;
- Con: Not so fast compared to the first one. It takes more knowledge of the time zone for the programmer to understand what is happening here.
source to share