include daily maximum (probably including time) in statistic

[stylpen]

trigger on insert in tick table is probably faster since SELECT * FROM "charts_solarentrytick" where device_id = 2 and time >= Datetime("2013-03-15 00:00:00") and time <= Datetime("2013-03-16 00:00:00") order by lW desc limit 1; takes about 5 seconds in my case

[martinmeinke]

something like this: echo "SELECT * FROM "charts_solarentrytick" where device_id = 2 and CAST(strftime(\"%d\", time) AS INT) = 16 order by lW desc limit 1;" | sqlite3 sensor.db might be faster.

[stylpen]

you're right SELECT * FROM "charts_solarentrytick" where device_id = 1 and CAST(strftime("%d", time) AS INT) = 16 and CAST(strftime("%m", time) AS INT) = 3 and CAST(strftime("%Y", time) AS INT) = 2013 order by lW desc limit 1; is 0.5s faster! SELECT max( lW) FROM "charts_solarentrytick" where device_id = 2 and CAST(strftime("%d", time) AS INT) = 16 and CAST(strftime("%m", time) AS INT) = 3 and CAST(strftime("%Y", time) AS INT) = 2013; is even 0.2s better that that! Luckily we don't have to compute that all the time.

The other thing is that we want to get the maximum (and the time when that was) of the interval the statistics page is comprising - not just one day if the month timeframe is selected. That is going to be more tricky when the ticks are stored in 2 tables but i'm sure it's gonna work somehow. (extra table or not)

[stylpen]

we want to get the maximum (and the time when that was) of the interval the statistics page is comprising - not just one day if the month timeframe is selected.

SELECT device_id, time, max(lW) FROM "charts_solarentrytick" WHERE time >= Datetime("2013-03-10 00:00:00") and time <= Datetime("2013-03-17 00:00:00") group by device_id; does what I want (here maximum of 6 days) ... but in more than 18s