Skip navigation.

Using gnuPlot to Graph House Temperatures

I have been playing around with building and logging house temperatures for a while now.  Previously I had manually created Excel based graphs, but I knew I wanted to generate some gnuPlot graphs and to possibly have a go at sending the data out onto the internet for graphing as well.  I have used gnuPlot previously to plot other data and found it extremely fast, generating 1600 pixel wide graphs of 160K points of data in way under a second.  Finally I have got around to writing some gnuPlot scripts to graph my house temperatures:

Inside:

gnuPlot of Inside House Temperatures 

 

Outside:

gnuPlot of Outside House Temperatures  

The Python script extracts the temperature data from the networked MySQL DB, dumps it into a text file, then calls a gnuPlot script to build the png based graphs.  The MySQL DB has a view that acts as a temporary table of the last 24 hours worth of temperature readings.  This table is used to extract the min/max temperatures for the period.

 

Here is the Python script:

#!/usr/local/bin/python
#
# this will extract the relevant temperature data from the DB and plot the data via gnuPlot into a png graph

# This has been updated to suit the new MySQL table that hosts one record for all the sensors
#
#  Parkview 2013-04-05
#

import os
import time
import MySQLdb as mdb
import sys
import subprocess
import datetime, time
import ephem # install from pyephem`

Data_Limit=360
MYSQL_HOST = '<mysql_svr_ip>'
MYSQL_DB = "temperature"
MYSQL_USER = "<mysql_user>"
MYSQL_PASSWD = "<mysql_pswd>"
MYSQL_TBL_SENSOR = "sensors_tbl"
MYSQL_TBL_TEMP = "temp_tb"
file="/tmp/gnuplot-data.dat"
max_file="/tmp/gnuplot-data-max.dat"
min_file="/tmp/gnuplot-data-min.dat"
srise_file="/tmp/gnuplot-data-srise.dat"
sset_file="/tmp/gnuplot-data-sset.dat"

# lets work out the previous Sun Set and Sun Rise:
o = ephem.Observer()
o.lat, o.long, o.date = '-33:65', '115:39', datetime.datetime.utcnow()
sun = ephem.Sun(o)
SRISE=ephem.localtime(o.previous_rising(sun))
SSET=ephem.localtime(o.previous_setting(sun))

