DbDrv driver - How to handle a MySQL TIMESTAMP

scoobsalamander · 2015-12-31 00:01:14

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?

jaroslav_sobota · 2016-01-05 00:17:08

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.

scoobsalamander · 2016-01-05 17:18:23

Thx, it's working...

scoobsalamander · 2016-01-06 10:37:50

...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"
	}
}

screenshot REXdraw

screenshot phpMyAdmin

jaroslav_sobota · 2016-01-06 13:10:20

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.

scoobsalamander · 2016-01-07 00:15:32

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

jaroslav_sobota · 2016-01-07 09:25:16

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.

scoobsalamander · 2016-01-07 12:58:49

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.....

jaroslav_sobota · 2016-01-08 15:37:58

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.

REXYGEN Community Forum

Announcement

REXYGEN forum has been moved. This one is closed.
Head over to the new REXYGEN Community Forum at https://forum.rexygen.com.

Looking forward to meeting you there!

#1 2015-12-31 00:01:14