Groupdate: Time Zone-Aware Temporal Grouping for Rails and ActiveRecord

Time-Aware Grouping Without Writing SQL Date Functions

Rails applications that need to group database records by time period, such as counting signups per day or revenue per week, typically require writing SQL date truncation or formatting functions. These vary by database engine and break when time zones are involved, because a 'day' in Pacific Time is not the same as a 'day' in UTC.

Groupdate addresses both problems. It adds a family of group_by_* methods to ActiveRecord that translate into the correct SQL for each supported database, with time zone conversion handled automatically. The README names PostgreSQL, MySQL, MariaDB, SQLite, and Redshift as supported backends.

The gem also works with plain Ruby arrays and hashes by accepting a block that extracts the timestamp from each object. This means it is not limited to database queries; in-memory collections can be grouped by the same methods.

Groupdate pairs with Chartkick, a Rails charting gem by the same author, for rendering the grouped results directly as charts. The README describes the two as going 'hand in hand'.

Installing Groupdate and Running the First Query

Add the gem to your application's Gemfile:

gem "groupdate"

For MySQL and MariaDB, additional setup is required before time zone grouping will work (covered in a later section).

The simplest query groups a model by day and counts records:

User.group_by_day(:created_at).count
# {
#   Thu, 01 Jan 2026 => 50,
#   Fri, 02 Jan 2026 => 100,
#   Sat, 03 Jan 2026 => 34
# }

Results are returned in ascending order by default. The keys are date or time objects representing the start of each period. The same method works anywhere ActiveRecord's group method works: count, sum, minimum, maximum, and average are all supported. The README notes that for median and percentile aggregations, the separate ActiveMedian gem is needed.

The full list of grouping periods available is: second, minute, hour, day, week, month, quarter, year, minute_of_hour, hour_of_day, day_of_week, day_of_month, day_of_year, and month_of_year. The cyclic groupings (hour_of_day, day_of_week, etc.) group by the time component alone and ignore the calendar date, which is the correct behavior for analyzing patterns by time of day or weekday.

Time Zones and Daylight Saving Time

Groupdate's primary design concern is correct time zone handling. The default time zone is Time.zone, the Rails application time zone. This can be changed globally:

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

Or per query:

User.group_by_week(:created_at, time_zone: "Pacific Time (US & Canada)").count
# {
#   Sun, 04 Jan 2026 => 70,
#   Sun, 11 Jan 2026 => 54,
#   Sun, 18 Jan 2026 => 80
# }

Time zone objects (ActiveSupport::TimeZone instances) are also accepted as the time_zone option. The README notes that running rake time:zones:all in a Rails app lists all available time zone names.

Daylight saving time transitions are handled automatically when a named time zone is used. A query for 'day' in a DST-observing zone will produce correct boundaries across the spring and autumn transitions, where a naive UTC offset calculation would group records into the wrong day.

For date columns that store a date without time and need no time zone conversion, set time_zone: false:

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

This disables conversion entirely, which is correct when the column type is DATE rather than DATETIME or TIMESTAMP.

Complete Series, Default Values, and Range Options

By default, Groupdate returns entries for every period in the range, even periods with no matching records. The README calls this 'Get the entire series' and describes it as one of the gem's key features. A chart of daily signups over 30 days will have 30 data points, not 27, even if three days had zero signups.

To disable this and return only periods with data:

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

To fill gaps with a custom default instead of zero:

User.group_by_day(:created_at, default_value: "missing").count

The time range for the series can be set explicitly:

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

For a rolling window of the most recent N periods, the last option avoids computing a range manually:

User.group_by_week(:created_at, last: 8).count # last 8 weeks

To exclude the current (in-progress) period from the window:

User.group_by_week(:created_at, last: 8, current: false).count

This is useful in dashboards where partial periods would show misleadingly low numbers compared to completed periods.

Key Formatting, Dynamic Periods, and Custom Intervals

Return keys as formatted strings instead of date objects using the format option:

User.group_by_month(:created_at, format: "%b %Y").count
# {
#   "Jan 2026" => 10
#   "Feb 2026" => 12
# }

The format option accepts a String (passed to strftime), a Symbol (looked up by I18n.localize in the 'time.formats' scope), or a Proc. A locale option is also available for I18n formatting.

When the grouping period needs to be a user-controlled parameter, group_by_period accepts the period name as a first argument:

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

The permit option restricts which period values are accepted. The README states that an ArgumentError is raised for unpermitted values, which prevents users from injecting arbitrary period names into the query.

For non-standard intervals, the n option groups by a multiple of the base period:

User.group_by_minute(:created_at, n: 10).count # 10 minutes

The n option is documented for minute and second. This covers use cases like 15-minute analytics buckets without requiring custom SQL.

Week start day defaults to Sunday. Change it globally with Groupdate.week_start = :monday or per-query with week_start: :monday. Day start hour defaults to midnight; change it with Groupdate.day_start = 2 (2 am) for applications where a 'day' should begin at a non-midnight boundary.

MySQL and MariaDB Require Server-Side Time Zone Tables

PostgreSQL and SQLite handle time zone conversion natively. MySQL and MariaDB do not have this capability enabled by default. They require time zone tables to be loaded into the mysql database before CONVERT_TZ can translate timestamps between named time zones.

The README shows the import command:

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

After running this, verify the import worked:

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

A successful import returns a timestamp. A NULL result means the time zone tables are not loaded, and Groupdate's time zone-based grouping will not produce correct results.

This is a server-level requirement, not a gem configuration. On managed database services (RDS, Cloud SQL, PlanetScale), the operator may need to enable time zone support separately or confirm it is already enabled. The README links to the MySQL time zone support documentation for reference.

For applications running on SQLite in development and MySQL in production, developers must reproduce the MySQL time zone setup in the development environment to get consistent results across environments.

Raw SQL GROUP BY as the Direct Alternative

Groupdate's alternative is writing the date grouping in raw SQL or Arel. PostgreSQL's DATE_TRUNC function, MySQL's DATE and DATE_FORMAT functions, and SQLite's strftime can all produce grouped results. This approach works in any Ruby web framework, not just Rails, and does not add a gem dependency.

The trade-offs are visible: a raw SQL approach requires writing the time zone conversion explicitly (AT TIME ZONE in PostgreSQL, CONVERT_TZ in MySQL), handling DST edge cases in the query, and producing a dense result set that excludes empty periods. Restoring empty periods requires generating the expected time series in Ruby and merging it with the query result.

Groupdate handles all of that inside the gem, at the cost of supporting only the databases it explicitly targets. Applications using less common databases, or databases accessed through a non-ActiveRecord ORM, cannot use Groupdate and must write the SQL directly.

The gem does not expose a way to inspect the generated SQL before execution beyond ActiveRecord's built-in to_sql method on the relation, which remains available since Groupdate returns a standard ActiveRecord::Relation.

Editorial conclusion

Teams building Rails dashboards or analytics features that need accurate, time zone-aware date grouping without writing manual SQL date functions will find Groupdate a direct fit. The gem does not provide percentile or median aggregations; the README points to ActiveMedian for those. Groupdate is MIT licensed and last pushed on 2026-08-15. Add it to the Gemfile and, for MySQL or MariaDB, run the time zone table import step before relying on any time zone conversion.

Frequently asked questions

Official sources

1. ankane/groupdate on GitHub
2. Issues
3. License: MIT
4. README