Skip to content

Instantly share code, notes, and snippets.

@francois
Last active August 2, 2017 16:46
Show Gist options
  • Save francois/4c0be6c620bbaa1ce889fe92dc35d0cd to your computer and use it in GitHub Desktop.
Save francois/4c0be6c620bbaa1ce889fe92dc35d0cd to your computer and use it in GitHub Desktop.
...
2017-07-14 12:52:00+00 | 2
2017-07-14 12:53:00+00 | 2
2017-07-14 12:54:00+00 | 2
2017-07-14 12:55:00+00 | 2
2017-07-14 12:56:00+00 | 2
2017-07-14 12:57:00+00 | 2
2017-07-14 12:58:00+00 | 2
2017-07-14 12:59:00+00 | 2
2017-07-14 13:00:00+00 | 3
2017-07-14 13:01:00+00 | 3
2017-07-14 13:02:00+00 | 3
2017-07-14 13:03:00+00 | 3
2017-07-14 13:04:00+00 | 3
2017-07-14 13:05:00+00 | 3
2017-07-14 13:06:00+00 | 3
2017-07-14 13:07:00+00 | 2
2017-07-14 13:08:00+00 | 2
2017-07-14 13:09:00+00 | 2
2017-07-14 13:10:00+00 | 2
2017-07-14 13:11:00+00 | 2
2017-07-14 13:12:00+00 | 2
2017-07-14 13:13:00+00 | 2
2017-07-14 13:14:00+00 | 2
...
DROP TABLE events CASCADE;
CREATE TABLE events(
id serial primary key
, device_id int not null
, start_at timestamp with time zone
, end_at timestamp with time zone
, unique(device_id, start_at)
);
/* DEVICE 9 10 11 12 13 14 15 16
* 1 |---| |--------|
* 2 |-------------|
* 3 |--------|
* 4 |----------------|
*
* In other words, there was 1 device at 9, 2 at 10, 1 at 11 and so on...
*/
INSERT INTO events(device_id, start_at, end_at) VALUES
(1, '2017-07-14 10:00', '2017-07-14 11:00')
, (1, '2017-07-14 13:00', '2017-07-14 15:00')
, (2, '2017-07-14 09:00', '2017-07-14 12:00')
, (3, '2017-07-14 11:00', '2017-07-14 13:07')
, (4, '2017-07-14 12:08', '2017-07-14 15:31')
;
-- Tested with PostgreSQL 9.6.3
WITH device_presences AS (
SELECT device_id, tstzrange(start_at, end_at, '[)') presence
FROM events)
SELECT minute, count(distinct device_id)
FROM generate_series((SELECT min(start_at) FROM events), (SELECT max(end_at) FROM events), interval '1 minute') AS minutes(minute)
CROSS JOIN device_presences
WHERE presence @> minute
GROUP BY minute
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment