sqlite question for PV power management

Moderator: leecollings

Post Reply
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

sqlite question for PV power management

Post by HvdW »

To get data from sqlite from last year I'm using this one:

Code: Select all

select count(*), * from DayData where TimeStamp > 1672532454 and TimeStamp < 1704064854 and Power >= 500;
Can you help me change the select criteria to find out the numer of days where Power >= 500 happens more than 25 times per day.
Last edited by HvdW on Friday 26 July 2024 0:07, edited 1 time in total.
Bugs bug me.
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: sqlite question

Post by willemd »

What does your table DayData contain? It is not a known domoticz table.

Does it contain one record per day? Of smaller intervals?
What does the Power field indicate? The maximum power in that interval?
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

Re: sqlite question

Post by HvdW »

It is the SBFspot db in ~/smadata
Bugs bug me.
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

Re: sqlite question

Post by HvdW »

I have come this far:

Code: Select all

sqlite3 -separator '  ' SBFspot.db "select Power,DATE(TimeStamp, 'unixepoch') from DayData where Power >= 500 and DATE(TimeStamp, 'unixepoch')< '2024-01-01' and DATE(TimeStamp, 'unixepoch')>= '2023-01-01';" > results.csv
2023-01-01 552
2023-01-01 504
2023-01-01 516
2023-01-01 540
2023-01-01 552
2023-01-01 540
2023-01-01 600
2023-01-01 660
2023-01-01 1092
2023-01-01 756
2023-01-01 696
2023-01-01 636
2023-01-01 552
2023-01-01 528
2023-01-02 528
2023-01-02 516
2023-01-02 564
2023-01-02 516
2023-01-02 540
2023-01-02 516
2023-01-02 528
2023-01-02 528
2023-01-02 588
2023-01-02 528
2023-01-02 552
2023-01-02 528
2023-01-02 504
2023-01-02 516
2023-01-02 552
2023-01-02 540
2023-01-02 552
2023-01-02 540
2023-01-03 612
2023-01-03 516
2023-01-04 528
2023-01-04 564
2023-01-06 552

Code: Select all

awk '{print $1}' results.csv | sort | uniq -c > results_counted.csv
14 2023-01-01
18 2023-01-02
2 2023-01-03
2 2023-01-04
1 2023-01-06

Code: Select all

awk '{if($1>50) print $1,$2;}' results_counted   > dates-with-sufficient-energy
56 2023-01-19
51 2023-01-28
68 2023-02-04
65 2023-02-06
55 2023-02-07
56 2023-02-08
74 2023-02-09
79 2023-02-10
54 2023-02-11
70 2023-02-13
67 2023-02-14
75 2023-02-15
64 2023-02-19
71 2023-02-22

Code: Select all

wc -l dates-with-sufficient-energy
262

Why I wanted to have this sorted out.
In the Netherlands recently a teruglevertarief has been introduced.
Using a Shelly plug I want to heat the close-in boiler at moments on days where there is enough sun to deliver the energy to heat the close-in boiler.
I was curious on how many days in a year (262) the boiler can be heatened with solar energy and on which days it has to be heatened with cheaper nightly power or even bypassed to have the regular gas boiler produce warm tap water.
Bugs bug me.
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: sqlite question for PV power management

Post by willemd »

If have tested the following on the meter table in domoticz

Code: Select all

select count(*) from (select substr(date,1,10) day,count(*) c from meter where devicerowid=3 and usage>5000 group by day) where c>100;
so what this does:
1) you take the date field, which includes time, and you select only the first 10 character to get only the date itself. The result is renamed (given an alias) to "day".
2) you take a count of the number of records returned and rename this to "c".
3) the selectiion is limited to devicerowid=3 which in my case is the solar panel production
4) and only the records with usage >5000 are selected,
5) the overall result of this selection is grouped by day, so for each day you get a count of the number of records with usage >5000

then in the second step
1) you select only the days where more than 100 records where returned (so for the meter table this means more than 100 5-minute intervals with usage>5000)
2) and you count that number of days

You can apply a similar selection to your daydata table
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

Re: sqlite question for PV power management

Post by HvdW »

I like the code you present. I can learn a lot of that.

However the Meter table holds the data of 1 day.
What I'd like to know is the number of days in a year where there are > 50 instances of a Power production of > 500 Watt
The Meter_Calendar table doesn't contain every 5 minute output from the solar panels.
That's why I tried with ~/smadata/SBFspot.db
I don't have time now but will try your code as an example on SBFspot.db

(meanwhile I have rediscovered the power of awk)
Bugs bug me.
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: sqlite question for PV power management

Post by willemd »

HvdW wrote: Friday 26 July 2024 23:41 I like the code you present. I can learn a lot of that.

However the Meter table holds the data of 1 day.
What I'd like to know is the number of days in a year where there are > 50 instances of a Power production of > 500 Watt
The Meter_Calendar table doesn't contain every 5 minute output from the solar panels.
That's why I tried with ~/smadata/SBFspot.db
I don't have time now but will try your code as an example on SBFspot.db

(meanwhile I have rediscovered the power of awk)
In my domoticz dbase the meter table contains data of 7 days (is configurable in the settings).
Meter table contains data of every 5 minutes, meter_calendar contains daily data.

It was meant as inspiration, to show you can easily achieve what you want with a nested sql. You can translate it to your situation.
Awk is powerful but usually so unreadible that after some time you don't understand your own code anymore....at least that is my experience.

Using your initial sql, the full sql for your result should be:

Code: Select all

select count(*) from (select DATE(TimeStamp, 'unixepoch') day, count(*) c from DayData where Power >= 500 and DATE(TimeStamp, 'unixepoch')< '2024-01-01' and DATE(TimeStamp, 'unixepoch')>= '2023-01-01' group by day) where c>50;
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

Re: sqlite question for PV power management

Post by HvdW »

willemd wrote: Saturday 27 July 2024 18:08 Using your initial sql, the full sql for your result should be:

Code: Select all

select count(*) from (select DATE(TimeStamp, 'unixepoch') day, count(*) c from DayData where Power >= 500 and DATE(TimeStamp, 'unixepoch')< '2024-01-01' and DATE(TimeStamp, 'unixepoch')>= '2023-01-01' group by day) where c>50;
Which is correct.
Nested...... good thing
Bugs bug me.
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest