I found it nonintuitive, but the docs for libdbi, REF: http://libdbi-drivers.sourceforge.net/docs/dbd_mysql.pdf Under 4.1 MySQL (mis)features, "DATETIME, TIMESTAMP, DATE and TIME are all treated as the DBI type DATETIME. This is currently a string, but will change in later releases."
On Wed, 2014-04-02 at 22:54 -0700, John Stile wrote: > 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 > > > [email protected] > > > > > > > > > > > > _______________________________________________ > > rrd-users mailing list > > [email protected] > > https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users > > > _______________________________________________ > rrd-users mailing list > [email protected] > https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users _______________________________________________ rrd-users mailing list [email protected] https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
