Postgresql: count records by time intervals

Question

I am trying to use the following SQL statement with postgresql to generate some report:

WITH GRID AS (
  SELECT START_TIME, LEAD(START_TIME) OVER (ORDER BY START_TIME) AS END_TIME 
  FROM  (
    SELECT GENERATE_SERIES('2015-08-01 12:00:00', '2015-09-01 12:00:00',  INTERVAL '1 day') AS START_TIME 
    FROM   MY_TABLE
    ) x
  )
  SELECT 
    START_TIME, COUNT(MY_TABLE._ID) AS COUNT 
    FROM
      GRID 
      LEFT   JOIN MY_TABLE ON MY_TABLE.CREATED >= GRID.START_TIME AND MY_TABLE.CREATED <  GRID.END_TIME  
    WHERE MY_TABLE.COMPANY_ID = '1001'  AND MY_TABLE.CUSTOMER_ID = '1003' 
    GROUP  BY 1 ORDER  BY 1

What I expected would be like:

"2015-08-01 12:00:00+02"; 0
"2015-08-02 12:00:00+02"; 1
"2015-08-03 12:00:00+02"; 0
"2015-08-04 12:00:00+02"; 1
"2015-08-05 12:00:00+02"; 0
....
"2015-08-31 12:00:00+02"; 0

but the actual result is:

"2015-08-02 12:00:00+02";1
"2015-08-04 12:00:00+02";1

it simply skips the records with 0 count.

if I don't specify the where clause (condition part), I could get the expected result.

can anyone help me out?

thank you!

Radek Postołowicz · Accepted Answer · 2015-08-13 21:32:39Z

1

Folllowing query seems do what you want:

WITH GRID AS
(
  SELECT 
    START_TIME,
    LEAD(START_TIME) OVER (ORDER BY START_TIME) AS END_TIME
  FROM
  (
    SELECT 
      GENERATE_SERIES('2015-08-01 12:00:00', '2015-09-01 12:00:00', INTERVAL '1 day') AS START_TIME
    FROM MY_TABLE 
  ) x 
)
SELECT 
  START_TIME,
  COUNT(MY_TABLE._ID) AS COUNT
FROM GRID
LEFT JOIN MY_TABLE ON 
  MY_TABLE.CREATED >= GRID.START_TIME AND 
  MY_TABLE.CREATED < GRID.END_TIME AND 
  MY_TABLE.COMPANY_ID = '1001' AND 
  MY_TABLE.CUSTOMER_ID = '1003'
GROUP BY 1
ORDER BY 1

answered Aug 13, 2015 at 21:32

Radek Postołowicz

4,8742 gold badges32 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

honghai Over a year ago

Great! Thanks a lot! it works like a charm. so it seems if I include the condition in where clause, it won't include these items with 0 count.

Radek Postołowicz Over a year ago

Yep. That's how where conditions work - everything in the final output must meets them, whereas left join conditions add data to the output but doesn't cause row to be hidden from output if no data found.

Collectives™ on Stack Overflow

Postgresql: count records by time intervals

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related