Can you Extract Data from the History Component

This is the history for my device tracker. Is it possible to pull data from this directly. I’m looking to kick off an automation that triggers every time the location changes away from “away”. Then look at the history and tell me how long it was in the away state. Maybe also pull the current location, and the previous location from that data. I know I can probably do this other ways using other sensors, but if the data is already there, why duplicate. The end game would be something that could keep track of commute times from on map location to another.

tracker

More details, so this is generally what I was thinking Logging all this to a google spreadsheet.

- alias: Commute Time Tracker
  trigger:
  - platform: state
    entity_id: device_tracker.my_iphone
    from: 'Away'
  action:
    service: ifttt.trigger
    data: {"event":"CommuteTracker", "value1":"Previous Place", , "value2":"Current Place", , "value3":"Commute Time"}

Of course I need to figure out how to get
Value1, value2, and value 3 from the history component. Again, I realize I could probably create some virtual devices to hold this info, but was trying to see if the history data could be used, since it seems like everything I need is right there.

There was another thread about something like this and I think the guy ended up dropping it. History statistics component can get information, but i’m not sure it can be used the way you want to use it.

Maybe you could build an automation that stores the timestamp and state change in a mqtt topic. Each time you fire the automation grab the previous value and calculate?

Might be easier to just create a appdaemon app.

EDIT: HIstory statistics component:

I know this isn’t exactly what you’re looking for, but I’ve used the File Notifications platform to create an ongoing log of things for future use.

I added the notify sensor to my configuration.yaml:

notify:
  - name: Enviro Data
    platform: file
    filename: Enviro_Data
    timestamp: true

Then I created an automation that wrote the data I wanted to that file every five minutes.

- alias: Check sensor value and show notification
  trigger:
    platform: time
    minutes: '/5'
    seconds: 00
  action:
    service: notify.enviro_data
    data_template:
      message: ",{{ states('sensor.dark_sky_temperature') }},{{ states('sensor.lr_temperature') }},{{ states('sensor.br_temperature') }},{{ states('sensor.ki_temperature') }},{{ states('sensor.dark_sky_humidity') }},{{ states('sensor.lr_humidity') }},{{ states('sensor.br_humidity') }},{{ states('sensor.ki_humidity') }}"

The result is a file with a new line created every five minutes that looks like this:
2018-09-22T05:35:00.925038+00:00 ,63.9,73.6,67.1,73.6,67.0,49.7,58.9,49.7

I can open a copy as a CSV file in Excel and clean up the data pretty easily. It isn’t ideal if you’re looking for something that updates statistics live. Although I’m sure there are ways to do that. Also, the timestamp is in GMT, so you have to adjust for that.

p.s. To convert that timestamp to a simpler date & time in Excel, I use the functions:
=LEFT(A2,10)
=MID(A2,12,8)

1 Like

There’s a SQL Sensor to fetch data from the history and here is a way using a python script with a command_line sensor.
Maybe you can use it.

Hi Dears,
you can open the home-assistant_v2.db SQLLite DB with a browser and query and extract the historical data that you need if you need the data for some analitical proposals.

I could not open the DB with Hassio up, so I just copy the DB or simply stop hassio during the extraction.

Hey, I am trying to setup this solution but it does not work.
In my configuratin.yaml I have this code:

notify:
  - platform: file
    name: TLogger
    file_path: /www/TData.csv
    timestamp: True

And in the automation.yaml:

- id: '1589866808196'
  alias: sensor_values_to_file
  initial_state: 'on'
  trigger:
    - platform: time
      minutes: '/1'
      seconds: 00
  action:
  - data_template:
        message: "it works"
    service: notify.TLogger

I tried “data” or “data_template”, “file_path” or “file” and some other settings. The result is the same:

The following integrations and platforms could not be set up.

  • notify.file
  • automation
    Please check your config

Anyone an idea?

Hi,
my action in order to write values to a csv file:

  action:
   - service: notify.washer_stats
     data_template: 
       message: '"{{ states.sensor.waschmaschine_watts.state }} w"; {{now().strftime("%d%m%Y")}}; {{now().strftime("%H:%M:%S")}}; {{ states.timer.timer_waschmaschine.state }}'

Check if you have access to the location.
Maybe you need this in your configuration:

homeassistant:
  whitelist_external_dirs:
    - '/config'
    - '/config/www/share'

I located the files in www/share/
Otherwise you do not have permission to write into the config folder

Thx, but it did not help. Still get the same error :frowning:

:arrow_upper_left:
Click on my profile picture and edit your post.
Then i have a few suggestions.

1 Like

Amazing - made my day :laughing:

1 Like

Sorry, it was my first post in such a forum :woozy_face:

1 Like

Your entity_id service will not contain capital letters. It’s most likely: notify.tlogger

Better, i think you see the difference. :slightly_smiling_face:

1.The www folder lives in your config dir and not in the root of your disk.
So try it without the leading /

file_path: www/TData.csv

or with the full path

file_path: /config/www/TData.csv

2.Case matters in linux and python, so check that the file name has the same case as the path

3.All HA entities are lower case, so notify.TLogger is not possible.
Check the entities in Dev Tools/states, should be notify.tlogger

Thx for your help, but the failure is still the same.
I changed everything to small letters, deleted an old entry in devTools/states

2020-05-26 16:22:11 WARNING (MainThread) [homeassistant.loader] You are using a custom integration for hacs which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant.
2020-05-26 16:22:22 ERROR (MainThread) [homeassistant.config] Invalid config for [notify.file]: required key not provided @ data['filename']. Got None. (See /config/configuration.yaml, line 24). Please check the docs at https://www.home-assistant.io/integrations/file
2020-05-26 16:22:22 ERROR (MainThread) [homeassistant.config] Invalid config for [automation]: [minutes] is an invalid option for [automation]. Check: automation->trigger->0->minutes. (See /config/configuration.yaml, line 13). 

This is my logfile entry

You reading your errors? It’s telling you that you’re missing required information in your notify service call.

This one is telling you that you have an unexpected key at the same level as your platform. Which I noticed you’re trying to use a time_pattern trigger.

- id: '1589866808196'
  alias: sensor_values_to_file
  initial_state: 'on'
  trigger:
    - platform: time_pattern #<<<<<<<<< was time
      minutes: '/1'
      seconds: 00
  action:
  - data_template:
      message: "it works"
      filename:   #<<<<<<<<< NEED SOMETHING HERE
    service: notify.tlogger
filename:   #<<<<<<<<< NEED SOMETHING HERE

And what does he need? Path to the csv file?
time or time_pattern dosn’t change anything. still got these errors

So, actually, it doesn’t need filename, you configured the notify file sensor incorrectly. That’s what requires filename.

Exampel pulled from docs:

# Example configuration.yaml entry
notify:
  - name: NOTIFIER_NAME
    platform: file
    filename: FILENAME

Your config:

notify:
  - platform: file
    name: TLogger
    file_path: /www/TData.csv
    timestamp: True

What differences do you see?

When you change that, your automation will actually be this (filename is not needed in the automation):

- id: '1589866808196'
  alias: sensor_values_to_file
  initial_state: 'on'
  trigger:
    - platform: time_pattern #<<<<<<<<< was time
      minutes: '/1'
      seconds: 00
  action:
  - data_template:
      message: "it works"
    service: notify.tlogger

Thank you. One error is gone. Only one to go :slight_smile:

2020-05-26 17:56:54 ERROR (MainThread) [homeassistant.config] Invalid config for [notify.file]: required key not provided @ data[‘filename’]. Got None. (See /config/configuration.yaml, line 24). Please check the docs at File - Home Assistant

I don’t understand what he is missing

Take a look at the example provided in the docs:

notify:
  - name: NOTIFIER_NAME
    platform: file
    filename: FILENAME

Now look at yours

notify:
  - platform: file
    name: TLogger
    file_path: /www/TData.csv
    timestamp: True

What is different? Hint you have an incorrect field. The error is telling you what is wrong, keep that in mind.

HINT 2:

notify:
- field_a: value_a
  field_b: value_b
  field_c: value_c