Qball’s Weblog

If you want to plot something under linux from the commandline,  gnuplot is almost always a good and safe choice.
So we want to plot the table from the sqlite database into a nice graph. We want todo this in a nice way, so no intermediate files.

If we look at sqlite3 output it looks something like this:

1
2
3
4

sqlite3 temperature.sql "select * from temperature where sensor=0"
1345395460|0|26.75
1345395492|0|26.6875
1345395523|0|26.75

So to note:

1. Time in seconds since 1-1-1970.
2. We want to plot the first and 3rd column
3. Field separator is |

To plot the data first, open gnuplot:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

$ gnuplot
 
        G N U P L O T
        Version 4.4 patchlevel 3
        last modified March 2011
        System: Linux 3.1.1-29-linaro-lt-omap
 
        Copyright (C) 1986-1993, 1998, 2004, 2007-2010
        Thomas Williams, Colin Kelley and many others
 
        gnuplot home:     http://www.gnuplot.info
        faq, bugs, etc:   type "help seeking-assistance"
        immediate help:   type "help"
        plot window:      hit 'h'
 
Terminal type set to 'wxt'
gnuplot>

First we want to set the field separator:

set datafile separator "|"

Then we want to set the format of the time column (as you can see this matches what we used for date in the previous post):

set timefmt "%s"

Set the X axes to show time.

set xdata time

Set the printed format to HH:MM:SS

set format x "%H:%M:%S"

Now plot the graph:

 plot "< sqlite3 ./temperature.sql 'select * from temperature where sensor=0'" using 1:3 with lines

This will give you a nice plot, but it is not perfect.

• A grid would be nice
• Time is off, for me there was a 2 hour offset, because of timezone.
• The graph is scaled between min temp and max temp, would be nicer between 0 and 50.
• Some nice axes titles

So lets fix them in reverse order:

set grid

The axes label:

set xlabel "Time (HH:MM:SS)
set ylabel "Temperature (Celcius)"

The scaling:

set yrange [0:50]

The time offset (for me it is 2 hours):

plot "< sqlite3 ./temperature.sql 'select * from temperature where sensor=0'" using ($1+60*60*2):3 with lines title "Room"

And if everything was right, you should see something like:

Full Script:

set term post eps
set output 'temperature.eps'
set datafile separator "|"
set xdata time
set timefmt "%s"
set format x "%H:%M:%S"
set yrange [0:40]
set xlabel "Time (HH:MM:SS)
set ylabel "Temperature (Celcius)"
set grid
plot "< sqlite3 ./temperature.sql 'select * from temperature where sensor=0'" using ($1+3600):3 with lines title "room"