Minuten berechnen innerhalb einer Tabelle

[toto1975]

Hallo in die Runde,
ich habe eine Tabelle mit zwei Spalten und folgenden Inhalt
Time | Value
10.09.2015 14:30:15 | on
10.09.2015 14:50:15 | off
10.09.2015 15:30:05 | on
10.09.2015 15:40:05 | off
Usw.
Jetzt möchte ich gerne die Zeit erfassen wie lange der Wert auf on steht, also von 14:30:15 – 14:50:15 = 20 Minuten. Die Minuten sollten wenn möglich nach Monaten gruppiert werden. Also ungefähr so:
Monat Jahr | Minuten
Januar 2015 | 158
Februar 2015 | 487
März 2015 | 468
usw.
Ich komme aber nicht auf die Lösung. Mein versuch zeigt mir zwar die richtigen Werte an aber leider nicht im Zeitformat.

select SUM(Bla)/60
FROM
(
SELECT Time, Value
,(SELECT Time FROM `Item22` as b WHERE b.Time > a.Time AND b.Value="OFF" ORDER BY Time LIMIT 1) as OffTime
,((SELECT Time FROM `Item22` as b WHERE b.Time > a.Time AND b.Value="OFF" ORDER BY Time LIMIT 1) - Time) as Bla
 
FROM `Item22` as a
WHERE Value ="ON"
    ) as Blupp

Ich bin für jeden Tipp dankbar
Viele Grüße
Torsten

 

[rammi22]

Was willst du von wo abziehen? Haben die Datensätze eindeutige ID´s?

Ansonsten, wenn die Spalte Time richtig eingestellt ist (DATETIME) ist die Berechnung kein Thema:

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55')- UNIX_TIMESTAMP('2010-11-29 13:13:55')as output
->
output
-------
180

schaust du hier

 

[akretschmer]

Ich komme aber nicht auf die Lösung

Mit MySQL schwer. Mit Window-Funktionen, die MySQL nicht kann, ginge es so:

test=*# select * from toto ;
  time  | val
---------------------+-----
 2015-09-10 14:30:15 | t
 2015-09-10 14:50:15 | f
 2015-09-10 15:30:05 | t
 2015-09-10 15:40:05 | f
(4 rows)

Time: 0,160 ms
test=*# select *, bis - von as dauer from (select time as von, lead(time) over (order by time) as bis, val from toto ) bla where val;
  von  |  bis  | val |  dauer
---------------------+---------------------+-----+----------
 2015-09-10 14:30:15 | 2015-09-10 14:50:15 | t  | 00:20:00
 2015-09-10 15:30:05 | 2015-09-10 15:40:05 | t  | 00:10:00
(2 rows)

Time: 0,345 ms

Gruppierung nach Monaten oder so ginge via JOIN auf eine Hilfstabelle, die die Monate enthält. Ginge mit PostgreSQL auch sehr einfach, zeige ich jetzt aber nicht, weil es in MySQL auch ned geht. Pech für Dich, falsche DB gewählt.

 

[akretschmer]

Mal noch a bissl gespielt...

test=*# select * from toto ;
  time  | val
---------------------+-----
 2015-09-10 14:30:15 | t
 2015-09-10 14:50:15 | f
 2015-09-10 15:30:05 | t
 2015-09-10 15:40:05 | f
(4 rows)

Time: 0,157 ms
test=*# select * from (select tsrange('2015-09-10 00:00:00'::timestamp + s * '1hour'::interval, '2015-09-10 00:00:00'::timestamp + (1+s) *'1hour'::interval) as stunde from generate_series(0,23) s) stundenintervalle left join (select ('[' || von::text || ',' || bis::text || ')')::tsrange eingeschaltet, bis - von as dauer from (select time as von, lead(time) over (order by time) as bis, val from toto ) bla where val) einschaltzeiten on stundenintervalle.stunde @> einschaltzeiten.eingeschaltet;
  stunde  |  eingeschaltet  |  dauer
-----------------------------------------------+-----------------------------------------------+----------
 ["2015-09-10 00:00:00","2015-09-10 01:00:00") |  |
 ["2015-09-10 01:00:00","2015-09-10 02:00:00") |  |
 ["2015-09-10 02:00:00","2015-09-10 03:00:00") |  |
 ["2015-09-10 03:00:00","2015-09-10 04:00:00") |  |
 ["2015-09-10 04:00:00","2015-09-10 05:00:00") |  |
 ["2015-09-10 05:00:00","2015-09-10 06:00:00") |  |
 ["2015-09-10 06:00:00","2015-09-10 07:00:00") |  |
 ["2015-09-10 07:00:00","2015-09-10 08:00:00") |  |
 ["2015-09-10 08:00:00","2015-09-10 09:00:00") |  |
 ["2015-09-10 09:00:00","2015-09-10 10:00:00") |  |
 ["2015-09-10 10:00:00","2015-09-10 11:00:00") |  |
 ["2015-09-10 11:00:00","2015-09-10 12:00:00") |  |
 ["2015-09-10 12:00:00","2015-09-10 13:00:00") |  |
 ["2015-09-10 13:00:00","2015-09-10 14:00:00") |  |
 ["2015-09-10 14:00:00","2015-09-10 15:00:00") | ["2015-09-10 14:30:15","2015-09-10 14:50:15") | 00:20:00
 ["2015-09-10 15:00:00","2015-09-10 16:00:00") | ["2015-09-10 15:30:05","2015-09-10 15:40:05") | 00:10:00
 ["2015-09-10 16:00:00","2015-09-10 17:00:00") |  |
 ["2015-09-10 17:00:00","2015-09-10 18:00:00") |  |
 ["2015-09-10 18:00:00","2015-09-10 19:00:00") |  |
 ["2015-09-10 19:00:00","2015-09-10 20:00:00") |  |
 ["2015-09-10 20:00:00","2015-09-10 21:00:00") |  |
 ["2015-09-10 21:00:00","2015-09-10 22:00:00") |  |
 ["2015-09-10 22:00:00","2015-09-10 23:00:00") |  |
 ["2015-09-10 23:00:00","2015-09-11 00:00:00") |  |
(24 rows)

Das ist nun mal eine Anzeige, in welchen Stunden des Tages da das eingeschaltet war. Man müßte noch berücksichtigen, daß die Dauer-Berechnung noch auf die Zeiten der betrachteten Stundenintervalle begrenzt werden müßte, falls eine Einschaltzeit über solch eine Grenze geht. Aber das wäre nun auch kein großer Aufwand mehr, wenn man wie gezeigt mit den TSRANGE-Typen arbeitet.

Ja, in MySQL hast das alles nicht, da wäre der Aufwand um einiges größer. Vielleicht hilft Dir jemand mit mehr Liebe zu MySQL hier weiter...

 

[toto1975]

Was willst du von wo abziehen? Haben die Datensätze eindeutige ID´s?

Ansonsten, wenn die Spalte Time richtig eingestellt ist (DATETIME) ist die Berechnung kein Thema:

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55')- UNIX_TIMESTAMP('2010-11-29 13:13:55')as output
->
output
-------
180

schaust du hier

Hallo rammi22,

mit Unix_TIMESTAMP habe ich das Problem das ich ja nur eine Spalte habe

SELECT ABS(UNIX_TIMESTAMP(t.datetime_col1) - UNIX_TIMESTAMP(t.datetime_col2)) as outpu

ist ja leider nicht möglich da die Tabelle diesen Aufbau hat:

Time | Value
10.09.2015 14:30:15 | on
10.09.2015 14:50:15 | off
10.09.2015 15:30:05 | on
10.09.2015 15:40:05 | off

oder verstehe ich da etwas falsch. Hintergrund ist dass ich die Minuten brauche von on bis zum off (also Zeile 1 bis Zeile2, Zeile 3 - Zeile 4) also wie lange war der Wert auf on.

Time ist richtig eingestellt und hat das Format DATETIME

Sorry aber ich habe bis jetzt noch nicht sehr viel mit MYSQL gemacht und stehe erst am Anfang und benötige eigentlich auch nur diese eine Lösung. Ist von mein SmartHome System und ich möchte nur wissen wie Lange die Lampen an waren.

Wenn mir hier noch mal auf die Sprünge helfen könntet wäre echt super

Viele Grüße
Torsten

 

[rammi22]

...ich möchte nur wissen wie Lange die Lampen an waren....

Also ich würde so vorgehen (nur eine Möglichkeit):

2 Tabellen:
1. Tabelle Lampe:
id;time;value

2. Tabelle verbrauchte_zeit
id;month;usedTime

1. Die Lampe wird angeschalten -> Datenbank- Update mit in der Zeile mit der id der Lampe
id;Time;Value
0;DATETIME NOW();1

2.Die Lampe wird ausgeschalten-> Datenbank- Update mit in der Zeile mit der id der Lampe
a. Berechne den Zeitunterschied zwischen dem Eintrag in Time mit dem aktuellen DateTime siehe oben
b. Update die Tabelle verbrauchte_zeit mit dem Ergebniss + usedTime
c. Update Tabelle lampe
id;time;value
0;NULL;0

 

