Groupdate

For MySQL and MariaDB, time zone support must be installed on the server. You can install it by running the following command in your terminal:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

To verify the installation, run this SQL query:

SELECT CONVERT_TZ(NOW(), '+00:00', 'Pacific/Honolulu');

It should return a time value instead of NULL.

Add the following line to your application's Gemfile to install Groupdate:

gem "groupdate"

If you are using PostgreSQL and have a default scope that includes an order clause, you may encounter the error: column must appear in the GROUP BY clause.

To resolve this, remove the order scope before grouping:

User.unscope(:order).group_by_day(:created_at).count

User.unscope(:order).group_by_day(:count).count

Groupdate also works with Ruby arrays and hashes by passing a block to the grouping method.

users.group_by_day { |u| u.created_at }
# or
users.group_by_day(&:created_at)

All standard options (like time_zone, series, etc.) are supported. To get a hash of counts from an array:

users.group_by_day { |u| u.created_at }.to_h { |k, v| [k, v.count] }

Groupdate allows you to group ActiveRecord queries by various time periods. It works with standard ActiveRecord methods like count, sum, minimum, maximum, and average. Results are returned in ascending order by default.

Available grouping periods:

• second, minute, hour, day, week, month, quarter, year
• minute_of_hour, hour_of_day, day_of_week (Sunday = 0, Monday = 1, etc), day_of_month, day_of_year, month_of_year

User.group_by_day(:created_at).count

Groupdate provides options to limit the data to specific ranges or recent periods.

Specific Range:

User.group_by_day(:created_at, range: 2.weeks.ago.midnight..Time.now).count

Expand Range (to include the full start/end of the period):

User.group_by_day(:created_at, range: 2.weeks.ago..Time.now, expand_range: true).count

Recent Periods:

• Get the last N periods: User.group_by_week(:created_at, last: 8).count
• Exclude the current period: User.group_by_week(:created_at, last: 8, current: false).count

To group by a specific number of minutes or seconds, use the n option.

User.group_by_minute(:created_at, n: 10).count # Groups by 10-minute intervals

By default, Groupdate returns the entire series (including periods with no data).

• To exclude periods without data: series: false
• To change the value used for missing data: default_value: "missing"

Example:

User.group_by_day(:created_at, series: false).count

The default time zone is Time.zone. You can set a global default or specify a time zone per query.

To set a global default:

Groupdate.time_zone = "Pacific Time (US & Canada)"

To specify a time zone for a specific query:

User.group_by_week(:created_at, time_zone: "Pacific Time (US & Canada)").count

If you are grouping on date columns that do not require time zone conversion, use time_zone: false.

You can customize when a week or a day begins.

Week Start (defaults to Sunday):

• Global: Groupdate.week_start = :monday
• Per query: User.group_by_week(:created_at, week_start: :monday).count

Day Start (defaults to midnight):

• Global: Groupdate.day_start = 2 (sets start to 2 am)
• Per query: User.group_by_day(:created_at, day_start: 2).count

You can use group_by_period to pass the period as a variable. To prevent arbitrary input from causing errors, use the permit option to whitelist allowed periods.

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

If a period is not in the permit list, an ArgumentError is raised.

By default, keys are returned as date or time objects. You can format them using the format option. The format argument accepts:

• A String (passed to strftime)
• A Symbol (looked up via I18n.localize in time.formats scope)
• A Proc

You can also pass a locale option.

Example using strftime:

User.group_by_month(:created_at, format: "%b %Y").count