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