Using the official docs, I can use the following to get DB Size in MB
SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;
so I wanted to use the following to get in bytes:
SELECT table_schema AS "Database", SUM(data_length + index_length) AS "db_size" FROM information_schema.TABLES WHERE table_schema = 'homeassistant' GROUP BY table_schema;
and then use the following value_template to convert it to B/MB/GB/etc
{% set bytes = 1024 %} {# You can adjust this based on the unit you want to convert from #}
{% set value_in_bytes = value | float %}
{% set kilobytes = value_in_bytes / bytes %}
{% set megabytes = kilobytes / bytes %}
{% set gigabytes = megabytes / bytes %}
{% set terabytes = gigabytes / bytes %}
{% set petabytes = terabytes / bytes %}
{% if petabytes >= 1 %}
{{ '%.2f' | format(petabytes) }} TB
{% elif terabytes >= 1 %}
{{ '%.2f' | format(terabytes) }} GB
{% elif gigabytes >= 1 %}
{{ '%.2f' | format(gigabytes) }} GB
{% elif megabytes >= 1 %}
{{ '%.2f' | format(megabytes) }} MB
{% else %}
{{ '%.2f' | format(kilobytes) }} KB
{% endif %}
but it always says “unavailable” does the SQL sensor not take advanced templating like this, or am i just wrong about it?
When you define a unit of measurement the state value must be numeric, the added “GB”, “MB”, and “KB” being printed by the value template cause the numeric test to fail and the sensor will fail.
So I’m not sure if this is even possible. I tried moving the logic into the SQL query using the following:
SELECT
OriginalValue,
CASE
WHEN ConvertedValue < POWER(1024, 1) THEN CONCAT(ConvertedValue, ' Bytes')
WHEN ConvertedValue < POWER(1024, 2) THEN CONCAT(ROUND(ConvertedValue / POWER(1024, 1), 2), ' KB')
WHEN ConvertedValue < POWER(1024, 3) THEN CONCAT(ROUND(ConvertedValue / POWER(1024, 2), 2), ' MB')
WHEN ConvertedValue < POWER(1024, 4) THEN CONCAT(ROUND(ConvertedValue / POWER(1024, 3), 2), ' GB')
ELSE CONCAT(ROUND(ConvertedValue / POWER(1024, 4), 2), ' TB')
END AS ConvertedSize
FROM (
SELECT
SUM(data_length + index_length) AS OriginalValue,
SUM(data_length + index_length) AS ConvertedValue
FROM information_schema.TABLES
WHERE table_schema = 'homeassistant'
GROUP BY table_schema
) AS Sizes;
This shows up in phpmyadmin as “131.41MB”, but when I enter this into the sql integration, it shows up as “state: unavailable” so it doesn’t seem possible to have it format the value either as a value template, nor bringing is as a value of “XXX.XX B/KB/MB/GB”
