How often Recorder flush data to database? Seem like a significant delay?

Hi all,

I am trying to setup an SQL sensor to average out some weather sensor readings. (ie: I want to take last 3-4 wind readings and average them out and use that as a value for the wind sensor)

While trying to workout my required SQL that would AVR readings from rows in DB for last 20 mins, I noticed, I actually do NOT have data for last 20 mins in MariaDB. My sensor does display latest data in the GUI, so the data is coming, but recorder does not seem to write it to a database. If I do a select for previous day, I do find data for the WHOLE day. So that means, eventually data does get into the database.

This leads me to believe that recorder buffers the data and flushes it out maybe a few times a day instead of in real time. Am I correct? If that is true, then making my SQL sensor that expects recent data to be in the database will not work.

Anybody knows if there is any parameter to make recorder flush data more often? And reasons why it buffers?
Maybe because trying to minimize writes to SD card?

What kind of solution could I use to have access to say 3-5 last readings of the sensor, if I can’t get it from the recorder database? (aside from writing my own custom Daemon? )

Here is a select statement you can use to see if you yourself have any data from sensors for last 20 mins in your recorder database :

select * from states
where domain = 'sensor' and created >= NOW() - INTERVAL 20 MINUTE
order by created desc

I think the problem is that the timestamps in the db are in utc.
So you have to add some hours depending on your timezone.

SELECT CURRENT_TIMESTAMP(); 
--> 2018-02-28 16:03:15.0
select max(created) from states limit 1;
--> 2018-02-28 15:04:20.0

Hi VDRRainer,

I thought about the timezone before posting as well. I think you are right, though I initially thought that it should not matter since time computation operation NOW() - INTERVAL 20 MINUTE is in UTC the interval should still be valid (ie: last 20 mins in UTC. Now() is still NOW no matter what display timezone is used. But it seem not to be the case.

MariaDB docs indicate timezone in the table automatically converted to current timezone when used. But not for all date types:
https://mariadb.com/kb/en/library/time-zones/

Time zone effects

Some functions are affected by the time zone settings. These include

NOW()
CURTIME()
UNIX_TIMESTAMP()
as well as values stored and retrieved from TIMESTAMP columns. The latter are converted to UTC (Coordinated Universal Time) when stored, and converted back when retrieved.

Some functions are not affected. These include:

UTC_TIMESTAMP()
as well as DATETIME, DATE and TIME columns.

Tried this: (still no results)

select domain, entity_id , CONVERT_TZ(created, 'UTC',  'Asia/Singapore') as created from states
where domain = 'sensor'  and CONVERT_TZ(created, 'UTC',  'Asia/Singapore') >= now() - INTERVAL 20 MINUTE
order by created desc

Also tried this: (now using UTC timestamp )… Still wrong.

select domain, entity_id , created from states
where domain = 'sensor' and last_changed >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute)
order by created desc

My local time is actually 1 hour past UTC.
These querys work for me and return all the same (records from the last 20 minutes).

select domain, entity_id , created from states
where entity_id = 'sensor.processor_use' and created >= NOW() - INTERVAL 80 MINUTE
order by created desc

select domain, entity_id , created from states
where entity_id = 'sensor.processor_use' and created >= UTC_TIMESTAMP() - INTERVAL 20 MINUTE
order by created desc

select domain, entity_id , created from states
where entity_id = 'sensor.processor_use' and last_changed >= ADDDATE( UTC_TIMESTAMP(), INTERVAL -20 minute)
order by created desc

Seem like the SQL actually works… I was getting results back as I tried to execute commands earlier in a day.
It just occurred to me that my recorder actually stops recording after sun goes into a state “below_horizon

I actually verified it and can see that the last update in my table is change of state for the sun to below_horizon
This is very bizarre. That is why yesterday when I tried late at night, I saw no rows for last 20 mins.

Any idea on what setting in the recorder does this?

