Small script to copy sensor data from InfluxDB to Home Assistant's SQLite DB

copy_influxdb_to_homeassistant_db.sh

#!/bin/sh

TMPFILE="$(mktemp)"
read -p "InfluxDB password: " INFLUXDB_PASSWORD
QUERY='SELECT mean("value"), min("value"), max("value") FROM "infinite"."°C" WHERE ("friendly_name" =~ /Temperatur$/) AND time < '"'2022-11-03T00:00:00Z'"' GROUP BY time(1h), "entity_id" fill(none);'
HA_DB="$1"

influx -precision rfc3339 -username admin -password "$INFLUXDB_PASSWORD" -database home_assistant -format csv -execute "$QUERY" \
    | sed \
        -e 's/^°C,entity_id=/sensor./' \
        -e '/^name,/d' \
    > "$TMPFILE"

sqlite3 "$HA_DB" <<EOF
CREATE TABLE IF NOT EXISTS "statistics_new" (
    name TEXT,
    start DATETIME,
    mean FLOAT,
    min FLOAT,
    max FLOAT
);
.import --csv $TMPFILE statistics_new
INSERT INTO statistics (created, metadata_id, start, mean, min, max) SELECT
    datetime(start, '+1 hour') as created,
    statistics_meta.id as metadata_id,
    start,
    mean,
    min,
    max
FROM statistics_new
INNER JOIN statistics_meta ON (statistics_meta.statistic_id = statistics_new.name);
DROP TABLE statistics_new;
EOF
rm "$TMPFILE"