The simplest way to group temporal data
Ruby
Latest commit b92b6cc Feb 13, 2017 @ankane Updated changelog [skip ci]

README.md

Groupdate

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

Limited support for SQLite

Build Status

💘 Goes hand in hand with Chartkick

Groupdate 3.0 was just released! See instructions for upgrading. If you use Chartkick with Groupdate, we recommend Chartkick 2.0 and above.

Get Started

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.

Time Zones

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.

Week Start

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

Day Start

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

Time Range

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

Order

You can order in descending order with:

User.group_by_day(:created_at, reverse: true).count

Keys

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.

Series

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

Dynamic Grouping

User.group_by_period(:day, :created_at).count

Limit groupings with the permit option.

User.group_by_period(params[:period], :created_at, permit: %w[day week]).count

Raises an ArgumentError for unpermitted periods.

Date Columns

If grouping on date columns which don’t need time zone conversion, use:

User.group_by_week(:created_on, time_zone: false).count

Arrays and Hashes

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] } ]

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

or copy and paste these statements into a SQL console.

You can confirm it worked with:

SELECT CONVERT_TZ(NOW(), '+00:00', 'Etc/UTC');

It should return the time instead of NULL.

For SQLite

Groupdate has limited support for SQLite.

  • No time zone support
  • No day_start or week_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

Upgrading

3.0

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. Use dates: false for the previous behavior, or change this globally with Groupdate.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.

2.0

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 returned Time or String keys

History

View the changelog

Groupdate follows Semantic Versioning

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: