Anzeigen Daten letzen 30 Tage oder 1 Monat

Hallo zusammen,
ich versuche gerade die Calls pro Tag der letzten 30 Tage oder 1 Monat anzeigen zu lassen. Allerdings klappt das nicht. Ich bekomme ganz falsche Daten.

so sieht meine Abfrage aus:

Ziel soll es sein, die Anzahl der Calls pro Tag der letzten 30 Tage bzw. 1 Monat zu erhalten.

SELECT count(cd.calls),cd.CONVERT_DATE
FROM (
SELECT count(callid) as calls,to_char(to_timestamp(starttime::bigint/1000),'DD.MM.YYYY') as CONVERT_DATE
FROM cdrsummary
WHERE calledname = 'Hotline Group' AND incoming = 'true' AND answeredbyusername ILIKE '%'
GROUP BY CONVERT_DATE,callid
) as cd
WHERE cd.CONVERT_DATE > to_char(current_date + Interval '-1' month,'DD.MM.YYYY') AND cd.CONVERT_DATE < to_char(current_date,'DD.MM.YYYY')
GROUP BY cd.calls,cd.CONVERT_DATE

Pro Tag klappt das. da bekomme ich auch die richtige Anzahl heraus. dort ist die Abfrage so:

SELECT count(cd.calls),cd.CONVERT_DATE
FROM (
SELECT count(callid) as calls,to_char(to_timestamp(starttime::bigint/1000),'DD.MM.YYYY') as CONVERT_DATE
FROM cdrsummary 
WHERE calledname = 'Hotline Group' AND incoming = 'true' AND answeredbyusername ILIKE '%'
GROUP BY CONVERT_DATE,callid
) as cd
WHERE cd.CONVERT_DATE = to_char(current_date,'DD.MM.YYYY')
GROUP BY cd.calls,cd.CONVERT_DATE

ich hatte es bereits mit Tickets hinbekommen. Dort ist es jedoch eine MySQL Datenbank. Da sieht die Abfrage so aus und da funktioniert es.

SELECT DATE(DATE_FORMAT(t.created,'%Y-%m-%d')) AS 'Datum', COUNT(t.number) AS 'Anzahl'
FROM ticket t
WHERE t.created > NOW() + INTERVAL -1 MONTH AND t.created < NOW()
GROUP BY DATE_FORMAT(t.created,'%Y-%m-%d');

Was habe ich hier falsch oder anders gemacht? Bestimmt geht es auch einfacher. Bin aber selbst ein ziemlicher Anfänger. Wäre toll hier Hilfe zu bekommen.

crosspost zu Anzeigen Daten letzen 30 Tage oder 1 Monat | Datenbank-Forum

Die sinnlosen Umwandlungen des TIMESTAMP Wertes in einen String sind mit hoher Wahrscheinlichkeit die Ursache:

Du solltest all Operationen auf dem Timestamp auch mit timestamp Werten machen, also etwas in der Art:

    SELECT count(callid) as calls, 
            to_timestamp(starttime::bigint/1000)::date as start_date
    FROM cdrsummary
    WHERE calledname = 'Hotline Group' 
      AND incoming = 'true' 
      AND answeredbyusername ILIKE '%'
      AND to_timestamp(starttime::bigint/1000)::date > current_date - interval '1 month'
      AND  to_timestamp(starttime::bigint/1000)::date < current_date
    GROUP BY start_date

Das count(callid) sieht komisch aus. Wenn Du wirklich die Anzahl willst, dann verwende count(*) - das ist offensichtlicher und sogar ein klein wenig schneller.

Wenn Du das Datum wirklich formatiert ausgeben willst, mach’ es nach der Aggregation:

SELECT calls, to_char(start_date, 'dd.mm.yyyy') as start_date
from (
  SELECT count(callid) as calls, 
        to_timestamp(starttime::bigint/1000)::date as start_date
  FROM cdrsummary
  WHERE calledname = 'Hotline Group' 
  AND incoming = 'true' 
  AND answeredbyusername ILIKE '%'
  AND to_timestamp(starttime::bigint/1000)::date > current_date - interval '1 month'
  AND to_timestamp(starttime::bigint/1000)::date < current_date
  GROUP BY start_date
) cd

perfekt! vielen lieben Dank castorp! Das war es was ich gebraucht habe!

Ich halte im Übrigen überhaupt nichts davon Datum/Uhrzeit als “Unix Epoch” zu speichern. Eine Spalte vom Typ timestamp oder timestamptz ist wesentlich besser.

1 „Gefällt mir“

ok. wie wäre es besser oder wie könnte ich das besser machen? in der Spalte “starttime” steht alles in diesem Format “1695994287302” Das ist ja ein Unix Timestamp? Oder verstehe ich dich falsch?

Pro Anruf erhalte ich mehrere Zeilen. Beispielsweise callid: 10789 wenn ich * mache dann zählt er die Zeilen mit. Wenn jetzt pro Anruf 6 Zeilen sind dann wären das 6 calls obwohl es nur einer ist.

