Count of rows for each 2 secs in PostgreSQL

Question

I have a data like this:

CREATE TABLE foo(id,dt) AS VALUES
    (  1::int, '2017-08-17 21:36:10'::timestamp  ),
    (  2::int, '2017-08-17 21:36:10'::timestamp  ),
    (  3::int, '2017-08-17 21:36:10'::timestamp  ),
    (  4::int, '2017-08-17 21:36:10'::timestamp  ),
    (  5::int, '2017-08-17 21:36:11'::timestamp  ),
    (  6::int, '2017-08-17 21:36:16'::timestamp  ),
    (  7::int, '2017-08-17 21:36:20'::timestamp  ),
    (  8::int, '2017-08-17 21:36:21'::timestamp  ),
    (  9::int, '2017-08-17 21:37:21'::timestamp  );

I want to get count of rows for each 2 secs, for the data above:

     Time              Count
2017-08-17 21:36:10      5
2017-08-17 21:36:12      0
2017-08-17 21:36:14      0
2017-08-17 21:36:16      1
2017-08-17 21:36:18      0
2017-08-17 21:36:20      2

Is it possible? I'm using PostgreSQL 9.6

Answer 1

Something like this should work.

SELECT gs.dt, count(*)
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
INNER JOIN foo AS f
  ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

First we need to get the min and the max...

SELECT
  date_trunc('second',min(dt)) AS min,
  date_trunc('second',max(dt))+interval '1 second' AS max
FROM foo;
         min         |         max         
---------------------+---------------------
 2017-08-17 21:36:10 | 2017-08-17 21:37:22
(1 row)

now we generate a series with that...

SELECT gs.dt
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt);
         dt          
---------------------
 2017-08-17 21:36:10
 2017-08-17 21:36:12
 2017-08-17 21:36:14
 2017-08-17 21:36:16
 2017-08-17 21:36:18
 2017-08-17 21:36:20
 2017-08-17 21:36:22
 2017-08-17 21:36:24
... etc.

now we just need to join that to our data. Using integer division on the epoch by 2, we can reduce this to every other second..

INNER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)

However if you want the groups with 0, we make that LEFT OUTER JOIN and count just the right side.

SELECT gs.dt, count(f.*)
FROM (
        SELECT
                date_trunc('second',min(dt)) AS min,
                date_trunc('second',max(dt))+interval '1 second' AS max
        FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
LEFT OUTER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

If they're out of order, add an ORDER BY gs.dt

         dt          | count 
---------------------+-------
 2017-08-17 21:36:10 |     5
 2017-08-17 21:36:12 |     0
 2017-08-17 21:36:14 |     0
 2017-08-17 21:36:16 |     1
 2017-08-17 21:36:18 |     0
 2017-08-17 21:36:20 |     2
 2017-08-17 21:36:22 |     0
 2017-08-17 21:36:24 |     0
 2017-08-17 21:36:26 |     0
 2017-08-17 21:36:28 |     0
 2017-08-17 21:36:30 |     0
 2017-08-17 21:36:32 |     0
 2017-08-17 21:36:34 |     0
 2017-08-17 21:36:36 |     0
 2017-08-17 21:36:38 |     0
 2017-08-17 21:36:40 |     0
 2017-08-17 21:36:42 |     0
 2017-08-17 21:36:44 |     0
 2017-08-17 21:36:46 |     0
 2017-08-17 21:36:48 |     0
 2017-08-17 21:36:50 |     0
 2017-08-17 21:36:52 |     0
 2017-08-17 21:36:54 |     0
 2017-08-17 21:36:56 |     0
 2017-08-17 21:36:58 |     0
 2017-08-17 21:37:00 |     0
 2017-08-17 21:37:02 |     0
 2017-08-17 21:37:04 |     0
 2017-08-17 21:37:06 |     0
 2017-08-17 21:37:08 |     0
 2017-08-17 21:37:10 |     0
 2017-08-17 21:37:12 |     0
 2017-08-17 21:37:14 |     0
 2017-08-17 21:37:16 |     0
 2017-08-17 21:37:18 |     0
 2017-08-17 21:37:20 |     1
 2017-08-17 21:37:22 |     0
(37 rows)