#  First need to query the DB for a list of sensor names and compare with what we actually have on the network
try:
    con = mdb.connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB);
    cur = con.cursor()
    # go get around days worth of temperature records
    cur.execute("""SELECT datetime,Sensor1,Sensor2,Sensor3,Sensor4,Sensor5,Sensor6,Sensor7,Sensor8,Sensor9,Sensor10,11,Sensor12,Sensor13,Sensor14,Sensor15,Sensor16,Sensor17,Sensor18,Sensor19,Sensor20,Sensor21,Sensor22,Sensor23 FROM temp_5min_tbl  order by datetime desc limit %s""", Data_Limit)
    f=open(file, 'w')
    for row in cur.fetchall():
       # now lets  add this into a list variable for use
       DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[8]),str(row[9]),str(row[10]),str(row[12]),str(row[16]),str(row[17])
       DATA=','.join(DATA1)+'\n'
       f.write(DATA)
       print  DATA  # could add a strip RH to clear the line feed
    print " "
    f.close()
    # now lets get the min data so it can be plotted
    cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature order by temp asc limit 1;""")
    f=open(min_file, 'w')
    for row in cur.fetchall():
       # now lets  add this into a list variable for use
       DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
       DATA=','.join(DATA1)+'\n'
       f.write(DATA)
    # now lets get the max data so it can be plotted
    cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature order by temp desc limit 1;""")
    f=open(max_file, 'w')
    for row in cur.fetchall():
       # now lets  add this into a list variable for use
       DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
       DATA=','.join(DATA1)+'\n'
       f.write(DATA)
       print  DATA  # could add a strip RH to clear the line feed
    print " "
    # now lets get the Sun Rise data so it can be plotted
    cur.execute("""SELECT datetime,Sensor17 FROM temperature.temp_5min_tbl where datetime > %s order by datetime asc limit 1;""", SRISE)
    f=open(srise_file, 'w')
    for row in cur.fetchall():
       # now lets  add this into a list variable for use
       DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
       DATA=','.join(DATA1)+'\n'
       f.write(DATA)
       print  DATA  # could add a strip RH to clear the line feed
    print " "
    # now lets get the Sun Set data so it can be plotted
    cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature where datetime > %s order by datetime asc limit 1;""", SSET)
    f=open(sset_file, 'w')
    for row in cur.fetchall():
       # now lets  add this into a list variable for use
       DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
       DATA=','.join(DATA1)+'\n'
       f.write(DATA)
       print  DATA  # could add a strip RH to clear the line feed
    print " "
   # print  "Data count: ", cur.count
    f.close()        
except mdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)        
finally: 
    if con:     
      con.close()

# call the various gnuPlot scripts from here:
    try:
      subprocess.Popen('/usr/local/bin/RPi-Temperature/new_gnuplot_temperature_inside.sh',shell = True)  
    except OSError, e:
      print 'OS Error: '
    except subprocess.CalledProcessError, e:
      print cmd_output," - "
    try:
      subprocess.Popen('/usr/local/bin/RPi-Temperature/new_gnuplot_temperature_outside.sh',shell = True)
   except OSError, e:
      print 'OS Error: '
    except subprocess.CalledProcessError, e:
      print cmd_output," - "

#print " Sun Rise: ", SRISE
#print " Sun Set: ", SSET

and here is the Outside gnuPlot script:

 #!/bin/sh
 # this is a GNUPLOT script to take the website download data and create a
 #  graphical overview png file
 #
 # Parkview August 2009

 # Local Variables
 BASEDIR="/usr/local/apache2/htdocs/"
 DATAFILE_IN="/tmp/gnuplot-data.dat"
 MINDATAFILE_IN="/tmp/gnuplot-data-min.dat"
 MAXDATAFILE_IN="/tmp/gnuplot-data-max.dat"
 SRISEDATAFILE_IN="/tmp/gnuplot-data-srise.dat"
 SSETDATAFILE_IN="/tmp/gnuplot-data-sset.dat"
 PNG="$BASEDIR/house_temperature-outside.png"
 MINY=13
 MAXY=41
 MYTICS=2
 COLOR1="#CD2222"
 COLOR2="#CD9F22"
 COLOR3="#2225CD"
 COLOR5="#f24D97"
 COLOR4="#000066"
 COLOR6="#22fDd7"
 COLOR7="#22fD57"
 COLOR8="#225D57"
 COLOR9="#72aDa7"
 COLOR10="#000000"
 COLOR11="#666666"

 # extract the required data from the csv file
 EDATE=`tail -1 $DATAFILE_IN | cut -d"," -f1`
 SDATE=`head -2 $DATAFILE_IN | tail -1 | cut -d"," -f1`

 # Now go and plot the graph
 /usr/local/bin/gnuplot << EOF
 set datafile separator ","
 set style fill solid border -1
 set style line 1 lt 2 lw 1
 set pointsize .5
 set sample 45
 set terminal png size 1024,400
 set terminal png
 set output "$PNG"
 set xlabel "Date - Time"
 set ylabel "Degree C"
 set title "House Temperatures - Outside"
 set xdata time
 set timefmt "%Y-%m-%d %H:%M:%S"
 set format x "%d %h\n%H:%M"
 #set xrange [ "$SDATE":"$EDATE" ]
 set xrange [ "$EDATE":"$SDATE" ]
 set timefmt "%Y-%m-%d %H:%M:%S"
 set yrange [ * : * ]
 #set yrange [ $MINY : $MAXY ]
 set xtics border out scale 2
 set ytics out mirror $MYTICS
 set mxtics 4
 set mytics 2
 set grid x y
 plot "$DATAFILE_IN"  using 1:7 title "Outside-average" smooth bezier, "" using 1:7 title "Outside" with lines lc rgb "$COLOR3", "" using 1:6 title "Attic" with lines lc rgb "$COLOR8","" using 1:3 title "Attic Switch" with lines lc rgb "$COLOR9", "$MAXDATAFILE_IN"  using 1:2 title "Max Temp"  with points pointtype 6 pointsize 2 lc rgb "$COLOR1", "$MINDATAFILE_IN"  using 1:2 title "Min Temp"  with points pointtype 6 pointsize 2 lc rgb "$COLOR4", "$SRISEDATAFILE_IN"  using 1:2 title "Sunrise"  with points pointtype 24 pointsize 1 lc rgb "$COLOR11", "$SSETDATAFILE_IN"  using 1:2 title "Sunset"  with points pointtype 24 pointsize 1 lc rgb "$COLOR10"
EOF

 Just for clarification, here are some sample data for the above data files...

cat /tmp/gnuplot-data.dat

2014-02-23 07:50:08,27.312,25.625,28.312,25.062,23.875,21.375,1015.36
2014-02-23 07:45:08,27.25,25.187,28.375,24.937,23.312,21.125,1015.42
2014-02-23 07:40:08,27.062,24.687,28.375,24.937,22.812,20.875,1015.39
2014-02-23 07:35:07,26.937,24.187,28.437,25.0,22.25,20.562,1015.37
2014-02-23 07:30:07,26.75,23.625,28.5,25.0,21.5,20.187,1015.42

cat /tmp/gnuplot-data-max.dat

2014-02-22 15:20:07,33.812

cat /tmp/gnuplot-data-min.dat

2014-02-23 06:15:08,16.0

cat /tmp/gnuplot-data-srise.dat

2014-02-23 06:00:07,16.125

cat /tmp/gnuplot-data-sset.dat

2014-02-22 19:05:08,27.0

 

Note: these scripts are run on a FreeBSD based server, so will need to be adapted for your OS.  They are being posted as an example for other people to use.