2

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

2
  • 2
    I don't have time at the moment to write up the query for you but yes it is possible. You can use generate series to create a table with values every 2 seconds. stackoverflow.com/questions/14113469/… You can then join using > and < to compare the times. Hope it's enough to get you started. – indiri Sep 1 '17 at 23:41
  • Where does 21:37:21 go from your sample data? – Evan Carroll Sep 2 '17 at 17:13
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.