The simplest way to group by:
- day
- week
- hour of the day
- and more (complete list below)
🎉 Time zones - including daylight saving time - supported!! the best part
🍰 Get the entire series - the other best part
Supports PostgreSQL, MySQL, and Redshift, plus arrays and hashes (and limited support for SQLite)
💘 Goes hand in hand with Chartkick
Add this line to your application’s Gemfile:
gem 'groupdate'
For MySQL and SQLite, also follow these instructions.
User.group_by_day(:created_at).count
# {
# Sat, 28 May 2016 => 50,
# Sun, 29 May 2016 => 100,
# Mon, 30 May 2016 => 34
# }
Results are returned in ascending order by default, so no need to sort.
You can group by:
- second
- minute
- hour
- day
- week
- month
- quarter
- year
and
- hour_of_day
- day_of_week (Sunday = 0, Monday = 1, etc)
- day_of_month
- month_of_year
Use it anywhere you can use group
. Works with count
, sum
, minimum
, maximum
, and average
. For median
, check out ActiveMedian. For other aggregate functions, including multiple together, check out CalculateAll.
The default time zone is Time.zone
. Change this with:
Groupdate.time_zone = "Pacific Time (US & Canada)"
or
User.group_by_week(:created_at, time_zone: "Pacific Time (US & Canada)").count
# {
# Sun, 06 Mar 2016 => 70,
# Sun, 13 Mar 2016 => 54,
# Sun, 20 Mar 2016 => 80
# }
Time zone objects also work. To see a list of available time zones in Rails, run rake time:zones:all
.
Weeks start on Sunday by default. Change this with:
Groupdate.week_start = :mon # first three letters of day
or
User.group_by_week(:created_at, week_start: :mon).count
You can change the hour days start with:
Groupdate.day_start = 2 # 2 am - 2 am
or
User.group_by_day(:created_at, day_start: 2).count
To get a specific time range, use:
User.group_by_day(:created_at, range: 2.weeks.ago.midnight..Time.now).count
To get the most recent time periods, use:
User.group_by_week(:created_at, last: 8).count # last 8 weeks
To exclude the current period, use:
User.group_by_week(:created_at, last: 8, current: false).count
You can order in descending order with:
User.group_by_day(:created_at, reverse: true).count
Keys are returned as date or time objects for the start of the period.
To get keys in a different format, use:
User.group_by_month(:created_at, format: "%b %Y").count
# {
# "Jan 2015" => 10
# "Feb 2015" => 12
# }
or
User.group_by_hour_of_day(:created_at, format: "%-l %P").count
# {
# "12 am" => 15,
# "1 am" => 11
# ...
# }
Takes a String
, which is passed to strftime, or a Symbol
, which is looked up by I18n.localize
in i18n
scope 'time.formats', or a Proc
. You can pass a locale with the locale
option.
The entire series is returned by default. To exclude points without data, use:
User.group_by_day(:created_at, series: false).count
Or change the default value with:
User.group_by_day(:created_at, default_value: "missing").count
User.group_by_period(:day, :created_at).count
Limit groupings with the permit
option.
User.group_by_period(params[:period], :created_at, permit: ["day", "week"]).count
Raises an ArgumentError
for unpermitted periods.
If grouping on date columns which don’t need time zone conversion, use:
User.group_by_week(:created_on, time_zone: false).count
If passing user input as the column, be sure to sanitize it first like you must with group
.
column = params[:column]
# check against permitted columns
raise "Unpermitted column" unless ["column_a", "column_b"].include?(column)
User.group_by_day(column).count
users.group_by_day { |u| u.created_at } # or group_by_day(&:created_at)
Supports the same options as above
users.group_by_day(time_zone: time_zone) { |u| u.created_at }
Count
Hash[ users.group_by_day { |u| u.created_at }.map { |k, v| [k, v.size] } ]
Time zone support must be installed on the server.
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
or copy and paste these statements into a SQL console.
You can confirm it worked with:
SELECT CONVERT_TZ(NOW(), '+00:00', 'Pacific/Honolulu');
It should return the time instead of NULL
.
Groupdate has limited support for SQLite.
- No time zone support
- No
day_start
orweek_start
options - No
group_by_quarter
method
If your application’s time zone is set to something other than Etc/UTC
(the default), create an initializer with:
Groupdate.time_zone = false
Groupdate 4.0 brings a number of improvements. Here are a few to be aware of:
group_by
methods return anActiveRecord::Relation
instead of aGroupdate::Series
- Invalid options now throw an
ArgumentError
week_start
now affectsday_of_week
- Custom calculation methods are supported by default
Groupdate 3.0 brings a number of improvements. Here are a few to be aware of:
Date
objects are now returned for day, week, month, quarter, and year by default. Usedates: false
for the previous behavior, or change this globally withGroupdate.dates = false
.- Array and hash methods no longer return the entire series by default. Use
series: true
for the previous behavior. - The
series: false
option now returns the correct type and order, and plays nicely with other options.
Groupdate 2.0 brings a number of improvements. Here are two things to be aware of:
- the entire series is returned by default
ActiveSupport::TimeWithZone
keys are now returned for every database adapter - adapters previously returnedTime
orString
keys
View the changelog
Groupdate follows Semantic Versioning
Everyone is encouraged to help improve this project. Here are a few ways you can help:
- Report bugs
- Fix bugs and submit pull requests
- Write, clarify, or fix documentation
- Suggest or add new features