Rewrite query for version 9.2

Question

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, '[]') && 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+.

Answer 1

The only issue seems to be the FILTER clause. You can rewrite:

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

as:

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

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