README.md
Groupdate
The simplest way to group by:
- day
- week
- month
- day of the week
- hour of the day
- and more (complete list at bottom)
:tada: Time zones supported!! the best part
Get the entire series - the other best part
Works with Rails 3.0+
Supports PostgreSQL and MySQL
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
# }
# equivalently
time_zone = ActiveSupport::TimeZone["Pacific Time (US & Canada)"]
User.group_by_week(:created_at, time_zone).count
Note: Weeks start on Sunday.
You can also group by the day of the week or hour of the day.
# day of the week
User.group_by_day_of_week(:created_at).count
# {
# 0 => 54, # Sunday
# 1 => 2, # Monday
# ...
# 6 => 3 # Saturday
# }
# hour of the day
User.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").count
User.group_by_week(:created_at).order("week desc").count
User.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 :moneybag:
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 7
time_range = 6.days.ago..Time.now
User.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.
# postgresql
gem "activerecord-jdbcpostgresql-adapter", :github => "jruby/activerecord-jdbc-adapter"
# mysql
gem "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 This is fixed on activerecord master
User.group_by_day(:created_at).count
# mysql2
# pg and activerecord master
{2013-04-22 00:00:00 UTC => 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)
Contributing
- Fork it
- Create your feature branch (
git checkout -b my-new-feature) - Commit your changes (
git commit -am 'Add some feature') - Push to the branch (
git push origin my-new-feature) - Create new Pull Request