state_id|domain|entity_id|state|attributes|event_id|last_changed|last_updated|created																					
519345|sun|sun.sun|below_horizon|{"next_dawn": "2018-03-01T22:52:58+00:00"	 "next_dusk": "2018-03-01T11:40:11+00:00"	 "next_midnight": "2018-03-01T17:16:27+00:00"	 "next_noon": "2018-03-02T05:16:28+00:00"	 "next_rising": "2018-03-01T23:13:48+00:00"	 "next_setting": "2018-03-02T11:19:09+00:00"	 "elevation": -5.07	 "azimuth": 262.77	 "friendly_name": "Sun"}||2018-03-01 11:19:20.0|2018-03-01 11:36:30.0|2018-03-01 11:36:30.0													
519339|zwave|zwave.office_sensor|initializing|{"node_id": 22	 "node_name": "office_sensor"	 "manufacturer_name": "Philio Technology Corporation"	 "product_name": "PST02-A 4 in 1 Multi-Sensor"	 "old_entity_id": "zwave.office_sensor_22"	 "new_entity_id": "zwave.office_sensor"	 "query_stage": "CacheLoad"	 "is_awake": false	 "is_ready": false	 "is_failed": false	 "is_info_received": true	 "max_baud_rate": 40000	 "is_zwave_plus": true	 "capabilities": ["beaming"	 "routing"]	 "sentCnt": 1	 "sentFailed": 1	 "retries": 0	 "receivedCnt": 447	 "receivedDups": 2	 "receivedUnsolicited": 447	 "sentTS": "2018-02-28 21:42:27:076 "
519340|sensor|sensor.alarm_type_2|0|{"node_id": 22	 "value_index": 0	 "value_instance": 1	 "value_id": "72057594413072385"	 "old_entity_id": "sensor.office_sensor_alarm_type_22_0"	 "new_entity_id": "sensor.office_sensor_alarm_type"	 "unit_of_measurement": ""	 "friendly_name": "office_sensor Alarm Type"}||2018-03-01 11:36:17.0|2018-03-01 11:36:17.0|2018-03-01 11:36:17.0														
519341|sensor|sensor.alarm_level_2|0|{"node_id": 22	 "value_index": 1	 "value_instance": 1	 "value_id": "72057594413072401"	 "old_entity_id": "sensor.office_sensor_alarm_level_22_1"	 "new_entity_id": "sensor.office_sensor_alarm_level"	 "unit_of_measurement": ""	 "friendly_name": "office_sensor Alarm Level"}||2018-03-01 11:36:17.0|2018-03-01 11:36:17.0|2018-03-01 11:36:17.0														
519342|sensor|sensor.philio_technology_corporation_pst02a_4_in_1_multisensor_sourcenodeid|0|{"node_id": 22	 "value_index": 2	 "value_instance": 1	 "value_id": "72057594413072417"	 "old_entity_id": "sensor.office_sensor_sourcenodeid_22_2"	 "new_entity_id": "sensor.office_sensor_sourcenodeid"	 "unit_of_measurement": ""	 "friendly_name": "office_sensor SourceNodeId"}||2018-03-01 11:36:17.0|2018-03-01 11:36:17.0|2018-03-01 11:36:17.0														
519343|sensor|sensor.philio_technology_corporation_pst02a_4_in_1_multisensor_burglar|254|{"node_id": 22	 "value_index": 10	 "value_instance": 1	 "value_id": "72057594413072545"	 "old_entity_id": "sensor.office_sensor_burglar_22_10"	 "new_entity_id": "sensor.office_sensor_burglar"	 "unit_of_measurement": ""	 "friendly_name": "office_sensor Burglar"}||2018-03-01 11:36:17.0|2018-03-01 11:36:17.0|2018-03-01 11:36:17.0

There’s definitely no setting for this.
Real strange!
Which HA are you on?
Does the MariaDB run on a RPi? If yes maybe SD Card problem?
Anything in the MariaDB logs?

And it started sending data to the database at some later time again… Really, really strange.
I will dig into this more later.

I am on Haas.io 0.63.3 running on RPi

I wonder if it has to do with a flurry of activities that kick-in around sun going under_horizon. At that point, all my lights start to trigger with motion detection and lots of house automation kick in… I wonder if CPU utilization ramps up or IO becomes busy etc… and recorder start buffering data for a while till all this ease up? (my wind speculation ) . Will look into this soon if anything in the logs provide explanation.

Thank you for helping!