SQLite Abfrage Min Max von einem Tag

  • Hallo Zusammen,

    ich habe ein kleines Problem bei einer Abfrag in SQLite.

    Ich frage von Openweathermap verschiedene Daten ab. Unter anderem, die Temperatur, Luftfeuchtigkeit und die Windgeschwindigkeit.

    Aufgebaut ist SQLite so:

    Code
    id   |zeit                |temp |feuchtigkeit |wind
    2108 |2019-09-30 13:40:02 |18.43|48           |8.7
    2107 |2019-09-30 13:30:06 |17.99|48           |8.7
    2106 |2019-09-30 13:20:02 |17.77|48           |8.7
    2105 |2019-09-30 13:10:02 |17.83|45           |9.3
    2104 |2019-09-30 13:00:35 |17.89|45           |9.3
    2103 |2019-09-30 12:50:02 |17.92|45           |9.3
    2102 |2019-09-30 12:40:02 |17.49|51           |10.3
    2101 |2019-09-30 12:30:18 |17.57|51           |10.3
    2100 |2019-09-30 12:20:02 |17.41|51           |10.3

    Ich frage Openweathermap alle 10 Minute ab und trage die Daten in die SQLite DB ein.

    Jetzt würde ich gerne den Durchschnitt und gerne die Max und Min Werte zb. von "temp" haben über einen Tag.

    Führe ich folgenden Befehl aus:

    Code
    select id, min(temp) from wetterdaten;

    erhalte ich als Ausgabe:

    Code
    622|2.8

    Soweit auch so richtig, ich will den niedrigsten Wert in der DB haben.

    Frage ich jetzt allerdings so ab:

    Code
    select id, min(temp) from wetterdaten order by id desc limit 1;

    Er halte ich auch folgende Ausgabe:

    Code
    622|2.8

    Ich grenze meine Abfrage allerdings auf den letzten Wert ein, wie kann das sein, dass hier auch der Wert aus Zeile 622 raus kommt?

    Ich würde gerne die Abfrage immer um 00:00 Uhr machen. Somit kann ich immer genau den Tag eingrenzen.

    Code
    select id, min(temp) from wetterdaten order by id desc limit 144;

    Allerdings habe ich auch um 0 Uhr nachts folgende Ausgabe:

    Code
    622|2.8

    Auch diese Abfragen liefern mir immer wieder die Zeile 622:

    Code
    select id, temp  from wetterdaten t where t.temp in (select min(temp) from wetterdaten) ;
    Code
    select id, temp from wetterdaten where temp=(select min(temp)from wetterdaten) ORDER BY id DESC LIMIT 144;

    In den letzten 144 Einträge ist allerdings kein Wert mit 2,8 vorhanden.

    Meine Einträge in der DB sind momentan bei ID 2109. Somit fällt auch die ID 622 welche immer wieder kommt, nicht in das Limit von 144.

    Ich stehe hier irgendwie am Schlauch.

  • Das mit dem Limit funktioniert nicht so wie gehofft.

    Du musst die Auswahl der Daten mit einem where Statement einschränken, auf welches sich dann die min Funktion von SQL beschränkt.

    Das where muss auf die Tabellenspalte zeit abzielen, und dort den Zeitraum eingrenzen.

    https://sqlite.org/lang_datefunc.html

    Dort hast du einiges an Beispielen

  • noisefloor ja es geht um Python.

    ORM werde ich mir mal ansehen, allerdings hast du mir schon mal SQLAlchemy vorgeschlagen. Ich habe aber momentan wirklich wenig Zeit mich mit dem zu befassen.

    Bin auch noch dabei Python zu lernen. Da hapert es auch schon etwas ;)

    Ich habe jetzt allerdings durch Hofei eine Lösung gefunden.

    Code
    select min(temp), max(temp) from wetterdaten where zeit between datetime('now','-1 day') and datetime('now')

    Mit dem kommen ich genau an das Ergebnis was ich mir vorgestellt habe.

  • Sehr schön, genau so hätte ich es auch vorgeschlagen.

    Noch zur Erklärung oben :

    select id, min(temp) from wetterdaten;

    Bringt dir genau einen Datensatz,den mit dem kleinsten Wert. Das Limit 1 ist da überflüssig.

    Das brauchst du nur wenn du mehrere, gleiche Min Werte hättest.

    select id, temp from wetterdaten where temp=(select min(temp)from wetterdaten) ORDER BY id DESC LIMIT 144;

    Sucht in einem Subset aus min(temp) die 144 Werte raus. Wenn da nur einer mit dem Minimum ist, also keine zwei mit einem Wert von 2,8, bekommst du nur einen Wert. Anders wäre es wenn du 155 Minimum Werte mit 2.8 hättest, dann würde er dir die letzten 144 sortiert nach ID ausgeben.

    Bei nur 99 min Werten mit 2.8 eben nur die 99.

    Offizieller Schmier und Schmutzfink des Forum.
    Warum einfach wenn's auch schwer geht ?

    Kein Support per PN !
    Fragen bitte hier im Forum stellen. So hat jeder etwas davon.

  • Ich habe jetzt allerdings durch Hofei eine Lösung gefunden.

    Code
    select min(temp), max(temp) from wetterdaten where zeit between datetime('now','-1 day') and datetime('now')

    Mit dem kommen ich genau an das Ergebnis was ich mir vorgestellt habe.

    Sehr schön, genau so hätte ich es auch vorgeschlagen.

    Wobei ich selbst mit der Lösung nicht zufrieden bin, aber es aktuell auch nicht besser hinbekomme.

    Lt. Ausgangsstellung wird das Auslesen immer um 0.00Uhr per Cronjob gestartet. Wenn die Ausführung ein paar Sekunden später startet und in der Zeit dazwischen ein neuer Min-Wert geschrieben wird, wird er auch von der Abfrage erfasst, obwohl er jedoch zum letzten Tag gar nicht mehr zählen dürfte.

    Deshalb wäre doch die Ideallösung so:

    Code nicht ausführbar, wie gesagt ich bekomms auch nicht hin

    Code
    select min(temp), max(temp) from wetterdaten where zeit between datetime('now','-1 day') and datetime('now -1 day 23:59:59')


    ORM werde ich mir mal ansehen, allerdings hast du mir schon mal SQLAlchemy vorgeschlagen. Ich habe aber momentan wirklich wenig Zeit mich mit dem zu befassen.

    Bin auch noch dabei Python zu lernen. Da hapert es auch schon etwas ;)

    Ja wurde mir auch immer nahe gelegt ein ORM zu verwenden, ich entschied mich für Peewee, konnte mich damit besser anfreunden als mit SQLAlchemy. Zumindest mit meinem damaligen Wissensstand.

    Das ganze würde übrigends dann so aussehen:

    Ehrlich gesagt bin ich gerade daran gescheitert, alle Min Werte in einer SQL Abfrage auszulesen wie so z.B:

    Python
    def letzter_tag_minimal_werte_auslesen(beginn, ende):
        abfrage = Wetter.select(peewee.fn.min(Wetter.temp),
                                peewee.fn.min(Wetter.feuchtigkeit),
                                peewee.fn.min(Wetter.wind)).where(
            Wetter.zeit.between(beginn, ende)
        )
        return abfrage

    aber da bekomme ich ein None zurück.

    Vielleicht kann das noisefloor noch zeigen wie das geht - daran hätte ich auch interesse.

  • Wobei ich selbst mit der Lösung nicht zufrieden bin, aber es aktuell auch nicht besser hinbekomme.

    Lt. Ausgangsstellung wird das Auslesen immer um 0.00Uhr per Cronjob gestartet. Wenn die Ausführung ein paar Sekunden später startet und in der Zeit dazwischen ein neuer Min-Wert geschrieben wird, wird er auch von der Abfrage erfasst, obwohl er jedoch zum letzten Tag gar nicht mehr zählen dürfte.

    Deshalb wäre doch die Ideallösung so:

    Code nicht ausführbar, wie gesagt ich bekomms auch nicht hin

    Code
    select min(temp), max(temp) from wetterdaten where zeit between datetime('now','-1 day') and datetime('now -1 day 23:59:59')

    Hier ein Beispiel von mir in PHP, ein wenig angepasst. Keine Ahnung ob das läuft aber der Sinn ist klar.

    Code
    $uts = now() - 86400; 
    $startdate = date("Y-m-d",$uts) . " 00:00:00";
    $enddate = date("Y-m-d",$uts) . " 23:59:59";
    $sQRYfc  = "SELECT min(temperatur) as tmin, max(temperatur) as tmax FROM raumtemp WHERE tsstr BETWEEN DATETIME(" . $startdate . ") AND DATETIME(" . $enddate . ")";

    Erst Gestern ermitteln ,now() - 86400 ergibt auch um 00:01h definitiv gestern.

    Dann von 00:00 - 23:59 basteln und rein in die Abfrage.

    Ich speicher das Datum anders, deswegen gebastel. 20191001160449 für 16:04:49 01.10.2019

    Offizieller Schmier und Schmutzfink des Forum.
    Warum einfach wenn's auch schwer geht ?

    Kein Support per PN !
    Fragen bitte hier im Forum stellen. So hat jeder etwas davon.

  • Nein, weil es ja nicht mein Thread ist.

    Ich mache sowas meist mit Grafana

    Mir ging es nur darum, dass bei der oben gezeigten Konstelation von wusa es passieren kann, dass die Abfrage auch noch Daten vom nächsten Tag erwischen könnte unter gewissen Umständen...

  • Sucht in einem Subset aus min(temp) die 144 Werte raus. Wenn da nur einer mit dem Minimum ist, also keine zwei mit einem Wert von 2,8, bekommst du nur einen Wert. Anders wäre es wenn du 155 Minimum Werte mit 2.8 hättest, dann würde er dir die letzten 144 sortiert nach ID ausgeben.

    Bei nur 99 min Werten mit 2.8 eben nur die 99.

    Vielen Dank für die Erklärung! Das war mir so nicht klar. Jetzt ist es allerdings verständlich!

    Lt. Ausgangsstellung wird das Auslesen immer um 0.00Uhr per Cronjob gestartet. Wenn die Ausführung ein paar Sekunden später startet und in der Zeit dazwischen ein neuer Min-Wert geschrieben wird, wird er auch von der Abfrage erfasst, obwohl er jedoch zum letzten Tag gar nicht mehr zählen dürfte.

    Ich könnte den Cronjob auch um 23:55 Uhr starten. Da ich immer alle 10 Min abfrage ist die letzte Abfrage am Tag um 23:50 Uhr und der Cronjob hat somit 5 Minuten Zeit. Das sollte somit dann sauberer sein. Dann hätte ich die Werte von 00:00 Uhr bis 23:50 Uhr.

    Oder täusche ich mich hier?

    Wenn ich den Cronjob um 23:55 Uhr starte, und -1 Tag sage dann sollte doch eigentlich die Zeit zwischen 23:55 Uhr und und 23:55 Uhr sein.

    Letzter Datensatz am Tag ist 23:50 Uhr. Dieser sollte doch dann nicht mehr in die Abfrage mit rein fallen.

    Ich stelle mir die Abfrage so vor, dass der erste genommene Datensatz somit 00:00 Uhr sein sollte bis 23:55 Uhr?


    RRDtool

    Ich habe mir irgendwie gegen RRDtool entschieden.

    Ich habe vor wenn ich mal Daten gesammelt habe, dass ich Matplotlib verwende. Das ist für mich auf den ersten Blick verständlicher.

    Ich brauche auch keine dynamischen Bilder, mir reichen statische.

  • Ich würde die Abfrage einfach „sicher“ gestalten und nicht das die Abfrage davon Abhängig ist, zu welche Minute sich das Skript startet.

    Wie das geht hab ich dir eh oben schon in Python gezeigt.

    Aber je nachdem welche Ansprüche du selbst an dein Skript stellst, kannst du es auch so machen wie von dir gedacht

  • Wäre es nicht besser Datum und Uhrzeit als Unixzeit in der Datenbank abzulegen? Klar ich muss dann immer die Zahl in ein Datum/Uhrzeit umrechnen. Da es sich dann um Zahlen handelt wird die Suche in den Datensätzen nicht schneller/einfacher? Klar ich muss dann Anfangszeit und Endezeit wieder in Unixzeit umrechnen.

    Den Punkt, dass ich die Uhrzeit nicht mehr direkt lesen kann, lass ich nicht gelten, da die Daten in einer Datenbank sowieso nicht direkt lesbar sind. Datum/Uhrzeit in Klartext macht eigentlich nur in Textdateien Sinn.

    Just my 2 Cent.

    Glaube ersetzt kein Wissen

  • Den Punkt, dass ich die Uhrzeit nicht mehr direkt lesen kann, lass ich nicht gelten, da die Daten in einer Datenbank sowieso nicht direkt lesbar sind. Datum/Uhrzeit in Klartext macht eigentlich nur in Textdateien Sinn.

    ??? - ein SQL Query liefert doch Datenbankeinträge im Klartext (=wie gespeichert).


    Abgesehen davon ist das einer der Gründe für ein ORM mit Python. Dann kann es egal sein, wie die DB speichert, weil das ORM ein datetime Objekt liefert.


    Gruß, noisefloor

  • Vor dem Problem stand ich auch.

    Ich wollte das Datum lesbar in der Datenbank und einfach abzufragen.

    Jetzt immer in Querys mit den date funktionen von SQL zu spielen war mir zu aufwändig, vor allem weil mySQL und MsSQL sich ein wenig in der Syntax unterscheiden.

    Ich speicher das Datum jetzt als bigint im Format YmdHHnnss.

    Damit bekommst du wie ein UnixTS eine aufsteigende Zahl welche du auch lesen kannst.

    Offizieller Schmier und Schmutzfink des Forum.
    Warum einfach wenn's auch schwer geht ?

    Kein Support per PN !
    Fragen bitte hier im Forum stellen. So hat jeder etwas davon.

Jetzt mitmachen!

Du hast noch kein Benutzerkonto auf unserer Seite? Registriere dich kostenlos und nimm an unserer Community teil!