> For the complete documentation index, see [llms.txt](https://docs.umh.app/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.umh.app/usage/historian/querying.md). # Query the Historian Once a [Historian bridge](/usage/historian/save-to-historian.md) is storing a contract, the data is ordinary TimescaleDB, and any SQL client or Grafana can read it. The fastest route is to copy a query out of the topic browser; the schema further down is for when you write your own. The Management Console never runs these queries. It generates the SQL and you paste it into Grafana or psql. ## Copy a query from the topic browser 1. Open the **Topic Browser** and select a tag. 2. Choose the **Grafana** or **TimescaleDB** tab. The two differ only in the time filter. Grafana emits `$__timeFilter(ts)`, which the dashboard's time picker fills in. TimescaleDB emits a fixed window sized to the resolution, so the query returns roughly 60 points when pasted straight into psql. 3. Adjust the controls: * **Aggregate** buckets the data with `time_bucket()` and returns `avg`, `min`, and `max`. Turn it off for the raw rows. Text tags cannot be aggregated, so the toggle is disabled for them and the query always returns raw values. * **Resolution** sets the bucket width: `$__interval`, `1 second`, `10 seconds`, `1 minute`, `1 hour`, or `raw`. Grafana starts at `$__interval`, which lets Grafana pick a width from the panel's time range and width; TimescaleDB has no such macro and starts at `1 minute`. 4. Copy it. The panel is generated from the topic name, not from the database, so it appears for every tag, including ones no Historian bridge has stored yet. The panel says as much. Those queries are valid and return no rows. When the tag's datatype isn't known yet, the query defaults to the numeric column. Switch it to `value_text` if the tag holds strings. ## Schema Values are stored per contract; identity is shared across contracts. ```mermaid erDiagram "umh.value_pump" }o--|| "umh.topic" : topic_id "umh.attribute_pump" }o--|| "umh.topic" : topic_id "umh.topic" }o--|| "umh.tag" : tag_id "umh.topic" }o--|| "umh.location" : location_id "umh.value_pump" { bigint topic_id PK timestamptz ts PK double value_num text value_text } "umh.attribute_pump" { bigint topic_id PK timestamptz ts PK jsonb attribute } "umh.topic" { bigserial topic_id PK bigint location_id FK bigint tag_id FK } "umh.tag" { bigserial tag_id PK text name text virtual_path text data_contract_name value_type value_type } "umh.location" { bigserial location_id PK ltree path } ``` `umh.value_pump` and `umh.attribute_pump` are the per-contract tables, named after `data_contract_name`; the three dimension tables are shared. `attribute` is a JSON object, queryable with `attribute->>'key'` and `attribute @> '{...}'`. `value_type` on `umh.tag` records whether a tag is numeric or text. It is set on first write and cannot change afterwards, which is why a tag that flips datatype is dropped rather than stored. ### Resolving a tag `umh.get_topic_id(location_path, virtual_path, data_contract, tag_name)` hides that join for single-tag lookups. It is what the generated queries use: ```sql SELECT ts, value_num FROM umh.value_pump WHERE topic_id = umh.get_topic_id('enterprise.site.area.line', '', 'pump', 'temperature') AND ts BETWEEN now() - INTERVAL '1 hour' AND now() ORDER BY ts; ``` Three things trip up hand-written queries: * The timestamp column is **`ts`**, a `timestamptz`, not `timestamp` or `time`. * A tag with no virtual path stores `virtual_path` as the **empty string**, never `NULL`. Passing `NULL` matches nothing and returns an empty result with no error. * The `data_contract` argument is forgiving: `pump`, `_pump`, and `_pump_v1` all resolve to the same tag. Location paths are canonicalized into an `ltree`: characters outside `[A-Za-z0-9_-]` become `_`. Hyphens survive, so `line-1` and `line_1` are **different** locations with different `topic_id`s. ### Latest value of every tag ```sql SELECT DISTINCT ON (v.topic_id) l.path::text AS location, g.virtual_path, g.name AS tag, v.ts, v.value_num, v.value_text FROM umh.value_pump v JOIN umh.topic t ON t.topic_id = v.topic_id JOIN umh.tag g ON g.tag_id = t.tag_id JOIN umh.location l ON l.location_id = t.location_id ORDER BY v.topic_id, v.ts DESC; ``` This scans each topic's history to find its newest row, which is fine for hundreds of tags. For a dashboard that refreshes often, back the query with a continuous aggregate holding `last(value_num, ts)` per `topic_id` and read that instead. ## Using it from Grafana Add the database as a PostgreSQL data source, paste the Grafana-flavored query into a panel, and the dashboard's time picker drives `$__timeFilter(ts)`. If you don't have Grafana yet, [Grafana](/production/deployment/docker-compose/additional-services/grafana.md) covers adding it to a running stack. Point the data source at PgBouncer rather than TimescaleDB directly if your deployment has one. ## Precision `value_num` is `DOUBLE PRECISION`, a binary floating-point type. It stores an approximation of the value, which is close enough for sensor readings but wrong for anything that has to come back byte-for-byte: integer counters above 2^53 lose their low digits, and a decimal such as `0.1` is kept as the nearest binary fraction. Route those tags to a text contract, where the value is stored verbatim in `value_text`. The [Historian output reference](https://docs.umh.app/benthos-umh/output/historian) covers the rest of what the output plugin does: metrics, error classes, throughput tuning, and schema compatibility. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.umh.app/usage/historian/querying.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.