So far I have following working code (working with example data)
WITH miniPow as (
select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini
from power
group by date(timestamp)
)
, maxiPow as (
select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi
from power
group by date(timestamp)
)
select maxiPow.d, ROUND(maxi-mini, 1) from miniPow
join
maxiPow
on miniPow.d = maxiPow.d
Below my first SQL attempt (not really elegant)
WITH consumption AS
(SELECT date(TIMESTAMP) AS d,
min(TOTAL_KWH) mi,
max(TOTAL_KWH) ma
FROM power
GROUP BY date(TIMESTAMP)),
enumerated AS
(SELECT *,
(SELECT count(*)
FROM consumption b
WHERE a.d >= b.d) AS cnt
FROM consumption a),
preenumerated AS
(SELECT *,
(SELECT count(*)-1
FROM consumption b
WHERE a.d >= b.d) AS cnt
FROM consumption a)
SELECT strftime('%d.%m.%Y', en.d) AS "obdobà od",
strftime('%d.%m.%Y', pre.d) AS "obdobà do",
printf("%.1f", en.ma) AS "poÄáteÄnà kWh",
printf("%.1f", pre.ma) AS "koncový kWh",
printf("%.1f", pre.ma-en.ma) AS "kWh za obdobÃ",
(JulianDay(pre.d) - JulianDay(en.d)) AS "poÄet dnů",
printf("%.1f", (pre.ma-en.ma)/(JulianDay(pre.d) - JulianDay(en.d))) AS "kWh za den"
FROM enumerated en
JOIN preenumerated pre ON en.cnt = pre.cnt
UNION ALL
SELECT strftime('%d.%m.%Y', min(date(TIMESTAMP))) AS "obdobà od",
strftime('%d.%m.%Y', max(date(TIMESTAMP))) AS "obdobà do",
"" AS "poÄáteÄnà kWh",
"CELKEM:" AS "koncový kWh",
printf("%.1f kWh", max(TOTAL_KWH)) AS "kWh za obdobÃ",
printf("za %.0f dnů", (JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "poÄet dnů",
printf("ø %.1f kWh/den", (max(TOTAL_KWH)-min(TOTAL_KWH))/(JulianDay(max(date(TIMESTAMP))) - JulianDay(min(date(TIMESTAMP))))) AS "kWh za den"
FROM power
Link to example table here