The simplest way to group temporal data
Branch: master
Clone or download
Latest commit 72bd8a8 Jul 9, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
mysql
postgresql
test
.gitignore Exclude Gemfile.lock Jul 9, 2018
.travis.yml
CHANGELOG.md Updated changelog [skip ci] Jul 9, 2018
Gemfile Removed pg_typecast Sep 17, 2015
LICENSE.txt Prep for v2 Jul 9, 2018
README.md
Rakefile

README.md

Groupdate.sql

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!!

🌎 Use it with any programming language

Supports PostgreSQL and MySQL

Build Status

Usage

Groupdate.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).

SQL

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 |   145
 2013-04-12 |    65
 2013-04-13 |   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 |  1327
 2013-05-12 |  3432
 2013-05-19 |   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

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, and year

Time Zone

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;

Week Start

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, run:

START TRANSACTION;

DROP FUNCTION IF EXISTS gd_week_start;
CREATE FUNCTION gd_week_start()
  RETURNS INT
  RETURN 6; -- mon=0, tue=1, wed=2, thu=3, fri=4, sat=5, sun=6

COMMIT;

Language / Frameworks

Ruby on Rails (ActiveRecord)

User.group("gd_day(created_at, 'America/Los_Angeles')").count

Django, Node.js, Go, etc

# pull requests please :)

Installation

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

Complete list

All functions are prefixed with gd_ to prevent conflicts with MySQL functions.

  • second
  • minute
  • hour
  • day
  • week
  • month
  • year
  • hour_of_day
  • day_of_week

A list of time zones can be found here

Uninstall

Run this SQL

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: