[Python] P1 uitlezen en opslaan in Sqlite

[ironheart]

Hoi,

Mijn doel is om mijn P1 meter uit te lezen, de waarden op te slaan in een SQLite database en vervolgens mijn dagverbruik te calculeren om deze, vanaf een bepaalde waarde, middels een pushover melding naar mij toe te sturen.

Tot nu toe heb ik de waarden kunnen uitlezen en op kunnen slaan in de SQLite database.
Waar ik echter vastloop is om deze uit te lezen. Ik moet namelijk de minimale waarde hebben en de maximale waarde van vandaag. Hoe kan ik dit het beste aanvliegen?




Het veld datum is namelijk een TEXT veld en daar zitten ook minuten en seconden bij.
Ik moet dus de laagste waarde kwh en gas hebben waarbij de datum (Y-M-D) gelijk is aan vandaag.

Ik weet niet hoe ik dit het beste kan aanvliegen eerlijk gezegd.

[Janoz]

Het beste is om het type van het datumveld aan te passen naar een DATETIME. Vervolgens kun je allemaal ingebouwde functies gebruiken om te groeperen op dag.

[DexterDee]

Wat @Janoz zegt

Vervolgens een welgeplaatste query er tegenaan gooien, zoiets als:

code:

SELECT 
    MIN(kwh), MAX(kwh) 
FROM 
    table 
WHERE 
    date(datum) = date("2023-11-14")

Op die manier hoef je je niet druk te maken over de uren, minuten en seconden in de tijd.
Let wel op, als je database flink gevuld raakt dan kan dit een performance bottleneck vormen. Je zult dan een index op 'datum' moeten maken en je query iets aan moeten passen:

code:

SELECT 
    MIN(kwh), MAX(kwh) 
FROM
    table 
WHERE 
    datum >= "2023-11-14 00:00:00" AND datum < "2023-11-15 00:00:00"

Het veld datum zal in dat geval wel van het type DATETIME moeten zijn

[ironheart]

Ok maar kan dat datatype wel met een SQLite? Naar mijn (beperkte) kennis is datetime geen geldig Sqlite datatype of heb ik verkeerde info verkregen?

[Janoz]

Yup, je hebt verkeerde info.

https://www.sqlitetutorial.net/sqlite-date/

[ Voor 4% gewijzigd door Janoz op 14-11-2023 23:31 ]

[Merethil]

Janoz schreef op dinsdag 14 november 2023 @ 18:29:
Yup, je hebt verkeerde info.

https://www.sqlitetutorial.net/sqlite-date/

Dit is een quote van die website:

SQLite does not support built-in date and/or time storage class. Instead, it leverages some built-in date and time functions to use other storage classes such as TEXT, REAL, or INTEGER for storing the date and time values

Er is dus geen datetime, maar wel functies om er makkelijk mee te werken.

[Janoz]

Oeh, meaculpa. Ik heb er veel te snel overheen gelezen... of... eigenlijk gewoon helemaal neit gelezen

[ Voor 31% gewijzigd door Janoz op 14-11-2023 23:31 ]

[Mortis__Rigor]

Als je geen relaties met andere zaken nodig hebt, weet ik niet of een SQL database hier de geschikte oplossing voor is. Het is misschien makkelijker als je naar een database kijkt die specifiek ontworpen is om time series op te slaan (zoals bv. influxdb)

[Cartman!]

Je kunt ook de datum los opslaan en daar een index op zetten. Dan kun je de query ieder geval optimaliseren.

[Ben(V)]

Je kunt beter de datatime die je van p1 krijgt omzetten in een python time (aantal miliseconden sinds de epoch) en die in je database stoppen.
Dat is een float waar je beter mee kunt werken dat een asscii representatie van een datum/tijd
zoiets dus:

import time
EpochTime = time.mktime(time.strptime('23-11-14 17:32:17', '%y-%m-%d %H:%M:%S'))

Dan kun je selecteren op een Epochtime die ligt tussen vandaag 00:00 uur en 24:00 uur (ook weer even omzetten in epochtijd)

[ Voor 14% gewijzigd door Ben(V) op 15-11-2023 10:36 ]

[Merethil]

Ben(V) schreef op woensdag 15 november 2023 @ 10:33:
Je kunt beter de datatime die je van p1 krijgt omzetten in een python time (aantal miliseconden sinds de epoch) en die in je database stoppen.
Dat is een float waar je beter mee kunt werken dat een asscii representatie van een datum/tijd
zoiets dus:

[...]


Dan kun je selecteren op een Epochtime die ligt tussen vandaag 00:00 uur en 24:00 uur (ook weer even omzetten in epochtijd)

Alleen is dat toevallig net de optie die niet ondersteund wordt door de ingebouwde sqlite-functies voor date en time. Alleen epoch (seconds) in een integer, julianday in een real of gewoon een tekstuele weergave in ISO 8601 in een text. Dat is dus ook hier weergegeven: https://www.sqlitetutorial.net/sqlite-date/

Daarnaast vraag ik me af wat het praktisch nut is van het gebruiken van een getal (human unreadable) ipv een tekstuele waarde (human readable) als het gaat om een relatief kleine database en de ingebouwde functies met allebei om kunnen gaan. Natuurlijk zullen er minimale performanceverschillen zijn, maar ik denk dat de boel er leesbaarder en makkelijker bruikbaar op wordt door het te doen zoals TS het al doet en dus de ingebouwde functies gebruikt voor de query.

Cartman! schreef op woensdag 15 november 2023 @ 08:28:
Je kunt ook de datum los opslaan en daar een index op zetten. Dan kun je de query ieder geval optimaliseren.

Dit lijkt me inderdaad een prima aanpak, al is het natuurlijk een kwestie van meten of het heel veel winst oplevert of dat het premature optimization is

[ Voor 13% gewijzigd door Merethil op 15-11-2023 12:08 ]

[ironheart]

Guys, even een logische gedachtengang.

Ik schrijf nu om de 10 minuten de waardes weg naar de database.
Aan het eind van iedere dag (voor nu) en aan het eind van de week wil ik graag een resultaat ontvangen.

Is het dan eigenlijk wel zinvol om iedere 10 minuten ( of ieder uur ) de waardes weg te schrijven?

Is het niet zinvoller om iedere dag om 00:01 uur de actuele stand te noteren en vanuit daar de calculatie van het verbruik te maken zonder het op te slaan? Scheelt in opslag, toch? of zie ik iets over het hoofd?

[Anoniem: 80910]

Je kunt ook per week / maand een database opnieuw gebruiken. Dan is de performance prima. Als je dan een jaar overzicht wil maken moet je 12 databases inlezen. Wellicht kun je ook nog wel per jaar doen. Ff berekenen hoeveel entries dat zijn

[Bolukan]

3,15 miljoen entries per jaar bij elke 10 seconde. Zeg 20 bytes, is 60MB per jaar of een CD-tje vol per 10 jaar. Het valt allemaal wel mee. PS: Ik gebruik influxdb voor p1 data en nog veel meer uit de home automation en grafana om het in grafieken te ontsluiten.

[ Voor 30% gewijzigd door Bolukan op 15-11-2023 12:30 ]

[RobIII]

ironheart schreef op woensdag 15 november 2023 @ 12:11:
Ik schrijf nu om de 10 minuten de waardes weg naar de database.
Aan het eind van iedere dag (voor nu) en aan het eind van de week wil ik graag een resultaat ontvangen.
[...]
Is het niet zinvoller om iedere dag om 00:01 uur de actuele stand te noteren en vanuit daar de calculatie van het verbruik te maken zonder het op te slaan? Scheelt in opslag, toch? of zie ik iets over het hoofd?

Of je gebruikt, zoals eerder al geopperd, een timeseries DB als InfluxDB. Dan kun je, bij wijze van, elke 10 seconden een meting doen en opslaan en alles ouder dan, zeg, een week, platslaan naar een resolutie per minuut en alles ouder dan een jaar opslaan met een resolutie per uur en alles ouder dan 5 jaar opslaan met een resolutie per dag. Ik roep maar wat. Maar dat is waar InfluxDB en consorten goed in zijn. En dat scheelt enorm aan opslag en, eens ingericht, hoef je je dan niet eens om te bekommeren. Dat gebeurt gewoon volautomatisch.

[ Voor 13% gewijzigd door RobIII op 15-11-2023 13:14 ]

[Ben(V)]

Merethil schreef op woensdag 15 november 2023 @ 12:03:
[...]


Alleen is dat toevallig net de optie die niet ondersteund wordt door de ingebouwde sqlite-functies voor date en time. Alleen epoch (seconds) in een integer, julianday in een real of gewoon een tekstuele weergave in ISO 8601 in een text. Dat is dus ook hier weergegeven: https://www.sqlitetutorial.net/sqlite-date/

Daarnaast vraag ik me af wat het praktisch nut is van het gebruiken van een getal (human unreadable) ipv een tekstuele waarde (human readable) als het gaat om een relatief kleine database en de ingebouwde functies met allebei om kunnen gaan. Natuurlijk zullen er minimale performanceverschillen zijn, maar ik denk dat de boel er leesbaarder en makkelijker bruikbaar op wordt door het te doen zoals TS het al doet en dus de ingebouwde functies gebruikt voor de query.


[...]


Dit lijkt me inderdaad een prima aanpak, al is het natuurlijk een kwestie van meten of het heel veel winst oplevert of dat het premature optimization is

Wat is het nut van human readable format in een datatbase?
Dat is alleen nuttig in een gebruikers interface.

@ironheart

Een keer per dag opslaan is dus meer dan voldoende voor wat jij wil.
Ik zou het gewoon als een epochdag opslaan, dan kun je ook altijd met een simpele database query bijvoor een range (aantal dagen) opvragen.

In python maak je zo een epochdag

import time
EpochDay = int(time.time()/86400)

Of als je de p1 ascii datum wilt gebruiken.

import time
EpochDay = time.mktime(time.strptime(P1Waarde, '%y-%m-%d %H:%M:%S'))/86400

[ Voor 5% gewijzigd door Ben(V) op 15-11-2023 13:24 ]

[Merethil]

Ben(V) schreef op woensdag 15 november 2023 @ 13:20:
[...]


Wat is het nut van human readable format in een datatbase?
Dat is alleen nuttig in een gebruikers interface.

Jij hebt nog nooit direct naar je database moeten kijken om iets van een simpele analyse danwel een bugfix te doen? En hoe doe je het in een DB-engine die wel een datetime ondersteunt? Ga je het dan ook ombouwen naar een milliseconds-since-epoch?

Ik denk dat (naast dat ik het wel graag human readable houd vanwege voorgaande redenen, tenzij het echt een (performance)probleem oplevert) de aangeraden oplossingen voor dit specifieke probleem van de Sqlite-maker zelf zou volgen ipv zelf iets verzinnen. Al is het maar omdat er bijvoorbeeld slimmer op geïndexeerd kan worden doordat de engine zelf datums eruit parsed oid.
Het resulteert ook nog eens in minder code onderhouden in je client omdat je niet hoeft te vertalen beide kanten op.

[ Voor 6% gewijzigd door Merethil op 15-11-2023 14:44 ]

[ironheart]

Wat is het verschil in dit:

code:

tomorrow = datetime.date.today() + datetime.timedelta(days=1)
tomorrow = tomorrow.strftime("%Y-%m-%d 00:00:00")
print("Vandaag =", today)
print("Morgen =", tomorrow)

kwh_tarief = 0.39929
gas_tarief = 1.43627

conn = sqlite3.connect('p1_metrics.db')
c = conn.cursor()
c.execute('''
          SELECT
          MIN(kwh), MAX(kwh) FROM metrics WHERE datum >= "''today''" AND datum < "2023-11-16 00:00:00"
          ''')
records = c.fetchall()

for row in records:
            min_kwh = row[0]
            max_kwh = row[1]
            print("Laagste kwh: " + str(min_kwh))
            print("Hoogste kwh: " + str(max_kwh))
            kwh_verschil = round(max_kwh - min_kwh,3)
            print("Verschil kwh: " + str(kwh_verschil))
            kwh_kosten = round(kwh_verschil * kwh_tarief,2)
            print("Kosten kwh: " + str(kwh_kosten))

en

code:

tomorrow = datetime.date.today() + datetime.timedelta(days=1)
tomorrow = tomorrow.strftime("%Y-%m-%d 00:00:00")
print("Vandaag =", today)
print("Morgen =", tomorrow)

