Groupdate

The simplest way to group by:

  • day
  • week
  • hour of the day
  • and more (complete list below)

:tada: Time zones - including daylight saving time - supported!! the best part

:cake: Get the entire series - the other best part

Supports PostgreSQL, MySQL, and Redshift, plus arrays and hashes (and limited support for SQLite)

:cupid: Goes hand in hand with Chartkick

Build Status

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

Custom Calculation Methods

Groupdate knows all of the calculations defined by ActiveRecord like count, sum, or average. However you may have your own class level calculation methods that you need to tell Groupdate about. All you have to do is define the class method groupdate_calculation_methods returning an array of the method names as symbols.

class User < ApplicationRecord
  def self.groupdate_calculation_methods
    [:total_sign_ins]
  end

  def self.total_sign_ins
    all.sum(:sign_ins)
  end
end

Then you can use your custom calculation method:

User.group_by_week(:created_at).total_sign_ins

Note that even if your method uses one of the calculations from ActiveRecord, you'll still need to add it to the groupdate_calculation_methods array to have it return the Hash of dates to values. Otherwise it will return a Groupdate::Series object.

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: