Rounding OPC item values

Archived Topics
1

I was wondering if there was an easier way to round a given value to, lets say 2 decimal places, rather than scaling up the value, converting to an integer, and scaling back down. I know I can create an action item to round the currently stored entries but I don’t want to put any extra stress on the database.

Thanks.

2

Hi,

Often I think it’s just easier to round on the display side. However, if you really want to round it going into the database, I guess you have a few options (and there are probably other options as well, for some reason I feel like I’m missing an easy one):

  1. FactorySQL action item: Mark the OPC item as “read-only”. Then create an action item whose expression is: round({opcitem}, 2) , with the correct item reference, of course (use the CTRL-SPACE shortcut or right click->“reference item” to reference the item). Then have the action item write to the column that the opc item was previously writing to.

In your post you said you didn’t want to add stress to the DB. I think you were imagining reading the value with a query and then writing the value back. Doing the above, with a read-only opc item that’s referenced won’t add any extra stress on the DB. The only downside is you have to create an action item for each point you want.

  1. Change database column type: Some DBs let you specify the precision of the column. MySQL, for example, lets you say something like “DOUBLE PRECISION (8,2)” which means the column can hold 8 digits, 2 of which can be after the decimal. You can just change the columns that FactorySQL is writing to and let the db enforce the rounding. (See MySQL docs for more info)

  2. DB Trigger/Stored proc: Write a trigger on the database table that rounds the data during insert, or a stored procedure that rounds before then inserting into the table. I don’t really think I’d take this route, but it would probably work nonetheless.

That’s about what comes to mind immediately. Like I said, it’s usually easier to just round in your select query or in the display, but there are probably good reasons for doing it on the storage side as well.

Regards,

3

I tried the database approach. Made the most sense to me. However, I’m getting these error messages:

Out of range value adjusted for column 'Tank_106_Low_Speed' at row 1

The problem is that the data is actually not being logged, and I’m not sure why. Any ideas?

4

Ehhh, it may be throwing an error instead of rounding. Like, you have it set to 2 decimal places, and then you send it “3.53” and it responds with “whoa! too much info, don’t want to chop it, ERROR OUT!”. I’m sure there’s probably an option for this somewhere, but I’m not sure where.

Maybe I should start by asking what you defined the column as, and what you tried to put in it?

Also, what OPC server are you using? Depending on the server there may be other options as well.

Regards,

5

The value that was being rejected was a number with about 38 digits before the decimal place, and I set the column to a double with a length of 8 and 2 decimal places. This data is from a thermocouple which probably came loose, resulting in the extraordinarily high value. Although the data is meaningless, I would still prefer that it be stored in the database and truncated. I’m using KepServer for my OPC server but I don’t think there is any option to round. I changed the datatype to a double of length 40 with 2 decimal places. Will this cause any memory/performance issues? We are only logging this item once every minute.

6

I definitely wouldn’t worry about any performance problems- it’s really no different than using the default “double” data type (up to 53 digits, I think).

I looked around a bit and it appears that you might be able to get it to just store the truncated data by turning off “strict” mode, but you’ll have to look into that a bit more. I just searched for that error message you posted earlier.

Regards,

7

That makes sense. If only the error message wasn’t so misleading. Thanks