Summe über Tagesgrenze und gruppieren nach Datum

[magpie]

Ich möchte Logdaten auswerten. Diese liegen mit einem Timestamp vor und werden alle 30 Sekunden gespeichert. Genau gesagt, geht es um Stromdaten einer PV-Anlage. Ich möchte ermitteln wie viel Strom ich in der Nacht aus dem Netz zukaufe. Ich möchte den die Summe der zugekaufte Strommenge im Zeitraum von heute Morgen um 06:00 und gestern Abend um 18:00 berechnen. Das ganze soll dann pro Tag gruppiert ausgegeben werden. Ich habe die Abfrage mit Beispieldaten nachgestellt.

CREATE TABLE public.test (
    logtime timestamp without time zone,
    logdata smallint
);

insert into test (logtime, logdata) values ('2024-06-20 19:00:00', 2);
insert into test (logtime, logdata) values ('2024-06-20 19:30:00', 3);
insert into test (logtime, logdata) values ('2024-06-20 23:59:00', 5);
insert into test (logtime, logdata) values ('2024-06-21 02:30:00', 1);
insert into test (logtime, logdata) values ('2024-06-21 04:30:00', 4);
insert into test (logtime, logdata) values ('2024-06-21 05:59:00', 10);
insert into test (logtime, logdata) values ('2024-06-21 19:00:00', 2);
insert into test (logtime, logdata) values ('2024-06-21 19:30:00', 3);
insert into test (logtime, logdata) values ('2024-06-21 23:59:00', 5);
insert into test (logtime, logdata) values ('2024-06-22 02:30:00', 3);
insert into test (logtime, logdata) values ('2024-06-22 04:30:00', 7);
insert into test (logtime, logdata) values ('2024-06-22 05:59:00', 10);
 
select
    sum(logdata)
from
    test
where
    logtime between (logtime::date||' 06:00:00')::timestamp - interval '12 hours' and (logtime::date||' 06:00:00')::timestamp
group by
    logtime::date;

Das Problem ist, dass aufgrund der Gruppierung nach dem Datum die Summe nur zwischen Heut 06:00 und Heute 00:00 gebildet wird.

logtime;sum
2024-06-21;15
2024-06-22;20

Wie muss ich die Abfrage gestalten, damit die Summe korrekt bis zum Vortag um 18:00 gebildet wird?


Gruss
Martin

 

[ukulele]

Bin jetzt nicht mit der Postgres Syntax vertraut aber das WHERE wirkt falsch. Ist logtime nicht immer zwischen logtime - 12h und logtime? Da müsste hinter BETWEEN eher sowas wie getdate() oder now() anstelle von der Spalte logtime stehen.

 

[akretschmer]

du suchst offenbar dies:

postgres=# select * from magpie ;
             ts             | wert 
----------------------------+------
 2024-07-01 00:07:07.368894 |   10
 2024-07-01 04:07:22.98491  |   20
 2024-06-30 21:07:35.773908 |   30
(3 rows)

postgres=# select (ts - '12 hour'::interval)::date , sum(wert) from magpie group by 1;
    date    | sum 
------------+-----
 2024-06-30 |  60
(1 row)

postgres=#

 

[castorp]

Das summiert aber nur die Werte pro Kalendertag.
Wenn ich die Frage richtig verstanden habe, ist in diesem Fall aber "ein Tag", die Zeit von "gestern um 18:00 bis heute um 06:00 Uhr".

select log_date, sum(logdata)
from (
  select case
            when logtime::time >= time '18:00' then logtime::date + 1
            when logtime::time <= time '06:00' then logtime::date
            else null
         end as log_date,
         logdata, 
         logtime
  from test
) t
where log_date is not null
group by log_date;

Damit werden die Zeiten von 18:00 bis Mitternacht dem nächsten Tag zugeordnet. Werte die ausserhalb dieser Zeit liegen werden ignoriert. Ist nicht super effizient, weil die innere Abfrage über mehr Datensätze geht als notwendig, aber das könnte man mit einer zusätzlichen WHERE Bedingung in den Griff kriegen. Wenn man die innere Abfrage einzeln ausführt, kann man aber recht einfach analysieren ob die "Tageszuordnung" korrekt ist.

 

[magpie]

Vielen Dank für eure Antworten. @castorp, deine Lösung funktioniert. Danke. Ich bau das nun in meine Abfrage ein und schau wie es sich verhält.