Wie gesagt: eine Spalte vom Datentyp timestamp - dann musst Du nicht jedesmal wenn Du irgendwas mit dem Wert machen willst, das mit to_timestamp() in eine timestamp umwandeln. Z.B. durch ein ALTER TABLE:

alter table cdrsummary
  alter starttime type timestamp
        using to_timestamp(starttime::bigint/1000.0);

Dann willst du aber count(distinct callid)

hey, danke castorp, wieder was neues gelernt. :slight_smile:

wenn ich jedoch ALTER TABLE mache für die Spalte “starttime” dann ändert er ja das Datum / Uhrzeit in das normale Format um. Das kann sicherlich Auswirkungen haben wo Abfragen auf diese Felder statt finden mit Unix Timestamp. Korrekt?

Oder könnte man sogar eine neue Spalte erstellen mit alter table starttime_convert, wo diese Angaben permanent auch zur Verfügung stehen?

man kann vieles machen …

postgres=# create table shasamkab(ts_epoc bigint);
CREATE TABLE
postgres=# insert into shasamkab values (1717324375);
INSERT 0 1
postgres=# alter table shasamkab add column ts timestamp generated always as (to_timestamp(ts_epoc)) stored;
ALTER TABLE
postgres=# select * from shasamkab ;
  ts_epoc   |         ts          
------------+---------------------
 1717324375 | 2024-06-02 12:32:55
(1 row)


(1 row)

postgres=# 

nice, akretschmer. Das ist ja genial :smiley:

psql=# insert into public.shasamkab values (1717324376);
INSERT 0 1
psql=# insert into public.shasamkab values (1717324379);
INSERT 0 1
psql=# insert into public.shasamkab values (1717324370);
INSERT 0 1
psql=# select * from public.shasamkab ;
  ts_epoc   |         ts          
------------+---------------------
 1717324375 | 2024-06-02 12:32:55
 1717324376 | 2024-06-02 12:32:56
 1717324379 | 2024-06-02 12:32:59
 1717324370 | 2024-06-02 12:32:50
(4 rows)

psql=# insert into public.shasamkab values (1717328121);
INSERT 0 1
psql=# select * from public.shasamkab ;
  ts_epoc   |         ts          
------------+---------------------
 1717324375 | 2024-06-02 12:32:55
 1717324376 | 2024-06-02 12:32:56
 1717324379 | 2024-06-02 12:32:59
 1717324370 | 2024-06-02 12:32:50
 1717328121 | 2024-06-02 13:35:21
(5 rows)

könnte man auch eine neue Tabelle bauen wo solche Angaben aus verschiedenen Tabellen kommen?

Tabelle NEU:
ts,callnumber,callername

ts kommt aus Tabelle CDR → Besonderheit hier ts_epoc soll in ts umgewandelt werden in der Tabelle NEU.

callnumber aus Tabelle CDRSummary

callername aus Tabelle AgentState

ja, im Prinzip ja, als VIEW.

ok, wie würde das gehen?

Im Prinzip hätte ich dann eine Tabelle view_allsummary und da kann ich die Werte live rein portieren?

hast Du echt noch nicht CREATE VIEW als Abfrage-Alias gesehen?

postgres=# create table namen (id int primary key, name text);
CREATE TABLE
postgres=# create table auftrag(id int primary key, name int references namen);
CREATE TABLE
postgres=# insert into namen values (1, 'Max');
INSERT 0 1
postgres=# insert into namen values (2, 'Susi');
INSERT 0 1
postgres=# insert into auftrag values (1, 1);
INSERT 0 1
postgres=# insert into auftrag values (2, 1);
INSERT 0 1
postgres=# insert into auftrag values (3, 2);
INSERT 0 1
postgres=# create view my_view as select a.id, n.name from auftrag a left join namen n on a.name=n.id;
CREATE VIEW
postgres=# select * from my_view ;
 id | name 
----+------
  1 | Max
  2 | Max
  3 | Susi
(3 rows)

postgres=#

Beim select auf die View wird die zugrunde liegende Abfrage ausgeführt, ist halt nur kürzer.

danke. ich bin kompletter Anfänger. Gerde mal seit 4 Wochen mit SQL aktiv. :smiley:

die Firma, für die ich arbeite, bietet auch Schulungen an :wink:

kannst mir gerne mal eine PN schreiben :smiley: Danke für das Angebot!

was mache ich nun falsch?

asterisk=# SELECT starttime FROM cdrdata;
   starttime
---------------
 1695994287302
 1695994287478
 1695994287538
 1695994298730
 1695994298762

Befehl schlägt leider fehl

asterisk=# alter table cdrdata add column dt_starttime timestamp generated always as (to_timestamp(starttime)) stored;
ERROR:  syntax error at or near "("
LINE 1: ...column dt_starttime timestamp generated always as (to_timest...
                                                             ^
asterisk=# alter table cdrdata add column dt_starttime timestamp generated always as (to_timestamp(cdrdata.starttime)) stored;
ERROR:  syntax error at or near "("
LINE 1: ...column dt_starttime timestamp generated always as (to_timest...

was hast Du für eine Version am Start?