The simplest way to group by:
- day
- week
- month
- day of the week
- hour of the day
- and more
🎉 Time zones supported!!
Supports PostgreSQL, MySQL, and MariaDB
Run this SQL
For MySQL and MariaDB, time zone support must be installed on the server.
For MySQL, use:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysqlFor MariaDB, use:
mariadb-tzinfo-to-sql /usr/share/zoneinfo | mariadb -u root mysqlGroupdate.sql creates SQL functions, which are stored by the database. You can use these functions with any ORM or without one. Times stored in your database are assumed to be UTC (this is the default for frameworks like Rails).
Group by day
SELECT gd_day(created_at, 'America/Los_Angeles') AS day, COUNT(*) FROM users GROUP BY day; -- result day | count ------------+------- 2025-01-01 | 145 2025-01-02 | 65 2025-01-03 | 293
Group by week
SELECT gd_week(created_at, 'America/Chicago') AS week, COUNT(*) FROM users GROUP BY week; -- result week | count ------------+------- 2025-01-05 | 1327 2025-01-12 | 3432 2025-01-19 | 673
Weeks start on Sunday by default
Group by dynamic period
SELECT gd_period('day', created_at, 'America/Los_Angeles') AS period, COUNT(*) FROM users GROUP BY period;
Works with
day,week,month, andyear
Here’s the complete list of functions. All are prefixed with gd_ to prevent conflicts with MySQL functions.
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- hour_of_day
- day_of_week
- day_of_month
- day_of_year
- month_of_year
A list of time zones can be found here.
With PostgreSQL, the time zone is optional.
SELECT gd_day(created_at) AS day, COUNT(*) FROM users GROUP BY day;
The default is UTC. Change this to your preferred time zone by running:
CREATE OR REPLACE FUNCTION gd_time_zone() RETURNS text AS $$ SELECT 'America/Los_Angeles'::text; $$ LANGUAGE SQL IMMUTABLE;
Set the day weeks start. The default is Sunday.
For PostgreSQL, run:
CREATE OR REPLACE FUNCTION gd_week_start() RETURNS int AS $$ SELECT 6; -- mon=0, tue=1, wed=2, thu=3, fri=4, sat=5, sun=6 $$ LANGUAGE SQL IMMUTABLE;
For MySQL and MariaDB, run:
START TRANSACTION; DROP FUNCTION IF EXISTS gd_week_start; CREATE FUNCTION gd_week_start() RETURNS INT DETERMINISTIC RETURN 6; -- mon=0, tue=1, wed=2, thu=3, fri=4, sat=5, sun=6 COMMIT;
Run this SQL
View the changelog
Everyone is encouraged to help improve this project. Here are a few ways you can help:
- Report bugs
- Fix bugs and submit pull requests
- Write, clarify, or fix documentation
- Suggest or add new features
To get started with development:
git clone https://github.com/ankane/groupdate.sql.git cd groupdate.sql bundle install bundle exec rake test