Can I plot directly time-series dataset with gnuplot from sqlite database?
I have an sqlite db, I would like to plot time series data. I created the query. Really simple it's working. This is the sqlite3 query, and sending to a file:
$ sqlite3 ./home_temp.db "SELECT datetime(datetime_int,'unixepoch'), ROUND(temperature, 2) FROM bme280_data ORDER BY datetime_int DESC LIMIT 288;" >> home_temp.dat
This is few line from the home_temp.dat file:
2020-10-12 13:20:01|21.02
2020-10-12 13:15:02|20.95
2020-10-12 13:10:01|20.89
2020-10-12 13:05:01|20.8
2020-10-12 13:00:01|20.74
2020-10-12 12:55:02|20.87
I can read this with gnuplot, and plot it. That's ok.
#! /usr/bin/env gnuplot
set terminal dumb 150 25
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set format x "%H:%M"
set xtics rotate by -90
set datafile separator "|"
plot "<(sqlite3 ./home_temp.db 'SELECT datetime(datetime_int,'unixepoch'), ROUND(temperature, 2) FROM bme280_data ORDER BY datetime_int DESC LIMIT 288;')" using 1:2 with lines
plot './home_temp.dat' using 1:2 with lines
pause -1
Now, when I change this the gnuplot file, to read from sqlitedb, not from file. It's not working anymore. Anybody has any idea why?
#! /usr/bin/env gnuplot
set terminal dumb 150 25
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set format x "%H:%M"
set xtics rotate by -90
set datafile separator "|"
SqliteField(f) = '< sqlite3 ./home_temp.db "SELECT '.f.' FROM bme280_data ORDER BY datetime_int DESC LIMIT 288;"'
fields = "datetime(datetime_int,'unixepoch'), ROUND(temperature, 2)"
plot for [f in fields] SqliteField(f) using 1:2 with lines
Error message: x range is invalid
What is the problem?
There seems to be a misunderstanding in your code about the use of '[f in fields]'. In the "plot for" loop of your code, the loop body is executed three times, with the following values sequentially assigned to 'f'.
datetime(datetime_int,'unixepoch’)
ROUND(temperature
2)
This is because the string stored in fields is splitted by whitespace character and passed to f sequentially.
If you don't use the loop and simply evaluate "SqliteField(fields)", I think you will get the result you want.
In the definition of SqliteField(f), you don't need the trailing double quotation mark. You can check the result string of SqliteField(fields) by simply printing it to STDOUT,
print SqliteField(fields)
Your code would work if it were modified as follows.
#! /usr/bin/env gnuplot
set terminal dumb 150 25
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set format x "%H:%M"
set xtics rotate by -90
set datafile separator "|"
SqliteField(f) = '< sqlite3 ./home_temp.db "SELECT '.f.' FROM bme280_data ORDER BY datetime_int DESC LIMIT 288;'
fields = "datetime(datetime_int,'unixepoch'), ROUND(temperature, 2)"
plot SqliteField(fields) using 1:2 with lines