Database data/query

Configuration
1

Hi Guys and Gals,

I have a RFID reader all working and showing the states of each tag with a input_boolean, simple

  • input_boolean.rfid_tag1
  • input_boolean.rfid_tag2
  • input_boolean.rfid_tag3

so I now need a email report at the end of the day of each tag with the columns:

  • Tag name
  • Time On
  • Time Off

the email is not a problem:

	- service: notify.email_info
		data:
			title: "Tag - Daily Summary"
			message: "Tag - Daily Summary"
			data:
				html: >
					<!DOCTYPE html>
					<html>
					<head>
					<meta charset="utf-8" />
					...

so, within the html section above I have this (formatted to work in Developer Tools/Template)

{% set tag_name = states['input_boolean'] 
| rejectattr('entity_id.14', 'ne', 'r') 
| rejectattr('entity_id.15', 'ne', 'f') 
| rejectattr('entity_id.16', 'ne', 'i') 
| rejectattr('entity_id.17', 'ne', 'd') 
| sort(attribute = 'last_changed', reverse=true) 
| map(attribute = 'object_id')
| list 
| join(',')
%}

{% set tag_name_split = tag_name.split(',') %}
  0/ {{ tag_name_split -}}

{% for i in range(0, tag_name_split | count) -%}
  {% set col1 = states['input_boolean.' + tag_name_split[i]].name -%}
  {% set col2 = '???' -%}
  {% set col3 = '???' %}
  
  1/ {{ col1 }}
  2/ {{ col2 }}
  3/ {{ col3 }}

{%- endfor %}

The problem:

  • Cannot get the timestamp of the DB entry
  • Cannot return more than just the last entry

what I have ended up doing, is adding two input_datetime entities get updated:

  • input_boolean.rfid_tag1
    • input_datetime.rfid_tag1_in
    • input_datetime.rfid_tag1_out
  • input_boolean.rfid_tag2
    • input_datetime.rfid_tag2_in
    • input_datetime.rfid_tag2_out
  • input_boolean.rfid_tag3
    • input_datetime.rfid_tag3_in
    • input_datetime.rfid_tag3_out
{% set tag_name = states['input_boolean'] 
| rejectattr('entity_id.14', 'ne', 'r') 
| rejectattr('entity_id.15', 'ne', 'f') 
| rejectattr('entity_id.16', 'ne', 'i') 
| rejectattr('entity_id.17', 'ne', 'd') 
| sort(attribute = 'last_changed', reverse=true) 
| map(attribute = 'object_id')
| list 
| join(',')
%}

{% set tag_name_split = tag_name.split(',') %}
  0/ {{ tag_name_split -}}

{% for i in range(0, tag_name_split | count) -%}
  {% set col1 = states['input_boolean.' + tag_name_split[i]].name -%}
  {% set col2 = states['input_datetime.' + tag_name_split[i] + '_in'].state -%}
  {% set col3 = states['input_datetime.' + tag_name_split[i] + '_out'].state %}
  
  1/ {{ col1 }}
  2/ {{ col2 }}
  3/ {{ col3 }}

{%- endfor %}

Now, while this is working there really must be a better way?

  • To select the entity_id, with a wildcard. (i have seen some OLD threads on this issue, with no decent resolve)
  • To do this without helper entities, as the data is in the database I just need to be able to get at it.
  • And then moving on from this need to calculate time on in a day, week and month from the database data

Thanks for any help.

2

There’s no way to do wildcard searches on an entity ID using selectattr. You can for loop over the things in states['input_boolean'] though and create a filtered list of entities instead of using rejectattr per character like that. Or you can customize those entities using customize.yaml to add an attribute like rfid: true to each, then you can use selectattr on that.

As for getting to the data in the database, first thing I’d say is take a look at history stats. You’ll have to make one per RFID boolean but its state will simply be the equal to the amount of time that boolean spent in the on or off state over the time period of your choice. Then you don’t have to track that data so manually.

Another option is the SQL sensor which lets you provide a query and sets its state to the result of that query. The only challenge is it requires you specify a column and will set its state to the value in that column in the last row in the resultset. So if you need multiple results you’ll need multiple sensors.

If you really need to get the full resultset of a SQL query then you can do that but its more annoying. You can do it with command line sensor since that can interface with the DB and return the resultset. Then you can stash the rows as attributes of the sensor. But you’ll have to format the data as JSON first since it only lets you specify JSON fields to store as attributes.

You’ll probably also need the SSH add-on for the last option to work. The HA container doesn’t come with a commandline utility for SQL but the SSH add-on comes with mysql pre-installed. So you’ll have to ssh into the add-on to use it and run your query.