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

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 number of days where Power >= 500 happens more than 25 times per day.

NB: The following is untested. I'm just writing SQL off the top of my head. The examples below might not work and might contain syntax errors.

To see the number of times that Power>=500 for each day:

SELECT date(Timestamp,'unixepoch') AS date, count(*) AS cnt
  FROM DayData
 WHERE Power>=500
 GROUP BY date
 ORDER BY date;

Using the above as a subquery to compute the number of days on which cnt exceeds 25:

SELECT count(*) FROM (
    SELECT date(Timestamp,'unixepoch') AS date, count(*) AS cnt
      FROM DayData
     WHERE Power>=500
     GROUP BY date
) WHERE cnt>50;

I started with this :

select count(*), * from DayData where TimeStamp > 1672531200 + 86400 * 1 and TimeStamp < 1672617600 + 86400 * 0 and Power >= 500;

select count( * ), * from DayData where TimeStamp > 1672531200 + 86400*1 and TimeStamp < 1672617600 + 86400 * 2 and Power >= 500;

to select for 1st and second January 2023

On 1 July 2023

Your example shows the data from today I suppose.

How can it be changed to itinerate through the database and display a column of dates and count(*) outcomes? (or export it as CSV)

You might ask why I want these figures. Well I intend to mount a hotfill close-in boiler (400W)and fill it with cold water and heat it for immediate warm water from the tap. I want to know the chance for each day of the year that PV power might be able to heat the water inside the boiler to a reasonable temperature. PV output value is measured every 5 minutes during production time (daylight) To heat the water I need 1 hour of input plus heating the used water from the tap, assuming about 25 values of > 500 Watt.

I think you should be able to use GROUP and HAVING here:

SELECT timestamp, COUNT(*) AS count
FROM daydata
WHERE power>500
GROUP BY timestamp
HAVING count > 25;

You can provide your timestamp criteria as an 'AND' clause in the 'WHERE' section.

Test case below (it generates a bunch of random rows for testing).

-- DROP TABLE IF EXISTS daydata;
CREATE TABLE daydata(
	daydate_id INTEGER PRIMARY KEY NOT NULL, 
	timestamp TEXT NOT NULL, -- Note: using date strings, rather that epoch - adjust as necessary.
	power INTEGER NOT NULL
);

INSERT INTO daydata(timestamp, power)
WITH RECURSIVE 
	_vars(start_date, end_date, min_power, max_power, max_i) AS (
		SELECT
			DATE('now', '-1 year', 'start of year'),
			DATE('now', '-1 year', 'start of year', '+1 year', '-1 days'),
			0,1000,
			10000),
	_rnd_date(d) AS (
		SELECT DATE(start_date, printf('+%d days', 
					abs(random() %( 1 + (julianday(end_date) - julianday(start_date))))))
		FROM _vars),
	_rnd_power(p) AS (
		SELECT min_power + abs(random() % (1+(max_power-min_power)))
		FROM _vars),
	_loop(i) AS (
			SELECT max_i FROM _vars 
				UNION ALL
			SELECT i-1 FROM _loop 
			LIMIT (SELECT MAX(1,IFNULL(max_i,1)) FROM _vars)	
	)
SELECT d,p FROM _loop
JOIN _rnd_date
JOIN _rnd_power;

If you just want the count, rather than the summary, wrap it up as follows:

WITH
	_summary(timestamp, count) AS (
		SELECT timestamp, COUNT(*) AS count
		FROM daydata
		WHERE power>500
		GROUP BY timestamp
		HAVING count > 25
	)
SELECT COUNT(*) AS no_of_days from _summary;

Note: the test-case I provided doesn't guarantee that you will get data that will satisfy the criteria (but I tested with lower counts, seems to work).

In this case I need to have the sum of a timestamp + the next ones up till timestamp + 86400. After that skip to the next timestamp + every timestamp + 86400 et cetera.

I only have timestamp, however I want to count the total score of every day of the year.

To clarify - do you need to 'pad' days where are no readings at all?

That is: do you need a comprehensive list of days within a range?
Then consider a LEFT JOIN against a CTE that generates day entries for the range given.

Something like:

WITH RECURSIVE
	_vars(start_date, end_date) AS (
		SELECT
			DATE('now', '-1 year', 'start of year'),
			DATE('now', '-1 year', 'start of year', '+1 year', '-1 days')
		),
	_generator(start_date, end_date, iter_date) AS (
		SELECT start_date, end_date, start_date FROM _vars	
			UNION ALL
		SELECT start_date, end_date, DATE(iter_date, '+1 days')
		FROM _generator
		WHERE DATE(iter_date) < DATE(end_date)
		LIMIT 1000 --- safety valve
	),
	_summary(timestamp, count) AS (
		SELECT timestamp, COUNT(*) AS count
		FROM daydata
		WHERE power>500
		GROUP BY timestamp
		HAVING count > 25
	)
SELECT iter_date AS timestamp, IFNULL(count,0) AS "READNG COUNT WHERE POWER>500 FOR THIS DATE" FROM _generator
LEFT JOIN _summary ON timestamp=iter_date;

Example result:

[...]

I have come this far:

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-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

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

awk '{if($1>50) print $1,$2;}' results_counted.csv > dates-with-sufficient-energy.csv

 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

The solution has been handed to me on the domoticz forum. https://www.domoticz.com/forum/viewtopic.php?p=318864#p318864

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;

It renders the # of days where there is sufficient energy to heat the close-in boiler with sun power.