Groupdate: grouping Rails records by day, week and hour without hand-written SQL

What Groupdate solves for a Rails application

Counting records per day sounds trivial until you run it. A plain ActiveRecord group on a timestamp column returns one row per distinct timestamp, not per day, and an empty day simply does not appear in the result. Groupdate exists to close both gaps. It adds methods like group_by_day and group_by_week to Active Record relations, so an expression such as User.group_by_day(:created_at).count returns a hash keyed by the start of each period, in ascending order by default. The README states that results come back sorted, so no separate sort step is needed.

The audience is narrow and specific: Ruby developers on Rails who need a time series for a dashboard or a chart, and who would otherwise write a database-specific date_trunc expression plus a calendar table or a fill-in loop in Ruby. Groupdate supports PostgreSQL, MySQL, MariaDB, SQLite and Redshift, plus plain arrays and hashes, which means the same call site works across the databases a typical application uses in development and production. The README also notes it pairs with Chartkick, another project from the same author, which consumes exactly this hash-of-period-to-count shape.

How the grouping, time zones and series filling work

The gem is a query builder, not a background job or a cache. Each call rewrites the SQL that Active Record sends: the timestamp column is truncated to the requested period using the database's own date functions, and the GROUP BY clause is built from that expression. The period list is wide. The README lists second, minute, hour, day, week, month, quarter and year, plus calendar-relative periods such as minute_of_hour, hour_of_day, day_of_week, day_of_month, day_of_year and month_of_year. The calendar ones are the interesting half, because grouping by hour of day across months is a different query shape than grouping by hour, and doing it by hand means extracting the field yourself.

The documented default time zone is Time.zone, which is the Rails application zone. That default matters more than it looks. Grouping by day in UTC while users live elsewhere produces buckets that start at the wrong moment, and the README calls time zone support, including daylight saving time, the best part of the project. You can override the zone per query or globally, and the README notes that time zone objects work as well as strings. For date columns that should not be converted at all, time_zone: false skips the conversion.

The second mechanism is series filling. By default the entire series is returned, so a day with no matching rows still appears as a key, with a zero count. That is what makes a line chart continuous instead of jumping across gaps. Passing series: false returns only the periods that have data, and default_value changes what the empty periods hold. The range option bounds the series explicitly, expand_range snaps the bounds to whole periods, and last: 8 returns the most recent eight periods, which is the common dashboard case. The current: false option excludes the period in progress, useful when a partial week would otherwise show a misleading dip.

A few smaller controls round this out. Keys are date or time objects by default and can be reformatted with a strftime string, an I18n symbol, or a Proc. group_by_period takes the period as a runtime value, and the permit option whitelists which periods are accepted, raising ArgumentError for anything else. That last one is the guard against a user-supplied period string reaching the query.

Installing the gem and producing a first series

Installation is one line in the Gemfile, as the README shows.

gem "groupdate"

Run bundle install afterward. The README adds a caveat for MySQL and MariaDB users: the server needs time zone support installed, and the instructions are in a separate section of the README rather than in the installation steps themselves.

With the gem loaded, the smallest useful call is a count grouped by day. Assuming a User model with a created_at column, the README gives this example and its output shape.

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

The keys are the start of each day, and every day in the range appears even when the count is zero. From there, the options are passed as keyword arguments. A weekly series in a named zone, starting on Monday, limited to the last eight weeks, combines options the README documents separately.

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

The same methods work with sum, minimum, maximum and average, so a revenue-per-day series is the same call with a different aggregate. If you are grouping an in-memory array rather than a relation, the block form takes the timestamp extraction.

users.group_by_day { |u| u.created_at }

The README notes the array form supports the same options, and that series: true is what makes it fill empty periods, since arrays do not get the series by default.

Where Groupdate stops being the right tool

The aggregate list is the clearest boundary. Groupdate works with count, sum, minimum, maximum and average. It does not do median or percentile, and the README says so directly, pointing to ActiveMedian for those. If your dashboard needs a p95 latency per hour, this gem is not the piece that computes it.

The MySQL and MariaDB time zone requirement is a real deployment constraint, not a footnote. The README instructs loading the zoneinfo tables into the server with mysql_tzinfo_to_sql and verifying with a CONVERT_TZ query that should return a time rather than NULL. On a managed MySQL instance where you cannot run that command, named time zones will not resolve, and the failure is quiet: you get NULL back rather than an error. That is worth testing before you build a chart on top of it.

The Postgres default scope interaction is the other documented sharp edge. If a model has a default scope containing order, grouping can raise column must appear in the GROUP BY clause, which the README notes is the same behaviour as Active Record's own group method. The fix is calling unscope(:order) before the grouping call. It is a one-line fix, but it surfaces at runtime and only on some models.

Finally, the project is a query helper, not a reporting layer. There is no caching, no materialized view management, and no scheduling. Every call hits the database. A dashboard that runs a dozen grouped queries on each page load is doing a dozen aggregate scans, and nothing in the gem prevents that.

Groupdate against writing the SQL yourself

The honest alternative is not another gem. It is the database's own date_trunc in Postgres, or DATE_FORMAT in MySQL, plus a fill-in step in Ruby, or a calendar table joined against your data. That approach has one advantage: you control the exact SQL, including window functions, percentile_cont, and any index hint you want. If your series needs a median, a moving average, or a gap-filling rule that is not simply zero, hand-written SQL gets there and Groupdate does not.

The trade-off is portability and repetition. The Postgres expression and the MySQL expression are different strings, and the fill-in loop has to be written and tested for each period type, including the leap-year and daylight-saving cases. Groupdate centralises that: one call site, five supported databases, and the DST handling already accounted for. For the common case of a count per day, week or month feeding a chart, the gem removes a category of bug rather than a line of code.

For the chart itself, the README points to Chartkick, which is a separate library with its own API. Groupdate produces the hash; Chartkick renders it. If you already have a charting layer, you only need the first half.

On the in-memory side, the array and hash support overlaps with plain Ruby's group_by plus a transform. The difference the README describes is that Groupdate applies the same period options, including series filling, to arrays, so you do not maintain two grouping implementations for database-backed and in-memory collections.

Maintenance, licence and upgrade cost

The repository is not archived, and the last push was on 2026-08-15, which is recent enough that the codebase is being touched. The README links a changelog and a contributing guide at the repository root, and the top-level layout includes a Gemfile, a gemspec, a Rakefile, a gemfiles directory and a test directory, which is the standard shape for a gem that tests against multiple dependency versions. The README also carries a build status badge for the GitHub Actions workflow.

The repository has no published GitHub releases, so there is no version history to reason about here. The practical upgrade path is the changelog file in the repository, and the gemfiles directory suggests the maintainer runs the suite against more than one Rails version, which is the signal that matters when you upgrade Rails rather than when you upgrade this gem.

The licence is MIT, per the LICENSE.txt file at the repository root and the repository metadata. In plain terms, that permits use, modification and redistribution with the licence and copyright notice retained. It says nothing about the licences of your application or of the databases you query, and it is not legal advice; if your organisation has a policy on dependency licences, the file to read is LICENSE.txt.

The dependency surface is small by design. The gem sits on top of Active Record, so it inherits your Rails version constraints rather than pinning its own ORM. The cost of adopting it is therefore mostly the cost of the two documented sharp edges above: the MySQL time zone setup and the default scope with order.

Editorial conclusion

Groupdate fits a Rails application that already stores timestamps and needs a time series for a chart: the group_by_day and group_by_week helpers cover most of that ground, and the series filling means the chart does not collapse when a day has no rows. It is the wrong choice if you need median or percentile aggregates, since the README points those to ActiveMedian, or if you are not on Active Record at all. Before adopting it, check two things in your own setup: whether your database is MySQL or MariaDB, in which case the time zone tables must be loaded on the server first, and whether any model carries a default scope with an order clause, because that combination produces a column must appear in the GROUP BY clause error unless you call unscope(:order).

Frequently asked questions

Official sources

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