Category: How2

ESP32 DIY Energy Display with ESPHome

In this guide, you’ll learn how to set up an ESP32 board to display sensor data from Home Assistant and connected I²C sensors on a 20×4 LCD display. This setup is perfect for monitoring solar power production, indoor temperatures, CO₂ levels, and more — all in real-time!

Continue reading “ESP32 DIY Energy Display with ESPHome”

InfluxDB Backup Script

A simple script to back up an InfluxDB

Bash
#!/bin/bash

# Configuration
INFLUXDB_HOST="http://localhost:8086"
INFLUXDB_ORG="ORG"
INFLUXDB_BUCKET="BUCKET"
INFLUXDB_TOKEN="my-token"  # Insert the actual token here
BACKUP_DIR="/opt/influxbu"
ROTATION_WEEKS=4

# Date for the backup
TIMESTAMP=$(date +"%Y-%m-%d")
BACKUP_FILE="$BACKUP_DIR/backup-$TIMESTAMP.tar.gz"

# Ensure the backup directory exists
mkdir -p "$BACKUP_DIR"

# Perform the backup
influx backup --host "$INFLUXDB_HOST" --token "$INFLUXDB_TOKEN" --org "$INFLUXDB_ORG" --bucket "$INFLUXDB_BUCKET" "$BACKUP_DIR/backup-$TIMESTAMP"

# Archive the backup
tar -czf "$BACKUP_FILE" -C "$BACKUP_DIR" "backup-$TIMESTAMP"
rm -rf "$BACKUP_DIR/backup-$TIMESTAMP"

# Delete old backups
find "$BACKUP_DIR" -type f -name "backup-*.tar.gz" -mtime +$((ROTATION_WEEKS * 7)) -exec rm {} \;

# Done
echo "Backup completed: $BACKUP_FILE"

Run via cron, eg.

Bash
0 20 * * * /root/influxbu.sh >> /var/log/influxdb_backup.log 2>&1

Home Assistant & InfluxDB & Grafana: Show device positions on map

To display device positions on a map with history, I first have the positions written to an InfluxDB. I display the data from the InfluxDB with Grafana.

The pivot function must be used for this: The pivot() function in InfluxDB (Flux query language) is used to reshape data by converting a long, narrow table into a wide table. It moves values from a column into multiple columns, making it easier to analyse and visualize time series data.

Example in line 6
from(bucket: "hass")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "device_tracker.iphone")
  |> filter(fn: (r) => r["_field"] == "longitude" or r["_field"] == "latitude")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

Result: