Trying to write a query for 3CX outgoing call duration stats for the current day

Question

So far I have managed to write a query to list all the outgoing calls made by a user and their duration, but what I'm looking for is, rather than a list of all the individual calls, a total of the duration.

SELECT starttime, duration, is_answ, is_fromoutside, from_no
  FROM callhistory3
 WHERE is_answ = 't'
   AND is_fromoutside = 'f'
   AND starttime >= CURRENT_DATE
   AND from_no = '101';

The duration is in the format 00:00:00:00.000 (Days:Hours:Minutes:Seconds.Hundredths), so my question is really how do I add this up and present it as a single number for duration, rather than a list of individual calls each with their own separate duration?

My final query is as follows to anyone that is interested:

SELECT from_no, SUM(duration), COUNT (*)
  FROM callhistory3
 WHERE is_answ = 't'
   AND is_fromoutside = 'f'
   AND starttime >= CURRENT_DATE
 GROUP BY from_no
 ORDER BY from_no;

Duration should be measured in ... (seconds / minutes)? Please, show definition of the table. — vyegorov, May 09 '12 at 16:23
That's a very poorly designed table. Any solution people come up with here will not be very performant. — Mahmoud Al-Qudsi, May 09 '12 at 16:24
Yes the comma is a typo, thanks for pointing that out! The duration should be a single total in Hours:Minutes:Seconds — Fraserrr, May 09 '12 at 16:47

kgrittn · Accepted Answer · 2012-05-10T12:27:40.463

0

If the duration really always has exactly that format:

SELECT from_no, sum(duration)
  FROM callhistory3
  WHERE is_answ = 't'
    AND is_fromoutside = 'f'
    AND starttime >= CURRENT_DATE
    AND from_no = '101'
  GROUP BY from_no
  ORDER BY from_no;

Well, if you really are just doing it for one from_no you could simplify it by just selecting the sum() and leaving off the GROUP BY and ORDER BY clauses, but it seems likely you might want a list; for that, you can just change the WHERE clause above.

edited May 10 '12 at 12:27

answered May 09 '12 at 17:13

kgrittn

18,113
3
39
47

Hi, thanks for the help and reply, i couldnt seem to get that to work unfortunately, i kept getting: [Err] ERROR: function left(interval, integer) does not exist LINE 1: SELECT from_no, sum((left(duration, 2) || ' ' ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Fraserrr May 10 '12 at 10:31
This is why it's a good idea to actually provide `CREATE TABLE` statements to show what you have. You described the column as a string in a particular format, while it's actually an interval. That makes the query simpler. I will edit based on `duration` being of type `interval`. – kgrittn May 10 '12 at 12:26

Trying to write a query for 3CX outgoing call duration stats for the current day

1 Answers1