Just noticed that DATETIME columns in tables ‘events’ and ‘states’ are NULL.
The recorder puts states and events into a database on MariaDB for many months last time I looked these columns were filled.
I updated today to 2023.2 and in the logs I can see the timestamp columns were added. Database schema is at 33. No errors in the logs regarding this.
I checked and for all records column is empty. Retention is 7 days. New events and states are added also with empty values for the DATETIME columns.
These columns should be filled, right?
Hpefully somebody can shed some light on what’s going on?
Linked documenation on states suggests these fields are obsolete now. However from there it links to the official database documentation where the fields are part of the CREATE TABLE statement.
I have new events and states added with still no values in these columns.
Would be nice if there is some documentation if these columns are indeed intentionally empty now or if there’s something wrong. Already thinking of having a trigger that will update these columns based on the timestamps. DateTime values are more easily readable than timestamps
If you want to have the DATETIME fields populated you can add trigger as below.
NOTE: no guarantee this might not break in the future. I can imagine at some point the DATETIME columns will be removed in a new datascheme version
CREATE TRIGGER `update_time_fired` BEFORE INSERT ON `events`
FOR EACH ROW SET NEW.`time_fired` = from_unixtime(NEW.`time_fired_ts`)
This has nothing to do with the system you’re running HasOS on.
Changes were made in HasOS where instead of the datetime fields, timestamp fields are used. You can recognize them by the suffix _ts.
If you still want to have the datetime fields populated you can use the trigger I posted above.
No guarantees it keeps working as the datetime fields might be removed at some point.
On postgres the values are now stored as float8 under the last_updated_ts and last_changed_ts columns.
I have adapted my queries so that I now simply go to these two columns and convert them on the fly by using to_timestamp().
Could this be why a query I’ve been using the last few months is now returning NULL values for each column?
If so, could anyone here with more understanding of the changes provide some quick guidance as to what I need to change to make this query work?
I had some help originally creating this before as I am just getting started in SQL/MariaDB and haven’t yet been able to figure out what needs to be changed here to make it work again. I’ve read that a date selector may come to the Energy Dashboard in a future version of HA, but until then, I’ve been using this query to predict my monthly bills and track the payback of my PV system. Thank you!
SET @startdate = "2023-2-21 05:00:00.000000", @enddate = "2023-3-21 04:00:00.000000";
SELECT (SELECT ((SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_load_import" AND (A.start = @enddate)) - (SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_load_import" AND (A.start = @startdate)))) AS HomeEnergyUsage,
(SELECT ((SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_site_import" AND (A.start = @enddate)) - (SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_site_import" AND (A.start = @startdate)))) AS InflowTotal,
(SELECT ((SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_site_export" AND (A.start = @enddate)) - (SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.powerwall_site_export" AND (A.start = @startdate)))) AS OutFlowTotal,
(SELECT SUM(state) FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.dte_daily_consumption_peak" AND (A.start BETWEEN @startdate AND @enddate) AND DAYOFWEEK (start) BETWEEN 2 AND 6 AND HOUR (start) = 23) AS InFlowPeak,
(SELECT SUM(state) FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.dte_daily_export_peak" AND (A.start BETWEEN @startdate AND @enddate) AND DAYOFWEEK (start) BETWEEN 2 AND 6 AND HOUR (start) = 23) AS OutFlowPeak,
(SELECT ((SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.direct_wire_indoor_outdoor_smart_switch_electric_consumption_kwh" AND (A.start = @enddate)) - (SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.direct_wire_indoor_outdoor_smart_switch_electric_consumption_kwh" AND (A.start = @startdate)))) AS 30AmpEVSETotal,
(SELECT ((SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.emporiavue2_total_fcsp_daily_energy" AND (A.start = @enddate)) - (SELECT sum FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.emporiavue2_total_fcsp_daily_energy" AND (A.start = @startdate)))) AS FCSPTotal;
@timovn thank you so much! That looks like it correctly fixed most of my queries (in phpMyAdmin, but not Grafana).
Would you have any recommendations on what other adjustments I’d need to make in these two lines? I’ve attempted changing all of the “start” to “start_ts” but that still is returning NULL results instead of showing me the calculated values like it did before this schema change.
(SELECT SUM(state) FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.dte_daily_consumption_peak" AND (A.start_ts BETWEEN @startdate AND @enddate) AND DAYOFWEEK (start) BETWEEN 2 AND 6 AND HOUR (start) = 23) AS InFlowPeak,
(SELECT SUM(state) FROM `statistics` AS `A` LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id` WHERE `B`.`statistic_id` = "sensor.dte_daily_export_peak" AND (A.start_ts BETWEEN @startdate AND @enddate) AND DAYOFWEEK (start) BETWEEN 2 AND 6 AND HOUR (start) = 23) AS OutFlowPeak,
You’re still using start in DAYOFFWEEK and HOUR. These should be replaced by from_unixtimestamp(start_ts) as well.
Second: you’re using sum(state) but in both tables a column state exists resulting in no records as one of them has the value NULL. You need to specify from which table to get the column.
This one works:
SET @startdate = unix_timestamp("2023-2-21 05:00:00.000000"), @enddate = unix_timestamp("2023-3-21 04:00:00.000000");
SELECT sum(`A`.`state`)
FROM `statistics` AS `A`
LEFT JOIN `statistics_meta` AS `B` ON `A`.`metadata_id` = `B`.`id`
WHERE `B`.`statistic_id` = "sensor.<your_sensor>"
AND (A.start_ts BETWEEN @startdate AND @enddate) AND
DAYOFWEEK (from_unixtime(start_ts)) BETWEEN 2 AND 6 AND HOUR(from_unixtime(start_ts)) = 23 AND `A`.`state` is not null;
