The magic of SQL

tableI am still working on my Temperature Logging project with the RaspberryPi mentioned here. The Raspberry is collecting the data and stores it in a MySQL database. Each hour, it does 5 measurements. My plan is to visualize these measurements in a little program I want to write with Lazarus. Lazarus got the TAChart component and it does exactly what I want.

So I started to crack my head on how I want to visualize the data. Preferrably one graph per day, switchable between humidity or temperature. On the left is a part of the database table I am talking about.

My first thought was, to just extract the data hour by hour, calculate an average and display the value in the chart.

With this statement I would get the data hour by hour. Adding some code around and I would already get what I am looking for. But stop! Maybe the database can do all the work for me.

I started to consult the MySQL documentation and found the AVG() function. It can give me an average of my results and thats it!

So that’s how it looks:

But how about getting that output for a whole day? In that case all the work is done by the SQL server and I can just pass the result to the Chart components. Some deeper look into the MySQL documentation and I found what  I was looking for. I had to group my output by the hour, but had no idea how. Here comes the DATE_FORMAT() function handy. It works with placeholders for the different parts of date, like in so many other programming languages.  I am using it in my statement thrice. Once to reformat the date output to our ownly, for the GROUP BY and for the WHERE clause to select a certain day:

What I also found a bit annoying was the amount of decimal places. With the FORMAT() function I am reducing it to two and the output is automatically rounded:

With that I am exactly where I want! That has proven again for me, that its worth to spend some effort in the way how you extract your data from a database. I would also be possible to create a view from that and with a simple select with the date, it elegantly provides only the average data…

Time to complete the Lazarus program.

Leave a Reply