DAIKIN Onecta
Tabuľky:
Consumption data
-- Fakt: 2h sloty (UTC)
CREATE TABLE IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_2h (
device_id TEXT NOT NULL, -- FK na tvoju device tabuľku (už ju máš)
source_code TEXT NOT NULL, -- 'electrical' | 'gas'
mode_code TEXT NOT NULL, -- 'heating' | 'cooling'
period_start_utc TEXT NOT NULL, -- ISO8601, UTC 'YYYY-MM-DDTHH:MM:SSZ'
value REAL, -- NULL = zatiaľ neznáme
unit TEXT NOT NULL, -- 'kWh' | 'm³' (alebo z payloadu)
PRIMARY KEY (device_id, source_code, mode_code, period_start_utc)
);
CREATE INDEX IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_2h_idx_start
ON pe1_ws_daikinonecta_consumptiondata_2h(period_start_utc);
CREATE INDEX IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_2h_idx_dev_start
ON pe1_ws_daikinonecta_consumptiondata_2h(device_id, period_start_utc);
-- Raw snapshot (audit/debug)
CREATE TABLE IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_raw (
raw_id INTEGER PRIMARY KEY AUTOINCREMENT,
device_id TEXT NOT NULL,
polled_at_utc TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
payload_json TEXT NOT NULL,
timestamp TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_raw_idx_dev
ON pe1_ws_daikinonecta_consumptiondata_raw(device_id, polled_at_utc);reporty bez vstupných parametrov, ktoré vrátia všetky uložené dáta za všetky zariadenia, všetky dni a rozdelené podľa režimov (a zdrojov). Nižšie sú hotové SELECTy. Všetky agregujú v lokálnom čase Europe/Bratislava (CET/CEST), pričom výpočet lokálneho času robia inline z period_start_utc (UTC) — teda bez parametrov.
Pozn.: Výraz na konverziu UTC → lokálny čas zohľadňuje letný/zimný čas:
- DST (CEST, UTC+2) trvá od poslednej nedele v marci 01:00 UTC do poslednej nedele v októbri 01:00 UTC.
- Mimo tohto intervalu je CET (UTC+1).
Tento výraz uvidíš vložený v každom SELECT-e.
0) Pomocný výraz (len na vysvetlenie, netreba vytvárať)
V dotazoch konvertujem period_start_utc na lokálny čas takto (inline v každom SELECT-e):
SQL
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
Zobraziť viac riadkov
- Z tohto potom odvodzujem
date(...)(deň),strftime('%Y-%m', ...)(mesiac),strftime('%W', ...)(týždeň).
1) Denný sumár (za všetky dni a zariadenia, podľa režimu a zdroja)
Skupiny: deň (lokálny), device_id, source_code, mode_code.
SQL
SELECT
date(
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS day_local,
device_id,
source_code,
mode_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY day_local, device_id, source_code, mode_code
ORDER BY day_local DESC, device_id, source_code, mode_code;
Zobraziť viac riadkov
2) Mesačný sumár (YYYY‑MM, lokálny čas)
Skupiny: mesiac (lokálny), device_id, source_code, mode_code.
SQL
SELECT
strftime(
‘%Y-%m’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS ym_local,
device_id,
source_code,
mode_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY ym_local, device_id, source_code, mode_code
ORDER BY ym_local DESC, device_id, source_code, mode_code;
Zobraziť viac riadkov
3) Týždenný sumár (rok + týždeň %W, lokálny čas)
Skupiny: rok (lokálny), týždeň %W (lokálny), device_id, source_code, mode_code.
%Wnie je ISO týždeň, ale na prehľady stačí. Ak chceš ISO, doplním alternatívu.
SQL
SELECT
strftime(
‘%Y’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS yr_local,
strftime(
‘%W’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS wk_local,
device_id,
source_code,
mode_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY yr_local, wk_local, device_id, source_code, mode_code
ORDER BY yr_local DESC, wk_local DESC, device_id, source_code, mode_code;
Zobraziť viac riadkov
4) 2‑hodinová časová rada (lokálny čas slotu) — všetko, bez filtrov
Užitočné na línie grafy alebo profil spotreby v čase.
SQL
SELECT
period_start_utc,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
) AS period_start_local,
device_id,
source_code,
mode_code,
value,
unit
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
ORDER BY period_start_utc ASC, device_id, source_code, mode_code;
Zobraziť viac riadkov
5) Rozdelenie podľa režimu (heating vs. cooling) cez celé uložené obdobie
Skupiny: mode_code, device_id, source_code.
SQL
SELECT
mode_code,
device_id,
source_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY mode_code, device_id, source_code
ORDER BY device_id, source_code, total DESC;
Zobraziť viac riadkov
6) Rozdelenie podľa zdroja (electrical vs. gas) cez celé uložené obdobie
Skupiny: source_code, device_id, mode_code.
SQL
SELECT
source_code,
device_id,
mode_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY source_code, device_id, mode_code
ORDER BY device_id, mode_code, total DESC;
Zobraziť viac riadkov
7) Top dni s najvyššou spotrebou (lokálne dni) naprieč všetkým
Ak chceš globálny rebríček „najenergetickejších“ dní (naprieč zariadeniami).
SQL
SELECT
date(
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS day_local,
device_id,
source_code,
mode_code,
SUM(value) AS total
FROM pe1_ws_daikinonecta_consumptiondata_2h
WHERE value IS NOT NULL
GROUP BY day_local, device_id, source_code, mode_code
ORDER BY total DESC, day_local DESC, device_id
LIMIT 100;
Zobraziť viac riadkov
Tip na zjednodušenie (voliteľné)
Ak chceš, môžeme vytvoriť VIEW s predpočítaným lokálnym časom, napr.:
SQL
CREATE VIEW IF NOT EXISTS pe1_ws_daikinonecta_consumptiondata_2h_local_v AS
SELECT
period_start_utc,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
) AS period_start_local,
date(
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS day_local,
strftime(
‘%Y-%m’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS ym_local,
strftime(
‘%Y’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS yr_local,
strftime(
‘%W’,
datetime(
period_start_utc,
printf(
‘%+d hours’,
-(
CASE
WHEN datetime(period_start_utc) >= datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-03-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-03-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
AND datetime(period_start_utc) < datetime(
date(strftime(‘%Y’, period_start_utc) || ‘-10-31’,
printf(‘-%d days’, CAST(strftime(‘%w’, date(strftime(‘%Y’, period_start_utc) || ‘-10-31’)) AS INTEGER))
) || ‘ 01:00:00’
)
THEN -2 ELSE -1
END
)
)
)
) AS wk_local,
device_id,
source_code,
mode_code,
value,
unit
FROM pe1_ws_daikinonecta_consumptiondata_2h;
Zobraziť viac riadkov
Potom by tvoje reporty boli jednoduchšie (SELECT ... FROM pe1_ws_daikinonecta_consumptiondata_2h_local_v ...), ale chápem, že si chcel čisté SELECTy bez ďalších objektov.