MySQL, Rails ActiveRecord date grouping and timezones

Question

I want to count users by creation date. When I query 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}

The creation date is Aug 7, but the result is Aug 8. This is happening 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?

Answer 1

Try convert_tz first:

User.group("date(convert_tz(created_at,'UTC','[your_time_zone]'))").count

If the convert_tz returns null, maybe you will need to load the timezone tables with this command line:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Referrence to mysql convert_tz.

Edit 1:

If you use Rackspace MySQL, you will need to enable root access to the database and run the timezone queries as root. Here you can find instructions to how install trove and enable root access using rackspace API.