Do you know how I can reject non numeric states (e.g. unknown/unavailable) from this MariaDB query?
SELECT state
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.total_cost_today'
AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC
LIMIT 1;
Basically I’m after the the nearest valid value of the sensor from 24 hours before. The query above occasionally turns up non-numeric state warnings.
According to Stack Overflow this selects only numeric values but I am not sure how to include it:
SELECT c1
FROM t1
WHERE c1 REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?
```;
Would not the regex on ‘state’ just be another part of your ‘WHERE’ clause?
SELECT state
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.total_cost_today'
AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
AND state REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?'
ORDER BY last_updated_ts DESC
LIMIT 1;
I’m not versed in MarinaDB, however in Postgresql, there need to be a closing quote ’ on the regex expression.
