0

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+.

4

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.

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.