v1.1.1

Enables a real GitHub-tracked download counter (HACS zip_release), automated via CI on every future release. No functional changes to the integration itself.

v1.1.0

Added

• include_extra_columns (per config entry, off by default). The response set was fixed, so a
  view carrying columns of its own — a label, a unit, a formatted timestamp — had them dropped,
  because the reader never selected them. With the option on, every remaining column of the queried
  table comes along and can feed a table view directly.

  Column names come from information_schema through _fetch_table_columns, so they are by
  definition real columns of that table, and they are quoted as well. Reserved names (entity_id,
  value, state, time, bucket, minute) are never selected twice. With downsample > 0 each
  extra column is wrapped in last() so GROUP BY stays valid.

  With the option off the generated SQL is character for character what it was, so existing
  dashboards cannot be affected.

• sum as a downsample_method, alongside avg and last. For rows that are already a
  quantity per bucket — a cost, or a number of kWh per hour — asking for daily buckets should add
  those hours up rather than average them or take the last one. Only used when the caller asks for
  it explicitly; the automatic choice is unchanged.

Documentation

• README.nl.md added, with language badges on both READMEs, matching the other integrations.
• New sections on extra columns and a worked example: daily peaks presented as a readable table.

v1.0.11 - Real schema, minute table SQL, lag fix

The documented schema no longer matched reality

Current Scribe writes states_raw keyed on metadata_id, with entities as a lookup table and states as a view over the two. The old SQL assumed a states table carrying entity_id directly. A continuous aggregate may only read a single hypertable, so it now groups by metadata_id and a thin view adds entity_id back.

New: SQL/scribe/

The two scripts the setup actually needs, neither of which was in the repository before.

01_sensor_minute_aggregate.sql builds the 1-minute continuous aggregate sensor_minute_aggregate plus the sensor_minute_aggregate_entity lookup view, with retention, compression and a refresh policy.

02_sensor_minute_table.sql builds the prefilled sensor_minute hypertable, its indexes and policies, the refresh procedure and the job that runs every minute. This is the table charts should query — the older LOCF views (sensor_minute_scribe, sensor_minute_ltss) build a grid of every minute since the oldest bucket times every entity and run a correlated subquery per cell, which times out on any real dataset.

Fixed: the minute table lagged reality permanently

sensor_minute_refresh() only appended rows after max(minute). The continuous aggregate trails real time by a minute or two, so rows written for the most recent minutes still carried the previous value — and because they were never revisited, that lag became permanent.

It shows up on counters. A utility_meter with cycle: hourly resets exactly at :00, but in the minute table the reset appeared two minutes later, so a chart bucketing on the clock hour sampled every hour too early. Bars under-reported and the tail of each period landed in the next bar.

The procedure now reprocesses a short trailing window (5 minutes by default, configurable) and upserts on (minute, entity_id).

Both scripts are re-runnable

• add_job is guarded, so running the file again cannot create a second minute job competing for the same rows
• the parameterless procedure is dropped before the new signature is created, so it replaces rather than overloads — CREATE OR REPLACE with a different argument list would have left the old body in place

README rewritten

• Requirements, including a TimescaleDB 2.13 minimum (by_range)
• An explicit install order, and why the connection test passing does not mean the table exists
• Database roles and grants, with the grant blocks the SQL needs when the reader connects as a different role than the one that created the objects
• The state versus value split: for a numeric entity only value carries the reading, while state holds a placeholder. Reading state first flattens every numeric series to zero without raising an error
• Which table to query, and why not the LOCF views
• A sizing warning on the initial backfill: it writes entities × days × 1440 rows, easily tens of millions in one transaction. A day-at-a-time loop is included

Verified

Both scripts were run end to end on a clean database: 14 of 14 and 15 of 15 statements succeed, the aggregate backfills, the seed works, the scheduled job takes over and keeps the table growing, and a reader-style downsampled query returns correct data.

v1.0.10

Changed logo name

Full Changelog: v1.0.9...v1.0.10

v1.0.9

Added brand logo's

Full Changelog: v1.0.8...v1.0.9

v1.0.8

Bump to version 1.0.8

Full Changelog: v1.0.7...v1.0.8

v1.0.7

Timescale Database Reader

A Home Assistant integration for reading historical data from a TimescaleDB database that is filled by the LTSS integration or the Scribe integration. This integration does not work with arbitrary TimescaleDB databases; the schema and data must match that of LTSS or Scribe.
If you need a different database schema reach out to the author or consider contributing a new database reader for your specific schema.

Supported Databases

This integration is designed specifically for TimescaleDB databases that are populated by:

• LTSS (Long Term Statistics Store) — table: ltss
• Scribe — table: states

Other TimescaleDB schemas are not supported.

Example Query

You can use the WebSocket API or a Home Assistant service to run a query. For example, to fetch the temperature of a sensor:

SELECT time, state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
ORDER BY time ASC;

The integration also supports downsampling with time_bucket for efficient charting:

SELECT time_bucket('5 minutes', time) AS bucket,
       avg(state::double precision) AS avg_state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
GROUP BY bucket
ORDER BY bucket ASC;

Example: Query via WebSocket API

You can use the Home Assistant WebSocket API to query TimescaleDB data directly. Here is an example using Python and the websockets library:

import asyncio
import websockets
import json

async def query_timescale():
    uri = "ws://homeassistant.local:8123/api/websocket"  # Change to your Home Assistant URL
    async with websockets.connect(uri) as ws:
        # Authenticate (replace with your long-lived access token)
        await ws.send(json.dumps({"type": "auth", "access_token": "YOUR_LONG_LIVED_TOKEN"}))
        print(await ws.recv())  # Auth response

        # Send the query
        await ws.send(json.dumps({
            "id": 1,
            "type": "timescale/query",
            "sensor_id": "sensor.temperature_woonkamer",
            "start": "2026-01-01T00:00:00Z",
            "end": "2026-01-01T12:00:00Z",
            "limit": 1000,
            "downsample": 0
        }))
        # Receive the result
        while True:
            msg = await ws.recv()
            print(msg)
            if 'result' in msg or 'error' in msg:
                break

asyncio.run(query_timescale())

Replace YOUR_LONG_LIVED_TOKEN with your Home Assistant long-lived access token. The response will contain the queried data as JSON.

Visualization: Plotly Card

A special Home Assistant card has been developed to work with this integration: timescale-plotly-card. This allows you to easily create charts from your TimescaleDB data in the Home Assistant dashboard.

Multiple Database Support

This integration supports connecting to multiple TimescaleDB databases at the same time, as long as each is filled by LTSS or Scribe. You can add multiple database connections via the Home Assistant UI (each as a separate integration instance).

When querying data (for example, from a custom card or via the WebSocket API), you can specify which database to use by passing the database parameter. If you do not specify a database, the first configured database will be used by default.

Important: For Scribe, set the table option to states. For LTSS, set the table option to ltss. The correct table must be specified for each database in your query or card configuration.

Example: Querying a specific database and table

# Example for a custom card or direct WebSocket query
 type: custom:timescale-plotly-card
 database: scribe
 table: states
 sensor_id: sensor.amber_4h_average_ambient_temperature

 type: custom:timescale-plotly-card
 database: ltss
 table: ltss
 sensor_id: sensor.temperature_woonkamer

If you use multiple databases in one card (e.g., with multiple series), set the database and table options for each series as needed. See the wiki for the Timescale Plotly Card for more details on how to configure multiple series with different databases.

Installation

HACS (Recommended)

1. Go to HACS → Integrations
2. Click ⋮ → Custom repositories
3. Add URL: https://github.com/remmob/timescale_database_reader
4. Category: Integration
5. Search for 'Timescale Database Reader' and install
6. Restart Home Assistant

Manual

1. Copy this repository to your Home Assistant custom_components directory:

   custom_components/timescale_database_reader
   

2. Restart Home Assistant.

Configuration

In Home Assistant, go to Settings > Integrations > Add Integration and search for Timescale Database Reader. Enter your database host, port, username, password, and database name.

Issues & Contributions

Problems or want to contribute? Open an issue or pull request on GitHub.

---

©2026 Bommer Software | Author: Mischa Bommer

Note: This integration is a work in progress. Features and functionality may change or be incomplete.

v1.0.4

Timescale Database Reader

A powerful Home Assistant integration for reading data from any TimescaleDB database.

Universal TimescaleDB Support

This integration works with any TimescaleDB (PostgreSQL) database, as long as you provide the correct connection details (host, port, username, password, database). You do not need a special Home Assistant database.

Tested with LTSS and Scribe

This integration has been tested with a TimescaleDB database filled with values by the LTSS integration. Note: LTSS is no longer maintained. Currently, testing is ongoing with the Home Assistant Scribe integration as an alternative for storing long-term statistics in TimescaleDB.

Example Query

You can use the WebSocket API or a Home Assistant service to run a query. For example, to fetch the temperature of a sensor:

SELECT time, state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
ORDER BY time ASC;

The integration also supports downsampling with time_bucket for efficient charting:

SELECT time_bucket('5 minutes', time) AS bucket,
       avg(state::double precision) AS avg_state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
GROUP BY bucket
ORDER BY bucket ASC;

Example: Query via WebSocket API

You can use the Home Assistant WebSocket API to query TimescaleDB data directly. Here is an example using Python and the websockets library:

import asyncio
import websockets
import json

async def query_timescale():
    uri = "ws://homeassistant.local:8123/api/websocket"  # Change to your Home Assistant URL
    async with websockets.connect(uri) as ws:
        # Authenticate (replace with your long-lived access token)
        await ws.send(json.dumps({"type": "auth", "access_token": "YOUR_LONG_LIVED_TOKEN"}))
        print(await ws.recv())  # Auth response

        # Send the query
        await ws.send(json.dumps({
            "id": 1,
            "type": "timescale/query",
            "sensor_id": "sensor.temperature_woonkamer",
            "start": "2026-01-01T00:00:00Z",
            "end": "2026-01-01T12:00:00Z",
            "limit": 1000,
            "downsample": 0
        }))
        # Receive the result
        while True:
            msg = await ws.recv()
            print(msg)
            if 'result' in msg or 'error' in msg:
                break

asyncio.run(query_timescale())

Replace YOUR_LONG_LIVED_TOKEN with your Home Assistant long-lived access token. The response will contain the queried data as JSON.

Visualization: Plotly Card

A special Home Assistant card has been developed to work with this integration: timescale-plotly-card. This allows you to easily create charts from your TimescaleDB data in the Home Assistant dashboard.

Installation

1. Copy this repository to your Home Assistant custom_components directory:

   custom_components/timescale_database_reader/
   

2. Restart Home Assistant.

Configuration

In Home Assistant, go to Settings > Integrations > Add Integration and search for Timescale Database Reader. Enter your database host, port, username, password, and database name.

Issues & Contributions

Problems or want to contribute? Open an issue or pull request on GitHub.

---

©2026 Bommer Software | Author: Mischa Bommer

Note: This integration is a work in progress. Features and functionality may change or be incomplete.

Full Changelog: v1.0.1...v1.0.3

v1.0.3

Timescale Database Reader

A powerful Home Assistant integration for reading data from any TimescaleDB database.

Universal TimescaleDB Support

This integration works with any TimescaleDB (PostgreSQL) database, as long as you provide the correct connection details (host, port, username, password, database). You do not need a special Home Assistant database.

Tested with LTSS and Scribe

This integration has been tested with a TimescaleDB database filled with values by the LTSS integration. Note: LTSS is no longer maintained. Currently, testing is ongoing with the Home Assistant Scribe integration as an alternative for storing long-term statistics in TimescaleDB.

Example Query

You can use the WebSocket API or a Home Assistant service to run a query. For example, to fetch the temperature of a sensor:

SELECT time, state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
ORDER BY time ASC;

The integration also supports downsampling with time_bucket for efficient charting:

SELECT time_bucket('5 minutes', time) AS bucket,
       avg(state::double precision) AS avg_state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
GROUP BY bucket
ORDER BY bucket ASC;

Example: Query via WebSocket API

You can use the Home Assistant WebSocket API to query TimescaleDB data directly. Here is an example using Python and the websockets library:

import asyncio
import websockets
import json

async def query_timescale():
    uri = "ws://homeassistant.local:8123/api/websocket"  # Change to your Home Assistant URL
    async with websockets.connect(uri) as ws:
        # Authenticate (replace with your long-lived access token)
        await ws.send(json.dumps({"type": "auth", "access_token": "YOUR_LONG_LIVED_TOKEN"}))
        print(await ws.recv())  # Auth response

        # Send the query
        await ws.send(json.dumps({
            "id": 1,
            "type": "timescale/query",
            "sensor_id": "sensor.temperature_woonkamer",
            "start": "2026-01-01T00:00:00Z",
            "end": "2026-01-01T12:00:00Z",
            "limit": 1000,
            "downsample": 0
        }))
        # Receive the result
        while True:
            msg = await ws.recv()
            print(msg)
            if 'result' in msg or 'error' in msg:
                break

asyncio.run(query_timescale())

Replace YOUR_LONG_LIVED_TOKEN with your Home Assistant long-lived access token. The response will contain the queried data as JSON.

Visualization: Plotly Card

A special Home Assistant card has been developed to work with this integration: timescale-plotly-card. This allows you to easily create charts from your TimescaleDB data in the Home Assistant dashboard.

Installation

1. Copy this repository to your Home Assistant custom_components directory:

   custom_components/timescale_database_reader/
   

2. Restart Home Assistant.

Configuration

In Home Assistant, go to Settings > Integrations > Add Integration and search for Timescale Database Reader. Enter your database host, port, username, password, and database name.

Issues & Contributions

Problems or want to contribute? Open an issue or pull request on GitHub.

---

©2026 Bommer Software | Author: Mischa Bommer

Note: This integration is a work in progress. Features and functionality may change or be incomplete.

Full Changelog: v1.0.1...v1.0.3

v1.0.2

Timescale Database Reader

A powerful Home Assistant integration for reading data from any TimescaleDB database.

Universal TimescaleDB Support

This integration works with any TimescaleDB (PostgreSQL) database, as long as you provide the correct connection details (host, port, username, password, database). You do not need a special Home Assistant database.

Tested with LTSS and Scribe

This integration has been tested with a TimescaleDB database filled with values by the LTSS integration. Note: LTSS is no longer maintained. Currently, testing is ongoing with the Home Assistant Scribe integration as an alternative for storing long-term statistics in TimescaleDB.

Example Query

You can use the WebSocket API or a Home Assistant service to run a query. For example, to fetch the temperature of a sensor:

SELECT time, state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
ORDER BY time ASC;

The integration also supports downsampling with time_bucket for efficient charting:

SELECT time_bucket('5 minutes', time) AS bucket,
       avg(state::double precision) AS avg_state
FROM ltss
WHERE entity_id = 'sensor.temperature_woonkamer'
  AND time BETWEEN '2026-01-01T00:00:00Z' AND '2026-01-01T12:00:00Z'
GROUP BY bucket
ORDER BY bucket ASC;

Example: Query via WebSocket API

You can use the Home Assistant WebSocket API to query TimescaleDB data directly. Here is an example using Python and the websockets library:

import asyncio
import websockets
import json

async def query_timescale():
    uri = "ws://homeassistant.local:8123/api/websocket"  # Change to your Home Assistant URL
    async with websockets.connect(uri) as ws:
        # Authenticate (replace with your long-lived access token)
        await ws.send(json.dumps({"type": "auth", "access_token": "YOUR_LONG_LIVED_TOKEN"}))
        print(await ws.recv())  # Auth response

        # Send the query
        await ws.send(json.dumps({
            "id": 1,
            "type": "timescale/query",
            "sensor_id": "sensor.temperature_woonkamer",
            "start": "2026-01-01T00:00:00Z",
            "end": "2026-01-01T12:00:00Z",
            "limit": 1000,
            "downsample": 0
        }))
        # Receive the result
        while True:
            msg = await ws.recv()
            print(msg)
            if 'result' in msg or 'error' in msg:
                break

asyncio.run(query_timescale())

Replace YOUR_LONG_LIVED_TOKEN with your Home Assistant long-lived access token. The response will contain the queried data as JSON.

Visualization: Plotly Card

A special Home Assistant card has been developed to work with this integration: timescale-plotly-card. This allows you to easily create charts from your TimescaleDB data in the Home Assistant dashboard.

Installation

1. Copy this repository to your Home Assistant custom_components directory:

   custom_components/timescale_database_reader/
   

2. Restart Home Assistant.

Configuration

In Home Assistant, go to Settings > Integrations > Add Integration and search for Timescale Database Reader. Enter your database host, port, username, password, and database name.

Issues & Contributions

Problems or want to contribute? Open an issue or pull request on GitHub.

---

©2026 Bommer Software | Author: Mischa Bommer

Note: This integration is a work in progress. Features and functionality may change or be incomplete.