REXYGEN Community Forum / DbDrv driver - How to handle a MySQL TIMESTAMP

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-08T14:37:58Z

Yes we missed this problem when releasing version 2.10.7. If possible, use the documentation available from our server to use the most up-to-date revision.

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-07T11:58:49Z

jaroslav_sobota wrote:

Yes, see the DbDrv manual from REX Control System documentation, page 12. I'll improve the documentation, thanks for the question!

My version of this document contained one single page which was not translated yet....yep, page 12...
Time to get revision 2.....

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-07T08:25:16Z

scoobsalamander wrote:

Aha, I see....is this documented somewhere?

Yes, see the DbDrv manual from REX Control System documentation, page 12. I'll improve the documentation, thanks for the question!

Are there more data types which can be configured?

Another prefix is "s" for strings but there is no way to use it in the algorithm so I did not mention it.

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-06T23:15:32Z

Aha, I see....is this documented somewhere? Are there more data types which can be configured?

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-06T12:10:20Z

The input flags of the database driver (DbDrv) starting with "i" are processed as long type, initial "b" denotes a bool type. This special implementation is case-sensitive. Either rename your flag to "Barometric" or "pressure" and it will work as expected. Btw that's the reason why I named the flags iday, imonth, etc.

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-06T09:37:50Z

...everything was working fine but now I noticed that for the barometric pressure reading I get the value 'on'. All the other values are correct and if I try the query in phpMyAdmin I get the correct values from the database....

Any idea why this is not correct?

ODBC { 
  Connection "DRIVER=MySQL;SERVER=192.168.178.17;PORT=3306;DATABASE=REXcontrols;UID=REX;PWD=agusta;" 
 
  Group {
	Mode 3
	SQL "SELECT temp_C, dewpoint_C, rel_Humidity, precip_1hr, pressure, precip_today, DATE_FORMAT(observe_epoch,'%Y'), DATE_FORMAT(observe_epoch,'%c'), DATE_FORMAT(observe_epoch,'%e'), DATE_FORMAT(observe_epoch,'%k'), DATE_FORMAT(observe_epoch,'%i'), DATE_FORMAT(observe_epoch,'%s'), NOW()-observe_epoch, UNIX_TIMESTAMP(observe_epoch)-UNIX_TIMESTAMP('2000-01-01 00:00:00') FROM Weather_current_data ORDER BY observe_epoch DESC LIMIT 1" 
    Period 60 
	Items "OAT,DewPoint,Humidity,precip_1hr,barometric,precip_today,iyear,imonth,iday,ihour,imin,isec,ifresh,irextime"
	}
}

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-05T16:18:23Z

Thx, it's working...

Re: DbDrv driver - How to handle a MySQL TIMESTAMP

2016-01-04T23:17:08Z

Assuming a table mydata with two columns time and value, the following definition in .rio file works for me:

ODBC { 
  Connection "DRIVER=MySQL;SERVER=127.0.0.1;PORT=3306;DATABASE=dbname;UID=dbuser;PWD=dbpassword;" 
  Group { 
    Mode 3 
    SQL "SELECT value, DATE_FORMAT(time,'%Y'), DATE_FORMAT(time,'%c'), DATE_FORMAT(time,'%e'), DATE_FORMAT(time,'%k'), DATE_FORMAT(time,'%i'), DATE_FORMAT(time,'%s'), NOW()-time, UNIX_TIMESTAMP(time)-UNIX_TIMESTAMP('2000-01-01 00:00:00') FROM `mydata` ORDER BY `time` DESC LIMIT 1" 
    Period 5 
    Items "temperature,iyear,imonth,iday,ihour,imin,isec,fresh,irextime" 
  } 
}

You can access the values in the algorithm using the following input flags:

DB__temperature (type double)
DB__iyear (type long)
DB__imonth (type long)
DB__iday (type long)
DB__ihour (type long)
DB__imin (type long)
DB__isec (type long)
DB__fresh (type double, tells you how long it has been since the value was updated, in seconds)
DB__irextime (type long, allows you to compare time with the tsec output of the DATETIME function block)

See date and time functions in MySQL for detailed information, namely the formatting options of the DATE_FORMAT() function.

DbDrv driver - How to handle a MySQL TIMESTAMP

2015-12-30T23:01:14Z

I'm trying to read a value(temperature) from a MySQL database with corresponding timestamp.
Everything is working but I only don't know how to handle the timestamp...now it is been seen as a double and the value is equal to the year 2015. All the rest is missing (month, day, time).

Is there a way how I can compare this timestamp with the actual time of the REXcore?