First off, it’s important to note that in terms of actual sampling precision, you’re not going to be able to get down to the millisecond. There are too many variations along the chain from the PLC to the OPC server, FSQL and the database. I usually tell people not to expect more than about 100ms of precision, and even that can occasionally shift.
I know you’re logging at 500ms, so that’s fine- but I just want to make sure you don’t expect the sample to be taken at a given millisecond. If that’s what you want, you definitely need to buffer in the PLC. I believe there are a few threads on the forum here about how to then read those buffers from FSQL.
However, if you want this just so that your data lines up for table/correlation purposes, and the “000”/“500” millisecond values are more just IDs than actual timestamps, there are a few things you could do. First, instead of relying on the timestamp for the correlation, you could use a separate column for the “sample_id”, which would be 0 if it were the 000 sample, or 1 if it were the second sample for the time. You could do this with an action item:
(Note: the expression looks a bit strange because it seems that DateExtract doesn’t support milliseconds (or I couldn’t find the right keyword), so I used ToInt & DateFormat to get the milliseconds)
Basically what this is saying is that you know there will be two samples per second. You don’t know exactly at what time they’ll occur- that depends on when the group is started, and when it runs (which can be imprecise on the order of milliseconds, ignoring the effects of system load), but that you want to identify the executions as either the first or second one.
That’s easy, but of course it can make your queries more difficult. You have to group based on an expression of the timestamp, and then on this column as well if you’re trying to execute aggregate functions, among other things. Instead, it really would be best for the data to be only in the timestamp. Therefore, what you could do would be to create your own timestamp instead of using the built-in FSQL option.
- Uncheck “Store Timestamp” on the Action panel
- Create a new action item
- Use an expression like:
ToDate(Concat(DateFormat(now(),"yyyy-MM-dd HH:mm:ss."), if(ToInt(DateFormat(now(),"fff"))>500,"500","000")))
- Store results as date datatype to “t_stamp”, or appropriate date column.
In this way all of your timestamps will be either 000 or 500 in the milliseconds field.
Hope this helps,