sqlite3 unixepoch problem
nigel.h
I am having a problem with getting gnuplot to correctly handle this
query:-
(16:34:59) Nigel: gnuplot> plot "<sqlite3 /var/wview/archive/wview-
archive.sdb 'select dateTime(dateTime, 'unixepoch'),outTemp from
archive order by dateTime desc limit 100'"
Error: no such column: unixepoch
^
warning: Skipping data file with no valid points
^
x range is invalid
If I don't try and convert the dateTime field from the unixepoch
number to normal date/time, it works fine.
It seems to me that gnuplot isn't correctly parsing that bit of the
sqlite3 query but I don't know how to work around it - any help/advice
would be greatly appreciated.
many thanks
Nigel
Hans-Bernhard Bröker
> Hi folks,
> I am having a problem with getting gnuplot to correctly handle this
> query:-
I don't think that's a correct assessment of the situation. gnuplot
isn't the program mishandling this command.
> (16:34:59) Nigel: gnuplot> plot "<sqlite3 /var/wview/archive/wview-
> archive.sdb 'select dateTime(dateTime, 'unixepoch'),outTemp from
^ ^ ^
> archive order by dateTime desc limit 100'"
Note those quotes. I've never used sqlite3 myself, but even so I'm
reasonably sure that that command will fail as it is, before gnuplot
gets to see the output. Try that command in the shell, outside gnuplot,
to see what happens.
> It seems to me that gnuplot isn't correctly parsing that bit of the
> sqlite3 query
It's sqlite that's not parsing it as you expect it, actually.
nigel.h
wrote:
Hans,
thanks for your reply. I've tried the sql query in the shell, outside
of gnuplot and it works OK, converting the unixepoch number in the
first field of the database to yyyy-mm-dd hh:mm:ss format.
gnuplot is handling all other sqlite queries just fine - it's just
this particular problem with converting the unixepoch number that is
stumping me!
Thanks again,
Nigel
Hans-Bernhard Bröker
> On Jun 20, 1:05 am, Hans-Bernhard Bröker<HBBroe...@t-online.de>
>> Note those quotes. I've never used sqlite3 myself, but even so I'm
>> reasonably sure that that command will fail as it is, before gnuplot
>> gets to see the output. Try that command in the shell, outside gnuplot,
>> to see what happens.
> thanks for your reply. I've tried the sql query in the shell, outside
> of gnuplot and it works OK, converting the unixepoch number in the
> first field of the database to yyyy-mm-dd hh:mm:ss format.
That exact same query? Including _all_ the same quotes?
> gnuplot is handling all other sqlite queries just fine - it's just
> this particular problem with converting the unixepoch number that is
> stumping me!
And this particular problem wouldn't happen to be the only one where you
need another pair of quotes in the middle of a command string that's
already in quotes? The same kind of quotes, too?
Hermann Peifer
> Hans,
> thanks for your reply. I've tried the sql query in the shell, outside
> of gnuplot and it works OK, converting the unixepoch number in the
> first field of the database to yyyy-mm-dd hh:mm:ss format.
>
This works under cygwin:
plot "<(sqlite3 -separator \' \' testdb \
\"select dateTime\(date, \'unixepoch\'\),\
value from test \")" using 1:3 with lines
So you just need enough single and double quotes, plus a good handful of
escape characters, and it will work.
Hermann
nigel.h
Thanks Hermann & Hans,
obviously I must do some more experimentation!
My sqlite3 query, outside of gnuplot, with all the quotes (except of
course those outside of, and including the < ) worked.
So to clarify, this is what works :-
[nigel@HPLaptop ~]$ sqlite3 /var/wview/archive/wview-archive.sdb
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select dateTime(dateTime,'unixepoch','localtime'),outTemp from
archive;
It produced a full list of the data like:-
2010-06-20 11:00:00|81.099998
2010-06-20 11:15:00|81.199997
2010-06-20 11:30:00|81.599998
2010-06-20 11:45:00|82.599998
2010-06-20 12:00:00|82.400002
2010-06-20 12:15:00|82.199997
BUT, I have since read the gnuplot manual again more thoroughly and,
after several experiments, have found that the following, in gnuplot,
produces the required plot:-
gnuplot> set terminal x11
gnuplot> set xdata time
gnuplot> set timefmt "%s" # lower case s reads
unixepoch data
gnuplot> set datafile separator "|"
gnuplot> plot "<sqlite3 /var/wview/archive/wview-archive.sdb 'select
dateTime,outTemp from archive'" using 1:2
Now to get the xspan to what I want !
Thanks for your help - slowly gnuplot is becoming clearer to my old
brain!
What a great program,
regards
Nigel
Hans-Bernhard Bröker
> On Jun 20, 5:39 pm, Hermann Peifer<pei...@gmx.eu> wrote:
>> On 20/06/2010 14:24, nigel.h wrote:
> My sqlite3 query, outside of gnuplot, with all the quotes (except of
> course those outside of, and including the< ) worked.
> So to clarify, this is what works :-
>
> [nigel@HPLaptop ~]$ sqlite3 /var/wview/archive/wview-archive.sdb
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select dateTime(dateTime,'unixepoch','localtime'),outTemp from
> archive;
Well, sorry if I have to sound like a broken record, but that's _not_
the same query. For starters, there was no 'localtime' in the original
one.
But the real trick is that now you've written the command at the sqlite3
shell's prompt, instead of putting the SQL command directly as an
argument to the sqlite3 invocation. The command that gnuplot runs, and
that I'm still sure will, indeed, fail, is exactly the one you gave to
gnuplot's plot command, minus only the leading "< and ternimating ":
sqlite3 /var/wview/archive/wview-archive.sdb 'select dateTime(dateTime,
'unixepoch'),outTemp from archive order by dateTime desc limit 100'
(the above all in one line, entered at the shell prompt). And the
reason it'll fail is because there are 4 quotes in there which you meant
to be two nested pairs, but are actually two non-nested ones.
nigel.h
wrote:
Thanks Hans,
I can see what you mean about the 4 quotes and you are quite right
that it fails when entered as all one line at the shell prompt.
I have tried various combinations of quotes, but cannot find a
solution!
I appreciate your help and I will continue to explore more
possibilities as they come to mind.
cheers
Nigel
Hermann Peifer
>
> gnuplot> set terminal x11
> gnuplot> set xdata time
> gnuplot> set timefmt "%s" # lower case s reads
> unixepoch data
> gnuplot> set datafile separator "|"
> gnuplot> plot "<sqlite3 /var/wview/archive/wview-archive.sdb 'select
> dateTime,outTemp from archive'" using 1:2
>
> Now to get the xspan to what I want !
>
This is how it works for me:
[peifer@whitefish]> sqlite -header test.db "select * from archive"
dateTime|outTemp
1277197812|1
1277197900|2
[peifer@whitefish]> sqlite test.db "select dateTime(dateTime,
'unixepoch', 'localtime'), outTemp from archive"
2010-06-22 11:10:12|1
2010-06-22 11:11:40|2
[peifer@whitefish]> gnuplot
G N U P L O T
Version 4.4 patchlevel 0
last modified March 2010
System: Linux 2.6.26-2-amd64
(...)
gnuplot> set term png
gnuplot> set output "out.png"
gnuplot> set xdata time
gnuplot> set timefmt "%Y-%m-%d %H:%M:%S"
gnuplot> set datafile separator "|"
gnuplot> plot "<(sqlite test.db \" select dateTime(dateTime,
'unixepoch', 'localtime'),outTemp from archive \" )" u 1:2 w l
gnuplot> ^D
[peifer@whitefish]>
Hermann
Hans-Bernhard Bröker
> I have tried various combinations of quotes, but cannot find a
> solution!
That's because quotes alone won't help you. You need to escape some of
the quotes by prefixing them with backslashes, roughly like this:
plot "<sqlite3 /var/wview/archive/wview-archive.sdb 'select
dateTime(dateTime, \'unixepoch\'),outTemp from archive order by dateTime
desc limit 100'"
The key to getting this kind of stuff right is to be aware that your
command will be parsed by multiple tools in sequence: gnuplot, (shell,)
sqlite3, SQL engine.
The quotes needed by each tool need to be "smuggled" through the other
tools' command parsers. That's what escapes are for, typically using
the \ character.
To make matters worse, odds are that each tool uses the same two quote
characters, ' and ", but no two interpretations of quotes are exactly
the same. That's why you need to escape-protect some of the quotes.
And you may need to escape-protect some of the escape characters
themselves, too!