[rrd-users] rrd dbi syntx

[John Stile]

Sorry for the trouble.  I had a syntax error, as well as missing the *
in the date. Mysql TIMESTAMP dates required the * to force the unix
timestamp format.

This worked for me:
rrdtool graph /home/jstile/foo.png DEF:cpu0=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0/:avg:AVERAGE:step=600 AREA:user#66ff00:"cpu0"

thank you for your help.

On Wed, 2014-04-02 at 21:03 -0700, John Stile wrote:
> I did try both.
> rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600  DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0:min:AVERAGE
> rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600  DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE
> Both have the same error: ERROR: formatstring wrong - mysql
> The error doesn't really tell me what is wrong.
> 
> Is is possible to modify the select called by rrdtool to force a UNIX_TIMESTAMP?
> 
> e.g. If I could have rrdtool performt this select, data will be correct.
> 
> SELECT UNIX_TIMESTAMP(date),cpu0 FROM temperature
> 
> Output looks like:
> 1394007172
> 41
> 1394007183
> 43
> 1394007243
> 41
> 1394007303
> 42
> 
> On Mon, 2014-03-31 at 21:19 +0000, Steve Shipway wrote:
> > >rrdtool graph 
> > >./test.png --imgformat=PNG --start=-1day --end=+0hours --width=1000 --height=600
> > >"DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE" 
> > >"LINE1:min#FF0000:cpu0"
> > >
> > >Error:
> > >ERROR: formatstring wrong - mysql
> > 
> > Second attempt at a solution, since the mysql drivers are confirmed to be 
> > present!
> > 
> > See the documentation here 
> > http://oss.oetiker.ch/rrdtool/doc/rrdgraph_libdbi.en.html
> > 
> > Your DEF specifies that the table to use is 'temperature', with the time 
> > coming from field 'date' and the value from field 'cpu0'.
> > 
> > The 'date' field needs to be a UNIX timestamp, not a datetime field type. 
> > Prefix it with a '*' if it is a datetime field -- this requires RRDTool 
> > 1.4.something I believe.
> > 
> > You've not given a table definition for your 'temperature' table, but I 
> > suspect 'date' is a datetime rather than a UNIX timestamp.  Try this instead:
> > 
> > DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0:min:AVERAGE
> > 
> > ... and this should do the necessary datetime -> timestamp conversion.
> > 
> > Steve
> > 
> > 
> > Steve Shipway
> > s.shipway at auckland.ac.nz
> > 
> > 
> 
> 
> _______________________________________________
> rrd-users mailing list
> rrd-users at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users