Hallo Heiko,
ich schreite mit meinem MySQL ja nun immer weiter fort :-) und stoße natürlich an die Grenzen vom DbRep beim Parsen :-(
Meine Vermutung ist das "with", mit dem ich die Bandwurm SELECTs lesbarer aufteile.
Hier werte ich mein SmartMeter aus und berechne den Verbrauch passend zur Stromboerse im 15 Minuten Takt.
Das dann mulipliziert mit dem jeweiligen Preis wird mit dem INSERT direkt wieder als Kosten in die DB geschrieben.
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.
Wäre es denkbar eine Art Master Modus zu etablieren, bei dem das MySQL nicht überprüft wird?
Der Code läuft ohne Probleme in der DB, ansonsten müsste ich das ganze wieder in Proceduren in der DB ablegen ;-)
VG Christian