False peak in trend (Historicized Tags)

Hi,
Im having this problem when I display some trends of the tags that Im Historicizing.

I ran a client of my Ignition app, and Ive noticed some lack of sync with the trend that I was looking at. I realized that the clock of the server was out of sync with the clock of the client. So, Ive used the command “NET TIME” in the DOS console to synchronize the both clocks. It work well, but with some side effects. At the end of the trend appeared a false peak in the trends (as showed in the attachment). The graph is continuosly updating correctly, but that peak always appears, and confusses the operator of the control system.

Please help me with this little issue, it`s really annoying to have that peak fouling that trend.


Changing the clock on the computer probably caused the history system to think the system was down for some period of time, in which case the values returned will be of “bad” quality, and won’t display on the chart. If the gap is small enough, the chart still draws lines, and could possible look like what you have.

Take a look at the “sqlth_sce” table- it holds the record of when the scan classes were running, and is used to calculate when the system was down. While the system is up, each scan class updates the “end_time” of the most recent entry for it’s id. The thing to determine is whether there is a gap in this table represented by multiple rows for a given “scid”, with a difference between the end_time of one and the start_time of the next, that covers the time you’re having a problem with.

Of course, working with these tables by hand can be a tad difficult due to the way timestamps are stored. If you’re using mysql, here’s a query to view the entries in order with easy to read dates:

SELECT scid, from_unixtime(start_time/1000) as 'start', from_unixtime(end_time/1000) as 'end' FROM sqlth_sce

If you see a gap that corresponds to your point, the easiest thing to do would be to make the closest entry cover it. For example, update the “start_time” of the last row to contain it. It’s in milliseconds, so if you wanted to expand it by a day, you could do something like:

UPDATE sqlth_sce SET start_time=start_time-(60*60*24*1000) WHERE start_time=X and scid=Y

With the appropriate values for scid and the previous start time value.

Hope this helps,

I may not explained well what I ment. What I tried to say is that this peak appears always like one second “ahead in the future” in the graph. The following image shows the same tag`s history with about one minute of difference.


You can see in the previous image that the peak in right corner in both graphs appears a peak in the exact same position , but at different times, so the trend is displaying a phony peak always, and when you reach that time it overwrites with the correct value, but the peak is still appearing one moment ahead in the future.

I`m using an Oracle database. Exploring the database something caught my attention in the table “SQLTH_PARTITIONS”


the “pname” field has duplicated data “sqlt_data_1_2011_02”. I don`t know if this is normal, but some extra info is always useful.

I hope you can help me with this problem.

Thank you in advance

Hi,

That is interesting that you have multiple entries for that partition. I don’t think that it is causing your problem, but it may be causing the system to do more work than necessary. I recommend running the following to delete one:

delete from sqlth_partitions where start_time=1296931179371

One partition is “Feb 5-Mar 1”, and one is “Feb 1-Mar 1”. That query will delete the one that starts Feb 5th.

I suspect your problem is being caused by the fact that queries sometimes return bad data for the last row, depending on how the “blocks” of the query line up (time span / requested data count). I need to speak with some people to see if this makes sense - the chart used to ignore this data point.

Let me know if removing that partition entry helps, though.

Regards,

After testing some more, I’ve started to think the extra partition entry could be the problem. It appears that it does, in fact, query the data a second time, and then uses it- but since it’s already run through the whole time frame, it only has 1 block to fill. Since the default query mode is min/max, that’s what it does, and you end up with a spike consisting of the minimum and maximum values you had over the query period. When looking at your screen shots, this seems to hold true.

So, try deleting that entry, and let me know if it fixes the problem. I believe we’ve got it fixed from the software point of view (for 7.2.3), but I doubt you’ll end up with another “dual” partition entry in the future.

Regards,

Sorry about the delay, I wasn`t at the office these last days.

I`ve just deleted the duplicated partition and it worked perfect!.

Thank you for your help.

Best Regards