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.

select * from log WHERE timestamp >='2013-09-21 21:00' and timestamp <='2013-09-21 22:00';
29	31.5	55.4	2013-09-21 21:00:02.000
30	31.5	55.5	2013-09-21 21:10:02.000
31	31.5	55.8	2013-09-21 21:20:02.000
32	31.4	55.9	2013-09-21 21:30:01.000
33	31.5	55.9	2013-09-21 21:40:02.000
34	31.5	56.0	2013-09-21 21:50:01.000

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!

select AVG(temperature) as Temperature, AVG(humidity) as Humidity from log WHERE timestamp >='2013-09-21 21:00' and timestamp <='2013-09-21 22:00';

So that’s how it looks:

Temperature             Humidity
31.483333269755047	55.750000635782875

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:

SELECT  DATE_FORMAT( timestamp, '%Hh' ),format(avg( temperature ),2) as Temperature,format(avg( humidity ),2) as Humidity
FROM log WHERE DATE_FORMAT( timestamp, '%d.%m.%Y' )='22.09.2013'
GROUP BY DATE_FORMAT( timestamp, '%D.%M.%Y %Hh' );

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:

13h	31.90	60.20
14h	31.50	57.80
15h	31.60	55.90
16h	31.70	53.35
17h	31.75	51.80

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