Plotting sensor data using GnuPlot | Yet another Raspberry Blog …

Data of all sensors attached to my Pi are collected in a MySQL database. There are several ways of generating plots from sensor data such as jgraph. However, for a machine with a CPU frequency of 700 MHz, it is probably not the best idea to generate plots “on-the-fly”. Therefore, I decided to use GNUplot for generating plots of collected data sets at fixed time-intervals in order to present them as PNG images on a static webpage.

All you have to do is to export the desired data set into a CSV file, e.g. data of the past 24 hours and to run GNUplot on it. The MySQL export and GNUplot commands can be placed in a single bash script, which can be run as a cron job. Here is a short tutorial:

Data export: MySQL database to CSV file

In order to work with the data on an other machine it is necessary to export the desired subset from the database. Unfortunately, the INTO OUTFILE statement of MySQL doesn’t allow to choose a remote server for saving the CSV file. Therefore, one has to redirect the MySQL output. For replacing the tab-stops with a different separator, one can pipe the data stream through a stream editor such as ‘sed’:

mysql -h servername -u username --password=secret --skip-column-names -e "SELECT * FROM table where FROM_UNIXTIME(timestamp) >=NOW() - INTERVAL 7 DAY;" | sed 's/\t/;/g;s/\n//g' > week.csv

As a result a CSV file containing a subset of observations from the last 7 days is obtained.

2014-02-22;13:13:00;1393074780;20.3;47.4;21.4;1004.64;71.51;117.9;9.1
2014-02-22;13:14:00;1393074840;20.3;47.5;21.4;1004.73;70.84;119.8;9.1
2014-02-22;13:15:00;1393074900;20.3;47.4;21.4;1004.82;70.93;117.6;8.9
2014-02-22;13:16:00;1393074960;20.3;47.6;21.4;1004.7;71.68;117.8;9 
2014-02-22;13:17:00;1393075020;20.3;47.5;21.4;1004.73;72.18;121.5;9 
2014-02-22;13:18:00;1393075080;20.3;47.5;21.4;1004.69;71.68;126.3;9 
2014-02-22;13:19:00;1393075140;20.3;47.7;21.4;1004.69;72.02;125.6;9.1 
2014-02-22;13:20:00;1393075200;20.3;47.7;21.4;1004.67;70.93;123.4;9.1 
2014-02-22;13:21:00;1393075260;20.3;47.7;21.4;1004.7;71.26;124.4;9.1
.
.
.

Shell script to plot data of a CSV file using GNUplot

The columns in this file consist of date, time, timestamp, tmp1, hum, tmp2, bar, alt, light, tmp3. Now you are ready to plot the data using GNUplot. This can be done with a simple shell script:

#!/bin/bash

# Define title of the plot and print last date of plotting
TITLE="Temperature, Relative Humidity and Pressure until `date +{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}F\ {c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}T`"

# To correct UNIX epoch date and gnuplot epoch date, we have to substract $EPOCH_OFFSET
EPOCH_OFFSET=946684800

# Get UNIX epoch date
TODAY="`date +{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}s`"

# Correct offset to 2000-01-01 00:00:00
TODAY=$(expr $TODAY - $EPOCH_OFFSET)

# We want to plot values from 24 h
YESTERDAY="`date +{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}s --date="-1 day"`"

# Correct offset to 2000-01-01 00:00:00
YESTERDAY=$(expr $YESTERDAY - $EPOCH_OFFSET)

/usr/bin/gnuplot << EOF

# Data file uses semikolon as a separator
set datafile separator ';'

# Title of the plot
set title "$TITLE"

# We want a grid
set grid

# Ignore missing values
#set datafile missing "NaN"

# Functions for dew point,vapour pressure and g/m3
svp(t)=610*exp(t/(t+238.3)*17.2694)
vp(t,rh)=svp(t)*rh/100
w(t,rh)=log(vp(t,rh)/610.78)
dpt(t,rh)=(w(t,rh)*238.3)/(17.294-w(t,rh))
gm3(t,rh)=vp(t,rh)*2.166/(t+273.16)

# X-axis label
set xlabel "Date/Time (CET)"

# set X-axis range to current date only
set xrange ["$YESTERDAY":"$TODAY"]

# Y-axis ranges from 0 deg C to 100 deg C, same scale as for humidity
set yrange [0:80]

# Y2-axis set from 800 to 1200 kPa, used for barometric pressure
set autoscale y2

# place ticks on second Y2-axis
set y2tics border

# Title for Y-axis
set ylabel "Temperature (C) / Relative Humidity ({c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4})"

# Title for Y2-axis
set y2label "Pressure (hPa)"

# Define that data on X-axis should be interpreted as time
set xdata time

# Time in log-file is given in Unix format
set timefmt "{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}s"

# Display notation for time
set format x "{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}d.{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}m\n{c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4}R"    # Display time in 24 hour notation on the X axis

# generate a legend which is placed underneath the plot
set key outside bottom center box title "RasPi Sensor Data"

# output into png file
set terminal png large
set output "/var/www/images/plot.png"

# Data columns are:
# 3 is Unix Epoch time
# 4 is Temperature DHT22
# 5 is Humidity DHT22
# 6 is Temperature BMP085
# 7 is Barometric Pressure
# 8 is Altitude

# read data from file and generate plot
plot "/var/www/data/week.csv" using 3:4 title "Temperature (C)" with lines \
 ,"" using 3:5 title "Relative Humidity ({c7f7cb1468c0d02af358b3ce02b96b7aadc0ce32ccb53258bc8958c0e25c05c4})" with lines \
 ,"" using 3:7 title "Barometric Pressure (hPa)" with lines axes x1y2 \
 ,"" using 3:(dpt(\$6,\$5)) title "Dew Point (C)" with lines\
# ,"" using 3:(gm3(\$6,\$5)) title "Dew Point (C)" with lines\
# end of script
EOF

It is important to note that the timestamp used by GNUplot differs from the UNIX EPOCH time variable which starts at 1970-01-01 00:00:00. For GNUplot it is necessary to correct the offset date to 2000-01-01, otherwise the x-axis of your plots will display wrong labels.

Plots can be generated in different file formats. More results can be visited on my Sensors page.

Update

If you’d like to run the above script on your RasPi server, you can call an SQL query within the GNUplot shell script above. Just place the following line to the top of the script.

sudo mysql -uuser -p password -D database -B -e "SELECT * FROM sensors WHERE FROM_UNIXTIME(timestamp) >= NOW() - INTERVAL 7 DAY INTO OUTFILE '/tmp/week.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'"

Please note that MySQL has permissions to write only into the /tmp folder on Debian Wheezy. Remember that MySQL doesn’t overwrite week.csv if it already exists!