Hello,
I’m trying to create some sensors based on long term statistics I plan to use in “energy management” automations. Anyhow, I have created a SQL query to see what was average house consumption during working days between ~ 18:00 - 24:00 during past month. Here goes:
SELECT round(AVG(t.state_diff), 2) avg_state
FROM (SELECT FROM_UNIXTIME(start_ts, '%Y-%m-%d') as state_date
, round(MAX(state) - MIN(state), 2) as state_diff
FROM `statistics`
WHERE metadata_id = 804
AND start_ts >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 month))
AND WEEKDAY(FROM_UNIXTIME(start_ts)) IN (0,1,2,3,4)
AND (HOUR(FROM_UNIXTIME(start_ts)) = 17 OR HOUR(FROM_UNIXTIME(start_ts)) = 23)
GROUP BY state_date
ORDER BY start_ts ASC
) t;
It works great when I try to run the query in phpMyAdmin:
However, when I add exactly the same query to SQL integration, the result is wildly different:
I have even tried to create the sensor using YAML, which gives me the same wrong result:
Have anyone experienced same behavior? Any clues what is happening?
Thanks!
OK, after some digging I found out that the problem is that the data in the database is stored with UTC timestamp (which is quite standard). myPHPadmin somehow (from browser or from system/HA setting) obtains correct time zone and does the time conversion, however, the SQL integration does not do such a thing, unfortunatelly.
Sure, I can use CONVERT_TZ() function, but since the MariaDB’s time zone table is not populated and we use daylight saving time, it would be cumbersome at best.
Any thoughts? Thanks!
So, in the end I have used following:
SELECT round(AVG(t.state_diff), 2) avg_state
FROM (SELECT FROM_UNIXTIME(start_ts, '%Y-%m-%d') as state_date
, round(MAX(state) - MIN(state), 2) as state_diff
FROM `statistics`
WHERE metadata_id = 804
AND start_ts >= UNIX_TIMESTAMP(DATE_SUB(CONVERT_TZ(NOW(),'+00:00',@@global.time_zone), INTERVAL 4 WEEK))
AND start_ts < UNIX_TIMESTAMP(DATE(CONVERT_TZ(NOW(),'+00:00',@@global.time_zone)))
AND WEEKDAY(CONVERT_TZ(FROM_UNIXTIME(start_ts),'+00:00',@@global.time_zone)) IN (0,1,2,3,4)
AND (HOUR(CONVERT_TZ(FROM_UNIXTIME(start_ts),'+00:00',@@global.time_zone)) = 17 OR HOUR(CONVERT_TZ(FROM_UNIXTIME(start_ts),'+00:00',@@global.time_zone)) = 23)
GROUP BY state_date
ORDER BY start_ts ASC
) t;
So, in the end I have created an issue on github and it was resolved in version 2023.4.3, so the original code works just fine.