Abfrage: Umwandlung Sekunden in Zeit + Auswertung pro Tag

[shasamkab]

Hallo zusammen,
irgendwie komme ich nicht weiter und brauche Hilfe, wie ich die Durschnittszeit der Calls am Tag berechnen kann. Ich habe die Angabe in der Spalte duration in Sekunden. Diese möchte ich im Format HH.MI.SS haben.

Die CallID ist die Eindeutige Zeile in der cdrdata. Hier mal meine Abrage, die irgendwie nicht funktioniert.
Sicherlich auch eine etwas übertriebene Abfrage als Halbwissender

SELECT avg(ct.time_duration),ct.CONVERT_TIME
FROM (
SELECT 
avg(duration) 
AS time_duration,
to_char(to_timestamp(duration),'HH24:MI:SS')
AS CONVERT_TIME, 
(callid) 
AS calls,
to_char(to_timestamp(starttime::bigint/1000),'DD.MM.YYYY') 
AS CONVERT_DATE
FROM cdrdata 
WHERE calledcallerid = '10'
AND callresult = 'CONNECTED'
GROUP BY CONVERT_TIME,duration,callid,starttime
) AS ct
WHERE ct.CONVERT_DATE = to_char(current_date,'DD.MM.YYYY')
GROUP BY ct.time_duration,ct.CONVERT_TIME, ct.callid

[laurenz]

Wenn ich die Bedeutung der Spalten richtig verstehe, sollte das funktionieren:

SELECT day, avg(dur) AS avg_duration
FROM (SELECT /* "duration" in Sekunden */
             make_interval(secs => duration) AS dur,
             /* "starttime" in Millisekunden seit Mitternacht 1.1.1970 UTC */
             CAST (to_timestamp(starttime::double precision / 1000)
                   AT TIME ZONE 'UTC' AS date) AS day
      FROM cdrdata
      WHERE calledcallerid = '10'
        AND callresult = 'CONNECTED') AS q
GROUP BY day;

Die Kommentare beschreiben meine Annahmen.

[shasamkab]

habe ich ausprobiert und bekomme diesen Fehler:

ERROR:  syntax error at or near "STAMP"
LINE 6:                    AT TIME STAMP 'UTC' AS date) AS day
                                   ^ 

SQL state: 42601
Character: 311

[castorp]

@shasamkab

AT TIME STAMP

Das muss AT TIME ZONE heißen.

[shasamkab]

aah, das hat schonmal weitergebracht. jetzt kommt aber das hier:

ERROR:  column "cdrdata.starttime" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5:              CAST (to_timestamp(starttime::double precision ...
                                        ^ 

SQL state: 42803
Character: 248

[laurenz]

Tut mir leid, das eine avg() war zu viel.
Ich habe meine ursprüngliche Antwort korrigiert.

[shasamkab]

nice, Danke! Das funktioniert jetzt. So sieht das aus.

1. Wie bekomme ich die Millisekunden raus?
2. Wenn ich das auf den heutigen Tag immer filtern möchte?

[laurenz]

Nur die Daten des heutigen Tages?
Das ist leichter. “Heute” in UTC oder in einer anderen Zeitzone?

[shasamkab]

also nur day: 2024-06-04 und avg_duration: 00:01:42

[laurenz]

Wenn die Dauer in Millisekunden sein soll und es um “heute” in UTC geht, dann

SELECT round(avg(duration::double precision)) AS avg_duration
FROM cdrdata
WHERE (to_timestamp(starttime::double precision / 1000)
       AT TIME ZONE 'UTC')::date =
      (current_timestamp
       AT TIME ZONE 'UTC')::date
  AND calledcallerid = '10'
  AND callresult = 'CONNECTED';

[shasamkab]

laurenz, danke dir!

ich wollte nich tin Millisekunden, sondern ich wollte die MS weg haben. avg_duration sollte dann so aussehen 00:01:42 (HH:MI:SS) - aktuell sieht es so aus 00:1:42.5 …

[laurenz]

Hier als interval, gerundet auf Sekunden:

SELECT make_interval(secs => round(avg(duration::double precision))) AS avg_duration
FROM cdrdata
WHERE (to_timestamp(starttime::double precision / 1000) AT TIME ZONE 'UTC')::date
      = (current_timestamp AT TIME ZONE 'UTC')::date
  AND calledcallerid = '10'
  AND callresult = 'CONNECTED';

[shasamkab]

heheheh, nice!