I have a sensor (counter) that updates every minute and always increases.
I need to monitor the live difference between now and 1h, 6h, 12h, and 24h ago.
I tried creating 4 statistics sensors as shown below, in hope that the change attribute of each stat sensor would give me the difference i need but, i cannot really understand how the statistics sensor works and the change stays stuck at 0 for now.
(they have been running less than 1h - but sensor data from before they were created was available)
Here is how I did it by querying the database (mariaDB):
- platform: sql
db_url: !secret hassioslave_db_link
queries:
- name: history_1h
query: SELECT * FROM states WHERE entity_id = 'sensor.my_sensor' AND created < DATE_ADD(NOW(), INTERVAL -3-1 HOUR) ORDER BY state_id DESC LIMIT 1;
column: 'state'
db_url: !secret hassioslave_db_link : I have an external DB, so i needed to declare the link, if you are on default recorder settings i think this can be ommited.
created < DATE_ADD(NOW(), INTERVAL -3-1 HOUR) selects everything that was created over an hour ago. The -3 is there because i noticed my DB had wrong timestamps (offset by 3h) which i do not know - cannot bother to fix atm. So if your DB is on the right time, just -1 (or how many hours offset you need) should work.
ORDER BY state_id DESC LIMIT 1 Orders result by state_id (which is chronological) and selects the first, which should be the newest one.
Now that I have the value of 1h ago, I created a template sensor that substracts this, from the current and gives me the difference.
BTW: you can use a small app called DBeaver to test your queries.
Hope this helps someone,
Comments are always welcome, as I have little clue on what im doing
The recorder stores the history with UTC timestamps, so the offset is due to your timezone - so no need to fix the db
I think there will be problems with daylight saving time - in winter you would have UTC+2, while you now have UTC+3 …?
Hi Harry,
it should be possible to fix this inside the SQL-Query, but i give up… I tried several iterations, but as I have no experience with SQL, I don’t get the thing working.
I tried with IN TIME ZONE in several places - see here:
And also instead of NOW():
CONVERT_TZ(NOW(), 'UTC', 'Central European Standard Time')
Either it says wrong syntax or no results.
Can I acces my MariaDB (Installed as Addon via Supervisor on HASSOS / RPi4) with DBeaver over network from my Computer? On what Adress can I reach the DB? How else can I easily test SQL-Queries on my setup??
I’ll send you a screenshot with my debeaver settings once I’m on a pc.
One work around I was thinking (since I don’t know Sal either) would be to replace the “+3” with a HA template. Something that would output the correct time offset depending on the time of date.
Or replace the +3 with a {{states.sensor.utc_offset.state}} and create a template sensor that would calculate the time difference?
But to be honest I didn’t try it at all.
Edit:
I think it’s here, the value should be time difference plus what is in that formula there, which should be either 1 or zero?
(sorry for the crappy formatting I’m on mobile)
Edit2:
So hypothetically, this BD query should work (hoping all special chars are escaped and the template works: SELECT * FROM states WHERE entity_id = 'sensor.whatever' AND created < DATE_ADD(NOW(), INTERVAL -2-{{ now().timetuple().tm_isdst }}-1 HOUR) ORDER BY state_id DESC LIMIT 1;
(-2 is my local time offset, and the code gives either 1 or zero depending on what the date is, I have not tried it tho, if the template will be rendered)
Also, regarding dbeaver settings, they were pretty straightforward, you select mariaDB database type, then enter the local ip address, the db name, username and password and it should connect.
Hello @krash
Why reading all raws and all columns, to only get one value? You can have this value directly, like I did for a statistic value, to get heatpump power consumption over the last 7 days.
SELECT max(s.state) - min(s.state) as "7day"
FROM statistics_meta sm
inner join statistics s on s.metadata_id = sm.id
where sm.statistic_id = "sensor.heatpump_hz_power_consumption"
and s.created_ts > (strftime('%s', 'now') - (1*60*60*24*7))
You also can have for example three different values at once with
SELECT (select round(max(s.state) - min(s.state),0)
FROM statistics_meta sm
inner join statistics s on s.metadata_id = sm.id
where sm.statistic_id = "sensor.heatpump_hz_power_consumption" and s.created_ts > (strftime('%s', 'now') - (1*60*60*24))) as "24h",
(select round(max(s.state) - min(s.state),0)
FROM statistics_meta sm
inner join statistics s on s.metadata_id = sm.id
where sm.statistic_id = "sensor.heatpump_hz_power_consumption" and s.created_ts > (strftime('%s', 'now') - (1*60*60*24*7))) as "7day",
(select round(max(s.state) - min(s.state),0)
FROM statistics_meta sm
inner join statistics s on s.metadata_id = sm.id
where sm.statistic_id = "sensor.heatpump_hz_power_consumption" and s.created_ts > (strftime('%s', 'now') - (1*60*60*24*30))) as "30day"
You can get different kind of values with only 1 SQL sensor.
A second point … if you make date selection directly in the database, you don’t have to handle these time zone issues.
