Groupdate

The simplest way to group by:

• day
• week
• month
• day of the week
• hour of the day
• and more (complete list at bottom)

🎉 Time zones supported!! the best part

🍰 Get the entire series - the other best part

Works with Rails 3.0+

Supports PostgreSQL and MySQL

💘 Goes hand in hand with Chartkick

Usage

User.group_by_day(:created_at).count# {# 2013-04-16 00:00:00 UTC => 50,# 2013-04-17 00:00:00 UTC => 100,# 2013-04-18 00:00:00 UTC => 34# }Task.group_by_month(:updated_at).count# {# 2013-02-01 00:00:00 UTC => 84,# 2013-03-01 00:00:00 UTC => 23,# 2013-04-01 00:00:00 UTC => 44# }Goal.group_by_year(:accomplished_at).count# {# 2011-01-01 00:00:00 UTC => 7,# 2012-01-01 00:00:00 UTC => 11,# 2013-01-01 00:00:00 UTC => 3# }

The default time zone is Time.zone. Pass a time zone as the second argument.

User.group_by_week(:created_at,"Pacific Time (US & Canada)").count# {# 2013-03-03 08:00:00 UTC => 80,# 2013-03-10 08:00:00 UTC => 70,# 2013-03-17 07:00:00 UTC => 54# }# equivalentlytime_zone=ActiveSupport::TimeZone["Pacific Time (US & Canada)"]User.group_by_week(:created_at,time_zone).count

Note: Weeks start on Sunday by default. For other days, use:

User.group_by_week(:created_at,:start=>:mon)# first three letters of day# must be the last argumentUser.group_by_week(:created_at,time_zone,:start=>:sat)# change globallyGroupdate.week_start=:mon

You can also group by the day of the week or hour of the day.

# day of the weekUser.group_by_day_of_week(:created_at).count# {# 0 => 54, # Sunday# 1 => 2, # Monday# ...# 6 => 3 # Saturday# }# hour of the dayUser.group_by_hour_of_day(:created_at,"Pacific Time (US & Canada)").count# {# 0 => 34,# 1 => 61,# ...# 23 => 12# }

You can order results with:

User.group_by_day(:created_at).order("day asc").countUser.group_by_week(:created_at).order("week desc").countUser.group_by_hour_of_day(:created_at).order("hour_of_day asc").count

Use it with anywhere you can use group.

Task.completed.group_by_hour(:completed_at).average(:priority)

Go nuts!

Request.where(page: "/home").group_by_minute(:started_at).maximum(:request_time)

Show me the series 💰

You have two users - one created on May 2 and one on May 5.

User.group_by_day(:created_at).count# {# 2013-05-02 00:00:00 UTC => 1,# 2013-05-05 00:00:00 UTC => 1# }

Awesome, but you want to see the first week of May. Pass a range as the third argument.

# pretend today is May 7time_range=6.days.ago..Time.nowUser.group_by_day(:created_at,Time.zone,time_range).count# {# 2013-05-01 00:00:00 UTC => 0,# 2013-05-02 00:00:00 UTC => 1,# 2013-05-03 00:00:00 UTC => 0,# 2013-05-04 00:00:00 UTC => 0,# 2013-05-05 00:00:00 UTC => 1,# 2013-05-06 00:00:00 UTC => 0,# 2013-05-07 00:00:00 UTC => 0# }User.group_by_day_of_week(:created_at,Time.zone,time_range).count# {# 0 => 0,# 1 => 1,# 2 => 0,# 3 => 0,# 4 => 1,# 5 => 0,# 6 => 0# }

Results are returned in ascending order, so no need to sort.

Also, this form of the method returns a Groupdate::Series instead of an ActiveRecord::Relation. ActiveRecord::Relation method calls (like where and joins) should come before this.

Installation

Add this line to your application's Gemfile:

gem'groupdate'

For MySQL

Time zone support must be installed on the server.

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

For JRuby

Use the master version of your JDBC adapter. You will get incorrect results for versions before this commit.

# postgresqlgem"activerecord-jdbcpostgresql-adapter",:github=>"jruby/activerecord-jdbc-adapter"# mysqlgem"activerecord-jdbcmysql-adapter",:github=>"jruby/activerecord-jdbc-adapter"

Complete list

group_by_?

• second
• minute
• hour
• day
• week
• month
• year
• hour_of_day
• day_of_week

Note

activerecord <= 4.0.0.beta1 and the pg gem returns String objects instead of Time objects. This is fixed on activerecord master

User.group_by_day(:created_at).count# mysql2# pg and activerecord master{2013-04-2200:00:00UTC=>1}# Time object# pg and activerecord <= 4.0.0.beta1{"2013-04-22 00:00:00+00"=>1}# String

Another data type inconsistency

User.group_by_day_of_week(:created_at).count# mysql2{0=>1,4=>1}# Integer# pg and activerecord <= 4.0.0.beta1{"0"=>1,"4"=>1}# String# pg and activerecord master{0.0=>1,4.0=>1}# Float

These are not a result of groupdate (and unfortunately cannot be fixed by groupdate)

History

View the changelog

Groupdate follows Semantic Versioning

Contributing

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request