[akretschmer]

Und was soll dann in der DB stehen, wenn man es so wie Du vorschlägst macht, und die Lampe 42 mal an-aus - geschalten wurde? Wie bekommst Du das Log, von wann bis wann die Lampe an war? Wie willst Du auswerten, wie lange die Lampe in Monat Mai, Juni Juli, ... an war?

 

[toto1975]

Das wäre mit Sicherheit eine gute Lösung allerdings schreibt das System die Daten selbst in die DB und ich kann leider auch nichts daran ändern. Das Format der DB ist also vorgegeben:

Time | Value
10.09.2015 14:30:15 | on
10.09.2015 14:50:15 | off
10.09.2015 15:30:05 | on
10.09.2015 15:40:05 | off

Hast du da noch eine andere Idee?

 

[rammi22]

Jetzt bringst du mich durcheinader...

Und was soll dann in der DB stehen, wenn man es so wie Du vorschlägst macht, und die Lampe 42 mal an-aus - geschalten wurde?

dann wird die Datenbank 42 mal upgedatet

Wie bekommst Du das Log, von wann bis wann die Lampe an war?

die Frage war, wie lange eine Lampe im Monat genutzt wurde...das ist eben die Summe aus dem errechneten Ergebniss mit dem Value der usedTime

Wie willst Du auswerten, wie lange die Lampe in Monat Mai, Juni Juli, ... an war?

Indem ich aus den aktuellen Monat mit dem aus dem DateTime aus der time-Spalte vergleiche...

 

[rammi22]

...Hast du da noch eine andere Idee?

Sieht die Tabelle genau so aus? Das kann ich mir kaum vorstellen...

 

[akretschmer]

Nur ganz kurz: Du addierst fortwährend die Zeiten, Du hast keine Information mehr, wann die Lampe an war. Nach langer, langer Zeit, also vielen Jahren, hast Du eine Summe: die Lampe war insgesamt 4711 Stunden lang an. Aber wie lange am 12. Januar 2014, wie lange am 30 Juli 2015, brannte sie zwischen 10 und 12 Uhr am 10 September 2015? Du führst eine monatliche Kummulierung, was ist aber, wenn eine wöchentliche Auswertung gefragt ist? Du wirst relevante Informationen einfach weg.

Aus den Daten des Fragestellers ließen sich alle Informationen berechnen, nur leider bietet MySQL kaum/keine analytischen Abfragemöglichkeiten. Das ist das Problem.

 

[akretschmer]

Sieht die Tabelle genau so aus? Das kann ich mir kaum vorstellen...

Mag sein. Ich mir schon.

 

[rammi22]

...Aber wie lange am 12. Januar 2014, wie lange am 30 Juli 2015...

Gut, dann habe ich die Frage falsch verstanden, ich dachte er möchte nur die Nutzungsdauer / Monat wissen.

...Aus den Daten des Fragestellers ließen sich alle Informationen berechnen...

Ich sehe da keinen Bezug... ist es nur eine Lampe, oder 2? Zu welcheer gehört welcher Zustand...Fragen über Fragen, deshalb wundert mich die Tabelle

Gruss Rammi

 

[akretschmer]

Ich sehe da keinen Bezug... ist es nur eine Lampe, oder 2? Zu welcheer gehört welcher Zustand...Fragen über Fragen, deshalb wundert mich die Tabelle

die gegebene Tabelle um eine Spalte mit der ID der Lampe (oder was auch immer) zu erweitern ist nun eher eine Anfängerübung, oder?

 

[toto1975]

Hallo,

um ein wenig Licht ins Dunkel zu bringen.... es ist eine Lampe. Ich habe mal ein Screenshot aus phpmyadmin gemacht. Der Name der Tabelle, hier Item22, ist das Gerät. Eine weitere Tabelle (Items) hat die Bezeichnung der einzelnen Geräte also Item22 | Lampe Wohnzimmer, Item23 | Lampe Küche, usw. Für jedes Gerät gibt es eine eigene Tabelle. Sieht komisch aus ist aber leider so. Die Temperaturen lassen sich ja relativ leicht bearbeiten mit

Select week(Time), Year(Time), avg(Value)
from Item8
group by week(Time),Year(Time)

Ich habe versucht in die Tabelle ein weitere Spalte einzufügen für die ID allerdings werden dann vom Smart Home System keine Werte mehr in die Tabelle geschrieben :-(

Gibt es da wirklich keine andere Möglichkeit zu erfassen wie lange ein Gerät eingeschaltet ist? Dann macht ja die ganze Protokollierung keinen Sinn :-(