Zitat von: Prof. Dr. Peter Henning am 19 Oktober 2022, 13:48:03Es gibt gefühlt ca. 100 verschiedene Funkschalter, welche die Leistung der angeschlossenen Verbraucher messen und bestens in FHEM eingebunden sind. Von Homematic-Geräten bis hin zu Shelly Devices. Es erschließt ich mir deshalb nicht, warum man dieses eigentlich für den Standalone-Betrieb vorgesehene SEM6000 in FHEM einbinden sollte.Die meisten Geräte dieser Art messen sehr ungenau und mit einer geringen Auflösung; damit sind sie ungeeignet zur Messung des Standby-Verbrauchs.
{"endpointId":"69a1xxxx-xxxx-xxxx-xxxx-c16c22xxxxxxxxxx","manufacturerName":"dummy","description":"n: Katzenklappe","friendlyName":"katzenklappe","displayCategories":["OTHER"],"additionalAttributes":{"manufacturer":"FHEM","model":"<unknown>"},"capabilities":[{"type":"AlexaInterface","interface":"Alexa","version":"3"},{"type":"AlexaInterface","interface":"Alexa.ModeController","instance":"fhem.Katzenklappe.state","version":"3","properties":{"supported":[{"name":"mode"}],"proactivelyReported":false,"retrievable":true,"nonControllable":false},"capabilityResources":{"friendlyNames":[{"@type":"text","value":{"text":"state","locale":"de-DE"}}]},"configuration":{"ordered":false,"supportedModes":[{"value":"fhem.Katzenklappe.state.auf","modeResources":{"friendlyNames":[{"@type":"text","value":{"text":"auf","locale":"de-DE"}}]}},{"value":"fhem.Katzenklappe.state.","modeResources":{"friendlyNames":[{"@type":"text","value":{"text":"","locale":"de-DE"}}]}},{"value":"fhem.Katzenklappe.state.zu","modeResources":{"friendlyNames":[{"@type":"text","value":{"text":"zu","locale":"de-DE"}}]}}]},"semantics":{}}],"cookie":{"device":"Katzenklappe","fuuid":"69a1xxxx-xxxx-xxxx-xxxx-c16c22xxxxxxxxxxx"}}Internals:
FUUID 69a1xxxx-xxxx-xxxx-xxxx-c16c22xxxxxxxxxxx
NAME Katzenklappe
NR 723
STATE auf
TYPE dummy
eventCount 11
READINGS:
2026-02-27 17:52:07 mode
2026-02-27 18:05:07 state auf
Attributes:
alexaName katzenklappe
genericDeviceType mode
homebridgeMapping ModeController:mode,cmd=mode,mode=state,values=auf;;zu
readingList mode
setList mode:auf,zu
webCmd modeInternals:
CFGFN
DEF FBDECT_Fritzbox_Actors_15282_0919488_1
DEV_REGEXP FBDECT_Fritzbox_Actors_15282_0919488_1
FUUID 69a1cf31-f33f-7706-a527-c7e15bc12c3802f6
NAME Statistik_SmartMeter
NOTIFYDEV global,FBDECT_Fritzbox_Actors_15282_0919488_1
NR 52474
NTFY_ORDER 10-Statistik_SmartMeter
PREFIX stat
STATE Updated stats for: FBDECT_Fritzbox_Actors_15282_0919488_1
TYPE statistics
eventCount 20
READINGS:
2026-02-27 18:07:08 monitoredDevicesFBDECT FBDECT_Fritzbox_Actors_15282_0919488_1
2026-02-27 18:07:08 nextPeriodChangeCalc 2026-02-27 18:59:55
2026-02-27 18:15:56 state Updated stats for: FBDECT_Fritzbox_Actors_15282_0919488_1
fhem:
modulVersion $Date: 2024-05-18 09:37:34 +0200 (Sat, 18 May 2024) $
nextPeriodChangeTime 1772215195
Attributes:
alias Statistic Device SmartMeter
deltaReadings FBDECT_Fritzbox_Actors_15282_0919488_1:energy_kWh
room TeststatEnergy
Hour: 395 Day: 395 Month: 395 Year: 395 (since: 2026-02-27_18:07:08 )
2026-02-27 18:17:27
statPowerDay
Min: 991.00 Avg: 2956.31 Max: 3004.00 (since: 2026-02-27_18:07:08 )
2026-02-27 18:13:56
statPowerMonth
Min: 991.00 Avg: 2956.31 Max: 3004.00 (since: 2026-02-27_18:07:08 )
2026-02-27 18:13:56
statPowerYear
Min: 991.00 Avg: 2956.31 Max: 3004.00 (since: 2026-02-27_18:07:08 )
2026-02-27 18:13:56
statVoltageDay
Min: 0.000 Avg: 0.000 Max: 0.000 (since: 2026-02-27_18:07:08 )
2026-02-27 18:13:56
statVoltageMonth
Min: 0.000 Avg: 0.000 Max: 0.000 (since: 2026-02-27_18:07:08 )
2026-02-27 18:13:56
statVoltageYear
Min: 0.000 Avg: 0.000 Max: 0.000 (since: 2026-02-27_18:07:08 )Sieht soweit erstmal gut aus. Das einzige was mir nicht klar ist wie ich das Zeitintervall für def deltareadings setzen kann. Die Werte sollten ja vom 1. des Monats bis zum letzten 1. des Folgemonats gehen.
und stoße natürlich an die Grenzen vom DbRep beim Parsen 
2026.02.27 16:06:07.804 3: Stromboerse_connect ur_24 : Neuberechnung der Kosten in der Datenbank
INSERT INTO history (TIMESTAMP, DEVICE, TYPE, READING, VALUE)
WITH price AS (
SELECT
TIMESTAMP AS interval_start,
VALUE AS price_ct_per_kWh -- z.B. Cent/kWh
FROM history
WHERE DEVICE = 'Stromboerse_connect'
AND READING = 'fc0_total'
AND TIMESTAMP >= CURDATE()
AND TIMESTAMP < CURDATE() + INTERVAL 1 DAY
),
consumption AS (
SELECT
interval_start,
CASE
WHEN interval_start = CURDATE() THEN 0
WHEN VALUE < LAG(VALUE) OVER (ORDER BY interval_start) THEN NULL
ELSE VALUE - LAG(VALUE) OVER (ORDER BY interval_start)
END AS kWh_15min
FROM (
SELECT
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(TIMESTAMP) / 900) * 900) AS interval_start,
VALUE,
ROW_NUMBER() OVER (
PARTITION BY FLOOR(UNIX_TIMESTAMP(TIMESTAMP) / 900)
ORDER BY TIMESTAMP DESC
) AS rn
FROM history
WHERE DEVICE = 'WR_0_KSEM'
AND READING = 'Active_energy+'
AND TIMESTAMP >= CURDATE() - INTERVAL 15 MINUTE
) t
WHERE rn = 1
AND interval_start >= CURDATE()
AND interval_start < CURDATE() + INTERVAL 1 DAY
)
SELECT *
FROM (
SELECT
c.interval_start AS TIMESTAMP,
'Stromboerse_connect' AS DEVICE,
'cost' AS TYPE,
'nodes_cost' AS READING,
ROUND(COALESCE(c.kWh_15min,0) * COALESCE(p.price_ct_per_kWh,0) / 100, 6 ) AS VALUE
FROM consumption c
LEFT JOIN price p
ON p.interval_start = c.interval_start
) AS new_values
ON DUPLICATE KEY UPDATE
VALUE = new_values.VALUE;
2026.02.27 16:06:07.812 2: DbRep LogDBRep_Stromboerse_connect_SQL - DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at ./FHEM/93_DbRep.pm line 7073.
2026.02.27 16:06:07.812 2: DbRep LogDBRep_Stromboerse_connect_SQL - ERROR - DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at ./FHEM/93_DbRep.pm line 7073.
