{"id":2302,"date":"2021-09-18T22:57:01","date_gmt":"2021-09-19T02:57:01","guid":{"rendered":"http:\/\/matthannan.net\/blog\/?p=2302"},"modified":"2021-09-18T23:47:10","modified_gmt":"2021-09-19T03:47:10","slug":"modernizing-my-temperature-and-humidity-sensor","status":"publish","type":"post","link":"https:\/\/matthannan.net\/blog\/modernizing-my-temperature-and-humidity-sensor\/","title":{"rendered":"Modernizing My Temperature and Humidity Sensor"},"content":{"rendered":"\n

My basement temperature and humidity sensor<\/a> has been up and running for 2.5 years now with hardly a hiccup at all. But, it is time for an upgrade.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

I’ve made some tweaks to the page over the years, but I ‘ve grown less and less happy with the matplotlib implementation. You can see on the X axes that they do not actually reflect the timestamp, but rather the record number. I’ve gone after this so many times, but no luck. Also, I’d like to be able to crunch some of those numbers.<\/p>\n\n\n\n

In my research of how to modernize this, I discovered Grafana<\/a>, which reads databases and makes pretty graphs out of the data. This does not natively read sqlite3<\/a> databases, but it can be made to<\/a>. It isn’t easy or nice, however, due to how sqlite3 works with timestamps. Basically, it doesn’t. It deals with string-formatted timestamps, but Grafana, for all its power, can’t be arsed to figure out what I have seen in many, many json files:
YYYY-MM-DD hh:mm:ss<\/strong>
It relies on either straight UNIX time<\/a> (I guess for speed, as only a machine can figure out what that means), or something called RF3339 format<\/a>, which looks like this:
2008-04-02T20:00:00Z<\/strong>
Yeah, that is an actual thing. Somehow, you can get time zones involved with that mess, too, but, seriously, this time (no pun) it is me who couldn’t be arsed to figure it out.<\/p>\n\n\n\n

What it boils down to is that I have 2.5 years of data trapped in a database that I will not be able to use in Grafana, unless I figure out one of a couple of not-so-easy routes forward. At first, I was thinking of migrating the data to MySQL<\/a> (or MariaDB<\/a> for the Mods), but then I figured that if I was going to start messing with actual SQL databases on small Linux devices, I might as well look at something useful: PostgreSQL<\/a>. So, I set about installing PostgreSQL and Grafana on the target Pi, which is vintage 3B+. Grafana works, but it is not great. And, it has something of a learning curve to it. As for Postgres… Yeah, that was pretty much a non-starter. I’d still like to learn it, but not while I am working on this issue.<\/p>\n\n\n\n

While learning how to use Grafana, I was introduced to another database, InfluxDB<\/a>, which has great Grafana support, and is a time series database, which means something. So, I installed InfluxDB on the Pi and set out to learn how to make this work. Turns out, it is very different from SQL databases. It uses something called Series instead of Tables. This database write section is copied directly out of my test Python script:<\/p>\n\n\n\n

#InfluxDB Connection Details\ninfluxHost = 'localhost'\ninfluxPort = '8086'\ninfluxUser = 'grafana'\ninfluxPasswd = 'none_of_your_business'\ninfluxdbName = 'home'\n\ninflux_metric = [{\n    'measurement': 'TemperatureSensor',\n    'time': datetime.datetime.utcnow(),\n    'fields': {\n        'temperature': temperature,\n        'humidity': humidity,\n        'location': \"basement\"\n    }\n}]\n\n#Saving data to InfluxDB\ntry:\n    db = influxdb.InfluxDBClient(influxHost, influxPort, influxUser, influxPasswd, influxdbName)\n    db.write_points(influx_metric)\nfinally:\n    db.close()<\/code><\/pre>\n\n\n\n
If you are familiar with writing to SQL databases in Python, this is fairly different. But, it is pretty easy to figure out, too. In fact, I ran it a couple of time (that isn’t the whole script…the sensor set up stuff is all missing) for to see what was getting in the database. And hooking it up to Grafana was totes easy.<\/p>\n\n\n\n