kwh_tarief = 0.39929
gas_tarief = 1.43627

conn = sqlite3.connect('p1_metrics.db')
c = conn.cursor()
c.execute('''
          SELECT
          MIN(kwh), MAX(kwh) FROM metrics WHERE datum >= "''today''" AND datum < "''tomorrow''"
          ''')
records = c.fetchall()

for row in records:
            min_kwh = row[0]
            max_kwh = row[1]
            print("Laagste kwh: " + str(min_kwh))
            print("Hoogste kwh: " + str(max_kwh))
            kwh_verschil = round(max_kwh - min_kwh,3)
            print("Verschil kwh: " + str(kwh_verschil))
            kwh_kosten = round(kwh_verschil * kwh_tarief,2)
            print("Kosten kwh: " + str(kwh_kosten))

Het eerste werkt namelijk perfect. De tweede geeft namelijk aan:

code:

kwh_verschil = round(max_kwh - min_kwh,3)
                         ~~~~~~~~^~~~~~~~~
TypeError: unsupported operand type(s) for -: 'NoneType' and 'NoneType'

[ Voor 27% gewijzigd door ironheart op 16-11-2023 13:36 ]

[remyz]

Print in beide stukjes code in de for-loop eens de waarde van row[0] en row[1]. Daarmee zou je een aanwijzing moeten kunnen vinden.

[ironheart]

Dat had ik al gedaan, ik krijg value none terug maar ik weet eigenlijk niet waarom?

[remyz]

Dat je tweede stukje code andere waarden geeft heeft vast met de veranderde query te maken. Je moet een stapje hoger kijken: wat is het verschil in de inhoud van ‘row’ en als dat geen aanwijzing geeft, wat is het verschil in ‘record’.

[Merethil]

Trek anders even de querytext naar een losse variabele en print hem dan. Waarschijnlijk zie je dan dat er een verschil in syntax zit waardoor in de tweede versie sqlite geen waarden teruggeeft.

[ironheart]

Merethil schreef op donderdag 16 november 2023 @ 16:38:
Trek anders even de querytext naar een losse variabele en print hem dan. Waarschijnlijk zie je dan dat er een verschil in syntax zit waardoor in de tweede versie sqlite geen waarden teruggeeft.

Ik begrijp niet zo goed wat je hiermee bedoelt.

Ik ben nu de gehele code aan het doorlopen en debuggen maar ik heb nog niets gevonden so far.

Dit is de code:

code:

today = datetime.date.today()
today = today.strftime("%Y-%m-%d 00:00:00")

tomorrow = datetime.date.today() + datetime.timedelta(days=1)
tomorrow = tomorrow.strftime("%Y-%m-%d 00:00:00")
print("Vandaag =", today)
print("Morgen =", tomorrow)


kwh_tarief = 0.39929
gas_tarief = 1.43627

conn = sqlite3.connect('p1_metrics.db')
c = conn.cursor()

tomorrow = str(tomorrow)
#c.execute('''
#          SELECT
#          MIN(kwh), MAX(kwh) FROM metrics WHERE datum >= "2023-11-15 00:00:00" AND datum < "2023-11-16 00:00:00"
#          ''')

c.execute('''
          SELECT
          MIN(kwh), MAX(kwh) FROM metrics WHERE datum >= "''today''" AND datum < "''tomorrow''"
          ''')

records = c.fetchall()

for row in records:
            min_kwh = row[0]
            max_kwh = row[1]
            print("Laagste kwh: " + str(min_kwh))
            print("Hoogste kwh: " + str(max_kwh))
            kwh_verschil = round(max_kwh - min_kwh,3)
            print("Verschil kwh: " + str(kwh_verschil))
            kwh_kosten = round(kwh_verschil * kwh_tarief,2)
            print("Kosten kwh: " + str(kwh_kosten))
c.close()




print("* * * * *")

Edit:
Never mind, found it!

code:

MIN(kwh) FROM metrics WHERE datum >= "''' + today+''''" AND datum < "''' + tomorrow+''''"

[ Voor 3% gewijzigd door ironheart op 16-11-2023 17:24 ]