Code: Select all
select count(*), * from DayData where TimeStamp > 1672532454 and TimeStamp < 1704064854 and Power >= 500;
Moderator: leecollings
Code: Select all
select count(*), * from DayData where TimeStamp > 1672532454 and TimeStamp < 1704064854 and Power >= 500;
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
Code: Select all
awk '{print $1}' results.csv | sort | uniq -c > results_counted.csv
Code: Select all
awk '{if($1>50) print $1,$2;}' results_counted > dates-with-sufficient-energy
Code: Select all
wc -l dates-with-sufficient-energy
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;
In my domoticz dbase the meter table contains data of 7 days (is configurable in the settings).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)
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.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;
Users browsing this forum: No registered users and 1 guest