MySQL, Rails ActiveRecord date grouping and timezo

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

Your database always saves in UTC (unless you modify it) even though your app is configured to use Brasilia Local Time. When you use a 'where' statement, Rails gives you the option to say the time zone you are. But the group statement there is no such thing. One solution is to use a database specific function (like @JaugarChang answer ). Another is doing 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 don't depend on specific database native functions to convert time zone;
• Con: Not so fast compared to the first one. Requires more Timezone knowledge for a programmer to understand what's going on here.

Answer 2

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.

Answer 3

Without time zone function, just add hours.

User.group("date(created_at + INTERVAL 8 HOUR)").count

Add 8 hours is Shanghai's time zone. Welcome to Shanghai.