Rewrite query for version 9.2 I want to count a range between two dates, like this example:


  AGENT   | LOGIN_START          | LOGIN_END
  --------+----------------------+---------------------
  101     |  2016-01-01 06:00:00 | 2016-01-01 06:29:59
  102     |  2016-01-01 06:00:00 | 2016-01-01 08:20:00
  103     |  2016-01-01 06:00:00 | 2016-01-01 06:01:00
  101     |  2016-01-01 10:00:00 | 2016-01-01 10:01:00
  101     |  2016-01-01 10:02:00 | 2016-01-01 10:03:00


...and separate hours between 30 minutes and count by agent, just an example:


  TIME            | LOGIN_QTY         
  ----------------+-----------
  06:00 - 06:30   |  3
  06:30 - 07:00   |  1
  07:00 - 07:30   |  1
  07:30 - 08:00   |  1
  08:00 - 08:30   |  1
  10:00 - 10:30   |  1


Problem: agent 102 was online since 6:00 till 8:20, and need to count in ranges => 06:00 - 06:30, 06:30 - 07:00, 07:00 - 07:30, 07:30 - 08:00 , 08:00 - 08:30

Here's my SQL:

WITH min_max_time AS (
  SELECT MIN(login_start), MAX(login_end)
  FROM agents
), periods(time) AS (
  SELECT generate_series(min, max, '30 minutes'::interval)
  FROM min_max_time
)
SELECT
  time || ' - ' || (time + '30 minutes'::interval),
  COUNT(*) FILTER ( WHERE 
    tsrange(login_start, login_end, '[]') &amp;&amp; tsrange(time, time + '30 minutes'::interval, '[]')
  )
FROM agents, periods
GROUP BY time
ORDER BY time
;


Result:


┌───────────────────────────────────────────┬───────┐
│                 ?column?                  │ count │
├───────────────────────────────────────────┼───────┤
│ 2016-01-01 06:00:00 - 2016-01-01 06:30:00 │     3 │
│ 2016-01-01 06:30:00 - 2016-01-01 07:00:00 │     1 │
│ 2016-01-01 07:00:00 - 2016-01-01 07:30:00 │     1 │
│ 2016-01-01 07:30:00 - 2016-01-01 08:00:00 │     1 │
│ 2016-01-01 08:00:00 - 2016-01-01 08:30:00 │     1 │
│ 2016-01-01 08:30:00 - 2016-01-01 09:00:00 │     0 │
│ 2016-01-01 09:00:00 - 2016-01-01 09:30:00 │     0 │
│ 2016-01-01 09:30:00 - 2016-01-01 10:00:00 │     0 │
│ 2016-01-01 10:00:00 - 2016-01-01 10:30:00 │     1 │
└───────────────────────────────────────────┴───────┘


I need to rewrite the query for Postgres versions before 9.4+.
The only issue seems to be the FILTER clause. You can  rewrite:

COUNT(*) FILTER ( WHERE tsrange(login_start, login_end, '[]') 
                  &amp;&amp; tsrange(time, time + '30 minutes'::interval, '[]')
                )


as:

COUNT(CASE WHEN tsrange(login_start, login_end, '[]') 
                &amp;&amp; tsrange(time, time + '30 minutes'::interval, '[]')
           THEN 1 END
     )


I would also use inclusive-exclusive intervals: [). Otherwise some rows will be counted twice.