SQL integration creates a sensor with wrong value (MariaDB)

Configuration
1

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:

image
image790×553 34.3 KB

However, when I add exactly the same query to SQL integration, the result is wildly different:
image

I have even tried to create the sensor using YAML, which gives me the same wrong result:

image
image693×265 12.9 KB

Have anyone experienced same behavior? Any clues what is happening?

Thanks!

2

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!

3

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;
4

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.