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, 24 May 2020 => 50,
# Sun, 25 May 2020 => 100,
# Mon, 26 May 2020 => 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
- minute_of_hour
- hour_of_day
- day_of_week (Sunday = 0, Monday = 1, etc)
- day_of_month
- day_of_year
- month_of_year
Use it anywhere you can use group. Works with count, sum, minimum, maximum, and average. For median and percentile, check out ActiveMedian.
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, 08 Mar 2020 => 70,
# Sun, 15 Mar 2020 => 54,
# Sun, 22 Mar 2020 => 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 = :mondayor
User.group_by_week(:created_at, week_start: :monday).countYou can change the hour days start with:
Groupdate.day_start = 2 # 2 am - 2 amor
User.group_by_day(:created_at, day_start: 2).countTo get a specific time range, use:
User.group_by_day(:created_at, range: 2.weeks.ago.midnight..Time.now).countTo expand the range to the start and end of the time period, use:
User.group_by_day(:created_at, range: 2.weeks.ago..Time.now, expand_range: true).countTo get the most recent time periods, use:
User.group_by_week(:created_at, last: 8).count # last 8 weeksTo exclude the current period, use:
User.group_by_week(:created_at, last: 8, current: false).countYou can order in descending order with:
User.group_by_day(:created_at, reverse: true).countKeys 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 2020" => 10
# "Feb 2020" => 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).countOr change the default value with:
User.group_by_day(:created_at, default_value: "missing").countUser.group_by_period(:day, :created_at).countLimit groupings with the permit option.
User.group_by_period(params[:period], :created_at, permit: ["day", "week"]).countRaises an ArgumentError for unpermitted periods.
To group by a specific number of minutes or seconds, use:
User.group_by_minute(:created_at, n: 10).count # 10 minutesIf grouping on date columns which don’t need time zone conversion, use:
User.group_by_week(:created_on, time_zone: false).countIf you use Postgres and have a default scope that uses order, you may get a column must appear in the GROUP BY clause error (just like with Active Record’s group method). Remove the order scope with:
User.unscope(:order).group_by_day(:count).countusers.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 }Get the entire series with:
users.group_by_day(series: true) { |u| u.created_at }Count
users.group_by_day { |u| u.created_at }.to_h { |k, v| [k, v.count] }Time zone support must be installed on the server.
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysqlYou 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_startoption - No
group_by_quartermethod
If your application’s time zone is set to something other than Etc/UTC (the default), create an initializer with:
Groupdate.time_zone = falseView the changelog
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
To get started with development and testing, check out the Contributing Guide.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent