Last active
August 2, 2017 16:46
-
-
Save francois/4c0be6c620bbaa1ce889fe92dc35d0cd to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
... | |
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 | |
... |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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') | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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