buildlog

Logging Pi temperatures to SQLite

pitemp

The server lives in a cupboard with the router and a Raspberry Pi. In July the cupboard felt warm to the touch, and I wanted numbers instead of a feeling.

Project
pitemp
Stack
Python, SQLite, a 1-wire sensor
Time spent
About 6 evenings
Status
alerts pending

didWhat I did

Two readings, once a minute. The Pi's own CPU temperature comes from a file the kernel exposes. Air temperature comes from a cheap 1-wire sensor on a short cable, which also shows up as a file once the overlay is enabled in the boot config.

CPU_PATH = Path("/sys/class/thermal/thermal_zone0/temp")
SENSOR_GLOB = "/sys/bus/w1/devices/28-*/w1_slave"

def read_cpu() -> float:
    return int(CPU_PATH.read_text()) / 1000

A Python script reads both and stores them in SQLite with a timestamp. A second script renders a small SVG chart of the last seven days into a static page, served next to the status page.

The first month of data was the interesting part. The cupboard peaked at 38 °C on a Sunday evening, exactly when the weekly backup prune was running, with the Pi's CPU at 61 °C. Leaving the cupboard door open by two centimetres brought the peak down to 33 °C.

Cupboard air, last 7 days17–23 Aug
Peak 38.1 °C on Sunday at 22:00, during the weekly prune. Amber line: 35 °C.

brokeWhat broke

85 degrees out of nowhere. Every few days a single reading of exactly 85.0 °C appeared. That is the sensor's power-on reset value: it means a conversion didn't finish, not that anything is hot. The sensor file also has a CRC line that ends in YES or NO, which I had been ignoring.

POWER_ON_VALUE = 85_000

def read_air(path: Path) -> float | None:
    crc_line, data_line = path.read_text().splitlines()[:2]
    if not crc_line.endswith("YES"):
        return None
    raw = int(data_line.rsplit("t=", 1)[1])
    return None if raw == POWER_ON_VALUE else raw / 1000

Missing readings. When I moved the sensor to a longer cable, about one reading in ten went missing. The pull-up resistor I had used was too weak for the length. A 4.7 kΩ one fixed it, which is what the datasheet said in the first place.

SD card wear. One write per minute is small, but each one is a separate transaction on an SD card. I switched SQLite to WAL mode and now keep readings in memory, writing them in one batch every ten minutes. Losing up to ten minutes on a power cut is fine for this.

nextWhat's next

  • A threshold check type for the status page: amber above 35 °C in the cupboard, red above 40 °C.
  • A second sensor outside the cupboard, so I can tell a hot room from a hot cupboard.
  • Move the weekly prune to the early morning, when the flat is coolest, and see if the peak moves with it.