The simplest way to group by:
- day
- week
- month
- day of the week
- hour of the day
- and more (complete list at bottom)
🎉 Time zones supported!! the best part
🌎 Use it with any programming language the other best part
Supports PostgreSQL and MySQL
Groupdate.sql creates SQL functions, which are stored by the database.
You can use these functions with any ORM (or without one).
Group by day
SELECT gd_day(created_at, 'America/Los_Angeles') AS day, COUNT(*)
FROM users GROUP BY day;
-- result
day | count
------------------------+-------
2013-04-11 07:00:00+00 | 145
2013-04-12 07:00:00+00 | 65
2013-04-13 07:00:00+00 | 293
Group by week
SELECT gd_week(created_at, 'America/Chicago') AS week, COUNT(*)
FROM users GROUP BY week;
-- result
week | count
------------------------+-------
2013-05-05 05:00:00+00 | 1327
2013-05-12 05:00:00+00 | 3432
2013-05-19 05:00:00+00 | 673
Group by day of week
SELECT gd_day_of_week(created_at, 'America/New_York') AS day_of_week, COUNT(*)
FROM orders GROUP BY day_of_week;
-- result
day_of_week | count
-------------+-------
0 | 167
1 | 273
2 | 439
3 | 285
4 | 318
5 | 123
6 | 189
Note: Weeks start on Sunday.
Ruby on Rails (activerecord)
User.group("gd_day(created_at)").count
Django, Node.js, Go, etc
# pull requests please :)
Run this SQL
For MySQL, time zone support must be installed on the server.
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
All functions are prefixed with gd_
to prevent conflicts with MySQL functions.
- second
- minute
- hour
- day
- week - starts Sunday
- week_mon - starts Monday
- month
- year
- hour_of_day
- day_of_week
A list of time zones can be found here
Run this SQL
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