Gnuplot with data from sqlite3 (using multiple columns)

Question

i got the following problem: I habe a sqlite3 database with the following columns: ID, tempin, tempout, humidity, time, date

Now i want to plot all the content into a nice plot using gnuplot. I searched a little and found the following code:

plot '< sqlite3 myfile.db3 "SELECT temp1, temp2, pressure, humidity FROM myTable;"' using 0:1 title 'temp1', \
     '' using 0:2 title 'temp2'

But after editing it, the gnuplot said "Warning: Skipping data file with no valid points"

My database looks like:

ID    tempin      tempout     humidity     date         time
1     24          12          55           01.11.2014   07:00
2     23          13          54           01.11.2014   07:15
3     25          13          45           01.11.2014   07:30

Does anyone have an idea why i cant plot the database into gnuplot? And furthermore i want to use the column date or time for x scale.

Answer 1

Your code has two problems:

1. the column names in the gnuplot code do not match those in the database
2. sqlite3 by default uses | as delimiter between data columns, but gnuplot by default expects whitespace

To reproduce your problem, I typed into sqlite3

create table myTable ( id int, tempin int, tempout int, humidity int, date date, time time );
insert into myTable values ( 1, 24, 12, 55, "01.11.2014", "07:00" );
insert into myTable values ( 2, 23, 13, 54, "01.11.2014", "07:15" );
insert into myTable values ( 3, 25, 13, 45, "01.11.2014", "07:30" );

and then tried your query

select temp1, temp2, pressure, humidity from myTable;

and got

Error: no such column: temp1

Then I tried

select * from myTable;

and got

1|24|12|55|01.11.2014|07:00
2|23|13|54|01.11.2014|07:15
3|25|13|45|01.11.2014|07:30

Hence, my working gnuplot code suggestion for your database is

set datafile separator '|'
plot '< sqlite3 myfile.db3 "SELECT tempin, tempout FROM myTable;"' using 0:1 title 'temp1', \
 '' using 0:2 title 'temp2'

Answer 2

Directly behind the word plot, a source, e.g. a filename is needed expected, not the data itself. However, you can use the special filename "-" and gnuplot will ask you to enter a table manually. It looks like this:

gnuplot> plot "-" u 1:2 w l
input data ('e' ends) > 
ID    tempin      tempout     humidity     date        time
1     24          12          55           01.11.2014   07:00
2     23          13          54           01.11.2014   07:15
3     25          13          45           01.11.2014   07:30
input data ('e' ends) > e

Now, it depends on the way you call gnuplot. From a commandline, you can call

gnuplot -e "plot '-' u 1:2 w l" <  sqlite3 myfile.db3 ...

I myself are not a friend of passing data directly to gnuplot. I prefer to write it to a file, first:

sqlite3 myfile.db3 ... > myDataFile

gnuplot -e "plot 'myDataFile' u 1:2 w l" 

Even then, I prefer to write a gnuplot script containing all commands for gnuplot. As the number of settings increases, the single command line becomes longer and less readable.