3

I need to SUM Time of this Interval and Group by 30 and 30 minutes.

CREATE TABLE foo AS
SELECT date_start::timestamp,
  date_end::timestamp,
  agent
FROM ( VALUES
  ('2017-01-06 06:52','2017-01-06 06:52',164),
  ('2017-01-06 06:56','2017-01-06 07:25',164),
  ('2017-01-06 06:56','2017-01-06 07:05',169),
  ('2017-01-06 06:56','2017-01-06 10:29',142),
  ('2017-01-06 06:57','2017-01-06 07:23',116)
) AS t(date_start,date_end,agent);

like this example: need to group by 30 and 30 minutes and sum real dates.

    period          |   sum_times
---------------------------------
06:30:00 - 07:00:00 |   00:12:36
07:00:00 - 07:30:00 |   01:24:15
07:30:00 - 08:00:00 |   0:30:00
08:00:00 - 08:30:00 |   0:30:00
08:30:00 - 09:00:00 |   0:30:00
09:00:00 - 09:30:00 |   0:30:00
09:30:00 - 10:00:00 |   0:30:00
10:00:00 - 10:30:00 |   0:29:40

Sql code:

WITH min_max_time AS (
  SELECT MIN(datahora_ini::timestamp(0) ), MAX(datahora_fim::timestamp(0) )
  FROM callcenter.agente_login
  WHERE  datahora_ini >= '2017-01-06 00:00:00' AND datahora_ini <= '2017-01-06 23:59:59'
), periods(time) AS (
  SELECT generate_series(min, max, '30 minutes'::interval)
  FROM min_max_time
), w_agentes_logados AS (
SELECT
  CONCAT(to_char((time),'YYYYMMDD'), to_char(to_timestamp(floor((extract('epoch' from time) / 1800 )) * 1800) AT TIME ZONE 'UTC','HH24MI') ) as id_chamadas_receptiva,
  SUM(CASE WHEN datahora_ini::timestamp(0)  <= time AND datahora_fim::timestamp(0)  >= time THEN EXTRACT(EPOCH FROM (datahora_fim - datahora_ini)) else 0 end) as quantidade

FROM callcenter.agente_login, periods
GROUP BY time
)

SELECT * FROM w_agentes_logados

expected result, but it's wrong it's not returning the real result.

time_id           | qty (seconds)
----------------------------------
201701060630          | 9   
201701060700          | 103418  
201701060730          | 112410  
201701060800          | 137369  
201701060830          | 279747  
201701060900          | 370753  
201701060930          | 372496  
3
  • Dont have a system to hand, but I found Googling "group by timestamp period", particularly helpful, especially here - look at entire thread. Even though question is MySQL, PostgreSQL is also dealt with. Take a look at some of the other Google links also. – Vérace Jan 11 '17 at 2:22
  • Can you provide the DDL to create this stuff? meta.dba.stackexchange.com/a/2642/2639 – I Support The Boycott Jan 11 '17 at 2:44
  • What's an interval from '2017-01-06 06:52' to '2017-01-06 06:52' (same timestamp) supposed to mean? Are overlapping time periods counted multiple times for the sum? – Erwin Brandstetter Jan 11 '17 at 4:47
3

I've got a ton of questions here,

  1. Are these all the same day?
  2. What should happen if there is no overlap in time?

Let's also focus on this table from the times of 6:30-7:00

     date_start      |      date_end       | overlap  
---------------------+---------------------+----------
 2017-01-06 06:52:00 | 2017-01-06 06:52:00 | 
 2017-01-06 06:56:00 | 2017-01-06 07:25:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 07:05:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 10:29:00 | 00:04:00
 2017-01-06 06:57:00 | 2017-01-06 07:23:00 | 00:03:00

So how do you get this in your ideal result set?

06:30:00 - 07:00:00 |   00:12:36

Please, value our time... When you create sample data, it's important for you to be sure you're not misleading us. I'm sure it's an innocent mistake, but double check next time. Moreover, this should set you down the right path to get this job done..

SELECT
  tsrange,
  sum(
    UPPER(tsrange*tsrange(date_start,date_end)) -
    LOWER(tsrange*tsrange(date_start,date_end))
  )
FROM foo
JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)
GROUP BY tsrange
ORDER BY tsrange
;

Let's break it down, here we generate the times in 30 minute intervals as a timestamp range..

JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)

Then we join this set on an overlap test. The only other clever unintuitive thing is this,

sum(
  UPPER(tsrange*tsrange(date_start,date_end)) -
  LOWER(tsrange*tsrange(date_start,date_end))
)

In my opinion tsrange - tsrange should return an interval (or a cast should be provided). One isn't. So we have to do what's done above. This provides us the duration of the overlap that we previously detected with &&. We can sum(interval) and produce the result set you want..

                    tsrange                    |   sum    
-----------------------------------------------+----------
 ["2017-01-06 06:30:00","2017-01-06 07:00:00") | 00:15:00
 ["2017-01-06 07:00:00","2017-01-06 07:30:00") | 01:23:00
 ["2017-01-06 07:30:00","2017-01-06 08:00:00") | 00:30:00
 ["2017-01-06 08:00:00","2017-01-06 08:30:00") | 00:30:00
 ["2017-01-06 08:30:00","2017-01-06 09:00:00") | 00:30:00
 ["2017-01-06 09:00:00","2017-01-06 09:30:00") | 00:30:00
 ["2017-01-06 09:30:00","2017-01-06 10:00:00") | 00:30:00
 ["2017-01-06 10:00:00","2017-01-06 10:30:00") | 00:29:00
(8 rows)
5
  • What does the t() in AS t(ts) do? – bugmenot123 Feb 19 '20 at 16:46
  • @bugmenot123 table alias (t) column alias (ts) – I Support The Boycott Feb 19 '20 at 16:52
  • Sorry, I don't understand. It seems to work the same if I just say ts without the t() around it. – bugmenot123 Feb 19 '20 at 16:57
  • sure.. it would work without the ts too. As a practice, I always use table aliases and column aliases when I'm projecting data. If there is no collisions though, they're not needed. You do you. – I Support The Boycott Feb 19 '20 at 17:02
  • So t() is explicit long-form for "table alias"? – bugmenot123 Feb 19 '20 at 17:23
2

This counts overlapping time periods multiple times:

SELECT i_start, sum(LEAST(i_end, date_end) - GREATEST(i_start, date_start)) AS sum_times
FROM  (
   SELECT t AS i_start, t + interval '30 min' AS i_end
   FROM  (SELECT generate_series(date_trunc('hour', min(date_start))
                                                  , max(date_end), interval '30 min') AS t
          FROM foo) sub
   ) t
JOIN   foo ON date_start < i_end
          AND date_end   > i_start
WHERE  date_end > date_start  -- exclude nonsense entries in sample data
GROUP  BY 1
ORDER  BY 1;

Related:

You might want to use a smart index for big tables:

I would add more explanation ... if you would do the same.

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.