# Home Assistant State Ratio Export This project provides a Home Assistant shell-script workflow for exporting hourly and daily time-ratio statistics from the Home Assistant recorder database. It evaluates how much of each hour a target entity was in a selected target state. Optionally, the evaluation can be conditioned on a second dependency entity being in a selected dependency state. The script writes a CSV file, a summary text file, and a detailed log file into a request-specific output folder under `/config/data`. It can also create a Home Assistant persistent notification via the Home Assistant REST API when the export has finished. ## What the Script Calculates For every hour in the requested interval, the script calculates: ```text time_ratio = matching_seconds / 3600 ``` Without a dependency entity: ```text matching_seconds = seconds where target_entity == target_state ``` With a dependency entity: ```text matching_seconds = seconds where target_entity == target_state AND dependence_entity == dependence_state ``` The denominator is always one full hour, even if the requested interval starts or ends within an hour. If the target sensor was created inside the requested time interval, the script ignores all days and hours before the target sensor's first recorder entry when calculating averages. ## Workflow Overview ```mermaid flowchart TD A[Home Assistant Script] --> B[shell_command] B --> C[Bash Export Script] C --> D[SQLite Recorder DB] D --> C C --> E[CSV Output] C --> F[Summary TXT] C --> G[Detailed Log] C --> H[Home Assistant REST API] H --> I[Persistent Notification] ``` ## Output Structure Each request creates a dedicated folder directly in `/config/data`. Example: ```text /config/data/20260509-225400_state_ratio_binary_sensor.example_on/ ├── 20260509-225400_state_ratio_binary_sensor.example_on.csv ├── 20260509-225400_state_ratio_binary_sensor.example_on_summary.txt └── 20260509-225400_state_ratio_binary_sensor.example_on.log ``` The request timestamp is placed at the beginning of both the folder name and file names. ## Generated Files ### CSV File The CSV contains one row per evaluated hour. Important columns include: | Column | Description | |---|---| | `request_time` | Timestamp when the export was generated | | `target_friendly_name` | Friendly name of the target entity, if available | | `target_entity` | Entity ID of the evaluated target entity | | `target_state` | State that is evaluated for the target entity | | `dependence_entity` | Optional dependency entity | | `dependence_state` | Optional dependency state | | `requested_interval_start` | Original requested interval start | | `requested_interval_stop` | Original requested interval stop | | `effective_interval_start` | Actual start used for averaging, adjusted if the target sensor was created inside the requested interval | | `sensor_created_inside_requested_interval` | Whether pre-creation hours were ignored | | `date` | Date of the hourly row | | `day_name` | Weekday name | | `hour_of_day` | Hour in `HH:00` format | | `hour_start` | Start timestamp of the hour | | `hour_stop` | Stop timestamp of the hour | | `numerator_seconds` | Seconds matching the requested state condition | | `denominator_seconds` | Always `3600.0` | | `time_ratio` | Hourly time ratio | | `daily_average_time_ratio` | Average ratio for the respective day | ### Summary File The summary file contains: 1. Request metadata 2. Overall average time ratio for the effective interval 3. Daily average time ratio table 4. Hourly time ratio matrix The hourly matrix has hours as rows and dates as columns. A second header row shows the weekday name below each date. Example: ```text Hourly Time Ratio Matrix ------------------------ Rows are hours of the day. Columns are dates. Hours before target sensor creation are omitted. The second header line contains the weekday name for each date. Hour 2026-05-01 2026-05-02 2026-05-03 Friday Saturday Sunday -------------------------------------------------- 00:00 0.000000 0.125000 0.000000 01:00 0.250000 0.000000 0.000000 02:00 0.100000 0.000000 0.500000 ``` ### Log File The log file contains: - Input parameters - Output paths - Database connection status - SQL query information - Entity validation - Target sensor creation detection - State event loading - Segment construction - CSV generation - Summary generation - Home Assistant REST API notification details ## Requirements - Home Assistant with recorder enabled - SQLite recorder database at `/config/home-assistant_v2.db` - Python 3 available in the Home Assistant environment - Bash-compatible shell - Optional: Home Assistant long-lived access token for REST API notifications The script is designed for the default Home Assistant SQLite recorder database. ## Installation ### Create the Script File Create this file: ```text /config/scripts/export_state_ratio_csv.sh ``` Paste the shell script into that file. Then make it executable: ```bash chmod +x /config/scripts/export_state_ratio_csv.sh ``` ### Create the Output Directory The script writes to `/config/data` by default. If it does not exist yet, create it: ```bash mkdir -p /config/data ``` ## Home Assistant Setup ### shell_command Add this to `configuration.yaml`, or to your included shell command YAML file: ```yaml shell_command: export_state_ratio_csv: >- /config/scripts/export_state_ratio_csv.sh "{{ target_entity }}" "{{ target_state }}" "{{ dependence_entity | default('', true) }}" "{{ dependence_state | default('', true) }}" "{{ interval_start }}" "{{ interval_stop }}" ``` After changing `shell_command`, restart Home Assistant. ### Home Assistant Script Add this to `scripts.yaml`: ```yaml export_state_ratio_csv: alias: Export state ratio CSV description: > Exports hourly time ratios for a target entity being in a target state. Optionally, the ratio is limited to times where another dependence entity is in a defined dependence state. The denominator is always one full hour. mode: queued fields: target_entity: name: Target entity description: Entity whose state should be evaluated. required: true selector: entity: {} target_state: name: Target state description: State of the target entity to evaluate. required: true selector: text: {} dependence_entity: name: Dependence entity description: Optional entity that must be in dependence state. required: false selector: entity: {} dependence_state: name: Dependence state description: Optional required state of the dependence entity. required: false selector: text: {} interval_start: name: Interval start description: "Strict format: DD-MM-YYYY HH:MM:SS" required: true selector: text: {} interval_stop: name: Interval stop description: "Strict format: DD-MM-YYYY HH:MM:SS" required: true selector: text: {} sequence: - service: shell_command.export_state_ratio_csv data: target_entity: "{{ target_entity }}" target_state: "{{ target_state }}" dependence_entity: "{{ dependence_entity | default('', true) }}" dependence_state: "{{ dependence_state | default('', true) }}" interval_start: "{{ interval_start }}" interval_stop: "{{ interval_stop }}" ``` ## Timestamp Format The interval timestamps must use this strict format: ```text DD-MM-YYYY HH:MM:SS ``` Example: ```text 01-05-2026 00:00:00 ``` ## Usage Examples ### Evaluate a Target Entity Without Dependency ```yaml service: script.export_state_ratio_csv data: target_entity: binary_sensor.office_presence target_state: "on" interval_start: "01-05-2026 00:00:00" interval_stop: "07-05-2026 23:59:00" ``` This calculates: ```text seconds(binary_sensor.office_presence == on) / 3600 ``` for every hour in the requested interval. ### Evaluate a Target Entity With Dependency ```yaml service: script.export_state_ratio_csv data: target_entity: binary_sensor.office_presence target_state: "on" dependence_entity: binary_sensor.phone_home dependence_state: "on" interval_start: "01-05-2026 00:00:00" interval_stop: "07-05-2026 23:59:00" ``` This calculates: ```text seconds(binary_sensor.office_presence == on AND binary_sensor.phone_home == on) / 3600 ``` for every hour in the requested interval. ## Home Assistant REST API Notification The script can send a persistent notification in Home Assistant after finishing. ### Create a Long-Lived Access Token In Home Assistant: ```text User profile → Security → Long-lived access tokens → Create token ``` Create a token, for example named: ```text state_ratio_export ``` ### Store the Token The script expects the token here: ```text /config/scripts/.state_ratio_ha_token ``` Create the file: ```bash printf '%s' 'PASTE_YOUR_LONG_LIVED_ACCESS_TOKEN_HERE' > /config/scripts/.state_ratio_ha_token chmod 600 /config/scripts/.state_ratio_ha_token ``` Do not include quotes, Markdown links, labels, or extra text. The file should contain only the token. ### API Endpoint Used The script validates the token with: ```text GET /api/ ``` Then creates a persistent notification with: ```text POST /api/services/persistent_notification/create ``` Default Home Assistant URL: ```text http://127.0.0.1:8123 ``` If needed, override it through the `HA_URL` environment variable. ## Recorder Database Access The script opens the SQLite recorder database in read-only mode: ```text /config/home-assistant_v2.db ``` It reads from these recorder tables: - `states` - `states_meta` - `state_attributes` The script reconstructs state intervals from recorder state-change events. ```mermaid sequenceDiagram participant HA as Home Assistant Script participant SH as Bash Script participant DB as Recorder SQLite DB participant FS as /config/data participant API as HA REST API HA->>SH: Call shell_command with parameters SH->>DB: Validate target and dependency entities SH->>DB: Query first target state timestamp SH->>DB: Query target and dependency state events SH->>SH: Build continuous state segments SH->>SH: Calculate hourly and daily ratios SH->>FS: Write CSV, summary and log SH->>API: Validate token SH->>API: Create persistent notification ``` ## Sensor Creation Handling If the target entity was first recorded inside the requested interval, the script uses the target sensor's first recorder timestamp as the effective start. Example: ```text Requested interval: 01-05-2026 00:00:00 to 07-05-2026 23:59:00 Target first seen: 03-05-2026 14:23:10 Effective start: 03-05-2026 14:23:10 ``` All hours before the effective start are omitted from: - CSV rows - Daily averages - Overall average - Hourly matrix The CSV and summary include metadata showing whether this adjustment was applied. ## Configuration Variables The following variables can be adjusted at the top of the shell script or overridden through the environment. | Variable | Default | Description | |---|---|---| | `OUT_DIR` | `/config/data` | Base directory for request folders | | `DB_PATH` | `/config/home-assistant_v2.db` | Home Assistant recorder SQLite database | | `HA_TZ` | `Europe/Berlin` | Timezone for timestamp parsing and output | | `HA_URL` | `http://127.0.0.1:8123` | Home Assistant REST API base URL | | `HA_TOKEN_FILE` | `/config/scripts/.state_ratio_ha_token` | File containing the long-lived access token | | `HA_TOKEN` | empty | Optional token from environment variable | ## Troubleshooting ### No Notification Appears Check the generated log file in the request folder. Search for: ```text Preparing Home Assistant persistent notification ``` If the log says: ```text WARNING: No HA token found. ``` verify that this file exists: ```text /config/scripts/.state_ratio_ha_token ``` If the log says: ```text HTTP 401: Unauthorized ``` the token file was found, but the token is invalid. Create a new long-lived access token and overwrite the token file. ### Invalid Authentication or IP Ban If Home Assistant reports invalid authentication from localhost, remove stale entries from: ```text /config/ip_bans.yaml ``` Then restart Home Assistant. ### BusyBox date Error Some Home Assistant environments use BusyBox `date`, which does not support: ```bash date --iso-8601=seconds ``` The script uses the BusyBox-compatible form: ```bash date -Iseconds ``` ### Entity Not Found The script checks `states_meta` for the target and dependency entities. If an entity is not found, make sure: - Recorder is enabled - The entity has existed long enough to be recorded - The entity ID is spelled correctly - The database path is correct ### Empty Output The CSV can contain only headers if: - The target entity has no recorder rows - The target entity was created after the requested interval ended - The effective interval is empty Check the summary and log for the effective interval. ## Notes - The script assumes SQLite recorder storage. - MariaDB or PostgreSQL recorder setups require adapting the SQL connection and query execution code. - The script reads the database in read-only mode. - The denominator is intentionally always `3600`, even for partial first or last hours. - Dependency fields can be left empty. If one dependency field is set, both must be set. ## License Use and adapt this script freely for your own Home Assistant setup.