timeseries data plot from sqlite database with gnuplot

0
votes

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?

1
bashsqliteplottime-seriesgnuplot

1 Answers

1
votes

First Point:

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.

Second Point:

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)

Summary

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