ha-timeRatioScript/README.md

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:

time_ratio = matching_seconds / 3600

Without a dependency entity:

matching_seconds = seconds where target_entity == target_state

With a dependency entity:

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

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:

/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:

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:

/config/scripts/export_state_ratio_csv.sh

Paste the shell script into that file.

Then make it executable:

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:

mkdir -p /config/data

Home Assistant Setup

shell_command

Add this to configuration.yaml, or to your included shell command YAML file:

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:

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:

DD-MM-YYYY HH:MM:SS

Example:

01-05-2026 00:00:00

Usage Examples

Evaluate a Target Entity Without Dependency

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:

seconds(binary_sensor.office_presence == on) / 3600

for every hour in the requested interval.

Evaluate a Target Entity With Dependency

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:

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:

User profile → Security → Long-lived access tokens → Create token

Create a token, for example named:

state_ratio_export

Store the Token

The script expects the token here:

/config/scripts/.state_ratio_ha_token

Create the file:

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:

GET /api/

Then creates a persistent notification with:

POST /api/services/persistent_notification/create

Default Home Assistant URL:

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:

/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.

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:

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:

Preparing Home Assistant persistent notification

If the log says:

WARNING: No HA token found.

verify that this file exists:

/config/scripts/.state_ratio_ha_token

If the log says:

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:

/config/ip_bans.yaml

Then restart Home Assistant.

BusyBox date Error

Some Home Assistant environments use BusyBox date, which does not support:

date --iso-8601=seconds

The script uses the BusyBox-compatible form:

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.