POSTGRES SQL count range of two dates

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

  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

I was trying with this SQL but was not possible to get range longer than 30 minutes, ex: 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

SELECT 
count(agente)
agente,
to_char(to_timestamp(floor((extract('epoch' from datahora_ini) / 1800 )) * 1800) AT TIME ZONE 'UTC','HH24MI') 
FROM callcenter.agente_login
group by agente,  to_char(to_timestamp(floor((extract('epoch' from datahora_ini) / 1800 )) * 1800) AT TIME ZONE 'UTC','HH24MI')

********************UPDATE*********************

I ran the sql solution by Marth and Rémy Baron Still not working ....

Interval 2016-10-03 14:30:00 - 2016-10-03 15:00:00 was online 2 agents, not one...

example:

|| *datahora_ini*      || *datahora_fim*      ||agent||
|| 2016-10-03 09:19:07 || 2016-10-03 19:21:06 || 109 ||
|| 2016-10-03 09:19:07 || 2016-10-03 09:19:50 || 109 ||
|| 2016-10-03 09:32:03 || 2016-10-03 10:40:44 || 138 ||
|| 2016-10-03 09:32:03 || 2016-10-03 09:32:46 || 138 ||
|| 2016-10-03 10:43:32 || 2016-10-03 13:14:55 || 138 ||
|| 2016-10-03 10:43:32 || 2016-10-03 10:44:15 || 138 ||
|| 2016-10-03 14:51:11 || 2016-10-03 17:07:56 || 138 ||
|| 2016-10-03 14:51:11 || 2016-10-03 14:51:53 || 138 ||
|| 2016-10-03 17:07:26 || 2016-10-03 17:08:08 || 138 ||
|| 2016-10-03 17:08:08 || 2016-10-03 17:13:16 || 138 ||
|| 2016-10-03 17:14:55 || 2016-10-03 17:15:38 || 138 ||
|| 2016-10-03 17:15:38 || 2016-10-03 18:51:50 || 138 ||

sql result....

|| *interval*                                ||Cnt||
|| 2016-10-03 12:30:00 - 2016-10-03 13:00:00 || 2 ||
|| 2016-10-03 13:00:00 - 2016-10-03 13:30:00 || 2 ||
|| 2016-10-03 13:30:00 - 2016-10-03 14:00:00 || 1 ||
|| 2016-10-03 14:00:00 - 2016-10-03 14:30:00 || 1 ||
|| 2016-10-03 14:30:00 - 2016-10-03 15:00:00 || 1 ||
|| 2016-10-03 15:00:00 - 2016-10-03 15:30:00 || 2 ||
|| 2016-10-03 15:30:00 - 2016-10-03 16:00:00 || 2 ||
|| 2016-10-03 16:00:00 - 2016-10-03 16:30:00 || 2 ||
|| 2016-10-03 16:30:00 - 2016-10-03 17:00:00 || 2 ||
|| 2016-10-03 17:00:00 - 2016-10-03 17:30:00 || 2 ||
|| 2016-10-03 17:30:00 - 2016-10-03 18:00:00 || 2 ||
|| 2016-10-03 18:00:00 - 2016-10-03 18:30:00 || 2 ||
|| 2016-10-03 18:30:00 - 2016-10-03 19:00:00 || 2 ||
|| 2016-10-03 19:00:00 - 2016-10-03 19:30:00 || 1 ||
|| 2016-10-03 19:30:00 - 2016-10-03 20:00:00 || 0 ||
|| 2016-10-03 20:00:00 - 2016-10-03 20:30:00 || 0 ||
|| 2016-10-03 20:30:00 - 2016-10-03 21:00:00 || 0 ||
|| 2016-10-03 21:00:00 - 2016-10-03 21:30:00 || 0 ||
|| 2016-10-03 21:30:00 - 2016-10-03 22:00:00 || 0 ||
|| 2016-10-03 22:00:00 - 2016-10-03 22:30:00 || 0 ||
|| 2016-10-03 22:30:00 - 2016-10-03 23:00:00 || 0 ||
|| 2016-10-03 23:00:00 - 2016-10-03 23:30:00 || 0 ||

SQL:

WITH min_max_time AS (
  SELECT MIN(datahora_ini::timestamp(0) ), MAX(datahora_fim::timestamp(0) )
  FROM callcenter.agente_login
), periods(time) AS (
  SELECT generate_series(min, max, '30 minutes'::interval)
  FROM min_max_time
)
SELECT
  time || ' - ' || (time + '30 minutes'::interval) as intervalo,
  COUNT(CASE WHEN datahora_ini::timestamp(0)  <= time AND datahora_fim::timestamp(0)  >= time THEN 1 else null end)
FROM callcenter.agente_login, periods
GROUP BY time
ORDER BY time

sqlfiddle or other playground?..

Vao Tsun
– Vao Tsun

2016-11-29 10:47:26 +00:00
Commented Nov 29, 2016 at 10:47 — Vao Tsun
– Vao Tsun, Commented Nov 29, 2016 at 10:47

Marth · Accepted Answer · 2016-11-30 09:51:03Z

2

Use FILTER (PostgreSQL 9.4+) to limit the COUNT(*) to the rows you want:

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
;
┌───────────────────────────────────────────┬───────┐
│                 ?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 │
└───────────────────────────────────────────┴───────┘
(9 rows)

edited Nov 30, 2016 at 9:51

answered Nov 29, 2016 at 11:03

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

3 Comments

Rémy Baron Over a year ago

Before 9.4+ replace "COUNT(*) FILTER ( WHERE login_start <= time AND login_end >= time )" by "COUNT(CASE WHEN login_start <= time AND login_end >= time THEN 1 else null end)"

sealabr Over a year ago

do you have a solution before 9.4+?

Marth Over a year ago

As Remy Baron said, replace the COUNT() FILTER (WHERE <cond>) with SUM(CASE WHEN <cond> 1 ELSE 0 END)

Collectives™ on Stack Overflow

POSTGRES SQL count range of two dates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related