Historic data in XY Chart not matching Historic data from system.tag.queryTagHistory

I think I’m having a time zone adjustment issue on historical data so I’m trying to figure out where I need to adjust time zone to make my data display the correct time zone on my historic data points. I can provide code or even access to the system privately but I have to be careful with screenshots because it’s a live system.

My environment…

This system is currently running 8.01 RC so this may be a bug that was fixed or it could just be that I’m doing something wrong.

I have a bunch of tags set up for Historic data collection.

On one screen I have an XY chart that displays historic tag data using the “Tag History” section of the dataSource binding. I have the Tag Path static bound with an Alias defined for each tag. I bind that alias to a series on my XY Chart. I used a pair of “Date Time Input” controls to bind the Start Date and the End Date for the Historical Time Range for my dataSource binding. I’m binding to the “props.value” property on the Date Time Input controls. It displays a chart properly but I’m not sure if the dateTime displayed on the X axis is in EST or GMT.

On another screen I have the same Date Time Input controls but a Table control instead of an XY Chart. That screen is using a Python script to display data points Ad Hoc.

Notes for the code. dtiStart and dtiEnd are the names of my Date Time Input controls. Data is collected on a 2 minute interval and they wanted to exclude the last data point.

paths = payload['path']
alias = payload['alias']

startTime = self.getSibling('CoordContDatePick').getChild('dtiStart').props.value
endTime = self.getSibling('CoordContDatePick').getChild('dtiEnd').props.value


tempDate = system.date.fromMillis(endTime)
tempDate = system.date.addMinutes(tempDate, -2)
endTime = system.date.toMillis(tempDate)

dataSet = system.tag.queryTagHistory(paths=paths, columnNames=alias, startDate=startTime, endDate=endTime, aggregationMode='LastValue', intervalMinutes=2)
self.props.data = dataSet

The issue we’re seeing is that the data for a given time on the chart is not lining up with the same date in the data that is displayed in the table. I suspect the issue is that one of the data sets is displaying its datestamp in GMT and others are displaying it in EST (local time for the Gateway). All I know is that I see dips on the chart that are not reflected in the data for the data table on the same time range.

My understanding is that “system.tag.queryTagHistory” returns the data with the timestamp for the gateway’s time zone. I understood charts to work the same way. I think that assumption is wrong because the data source is the same and the data doesn’t line up.

There has been some research here on the forum about a similar situation:

I will not claim to be an expert, yet PGriffith has posted in the thread above- Mr. 'Witman' and he ( and others ) seem to have some great insights on the solution(s).
I have been advised that it is also a good idea to inform folks about a more immediate alternative: you can go to:
https://support.inductiveautomation.com
Based on what you have said, I would check the thread, then look at the Knowledgebase article there which could be searched on with "Clock Drift detector", then look at the support policies. There is free non-priority e-mail support, and an account buys priority e-mail and/or voice support. Please let us know back here in the forum when/if the issue is solved. Many of us wish to know. Thanks!

Thanks for linking that thread. dkylep’s response appears to be the problem I’m experiencing. I have to compare notes with what he’s saying and what I’m doing to hopefully come up with a solution. The issue appears to be that queryTagHistory is not adjusted for daylight savings time. I guess I just have to figure out how to detect and adjust for daylight savings time on my data import.

You are very welcome, especially if you let the rest of us know when/if there is a solution. It seems that scripting is different for Perspective by comparision to Vision ( so I am told ). As to the daylight savings time detection, there are a couple of ways to solve that: 1. Manual method ( crude, yet effective )- coordinate with other administrators and managers to artificially change the time on the PC which provides the timestamp. Problems- automatically may get ‘goofed up’ at least twice per year ( depends on various time servers ), or manually ‘goofed up’ by users who look at the bottom of their screen then compare it to their clocks ( if they know how to reset it and are logged into the Tag server ). 2. Scripting to automate the process just for X-Y Chart ( this should be superior, maybe not as easy in the immediate future- yet better overall ). Now you know why I suggest the command prompt ( C:\ ) Time as a test. Unfortunately, the servers get their own system time. No warp there…

I got some help from a support rep. The Message Handler code above was changed as follows and the problem was fixed. If this is determined to be a bug and later fixed this code would need to be changed back to the code in my original post.

import time
def isDaylightSavings():
	currentTime = time.localtime()

	return currentTime.tm_isdst

paths = payload['path']
alias = payload['alias']

startTime = self.getSibling('CoordContDatePick').getChild('dtiStart').props.value
endTime = self.getSibling('CoordContDatePick').getChild('dtiEnd').props.value


tempDate = system.date.fromMillis(endTime)
tempDate = system.date.addMinutes(tempDate, -2)
endTime = system.date.toMillis(tempDate)

dataSet = system.tag.queryTagHistory(paths=paths, columnNames=alias, startDate=startTime, endDate=endTime, aggregationMode='LastValue', intervalMinutes=2)
newHeaders = list(dataSet.getColumnNames())
newData = []

for i in range(dataSet.getRowCount()):
	row = []
	for j in range(dataSet.getColumnCount()):
		if dataSet.getColumnName(j) == 't_stamp' and isDaylightSavings():
			row.append(system.date.addHours(dataSet.getValueAt(i,j),1))
		else:
			row.append(dataSet.getValueAt(i,j))
	newData.append(row)
	
self.props.data = system.dataset.toDataSet(newHeaders,newData)

First, the code declares a function to use python to determine if we’re in a daylight savings time condition. If we are, it loops through the dataset and offsets the date stamps by 1 hour.

1 Like

Upon further testing the problem is more complicated and this is actually making the data wrong. It looks like the data time offset is wrong on the chart side. Very confusing.

I did some more testing and it appears that queryTagHistory is taking timezone into effect when it queries the historic data but the XYChart does not when it is bond to historic tags.

So the script I applied actually slid the data one hour in the wrong direction when it was actually right. I undid that but my chart is still one hour off.

I know they’re in the process of writing a chart component so the likelihood of getting relief on this issue is probably low but I could use some help if anyone has an idea of how to fix this. I basically need to offset the date on my XY chart by 1 hour when we’re in daylight time.

To fix the chart (only for the period that is now incorrect), you should be able to substitute the number -1 ( negative 1 ) for the number 1 in the fifth line from the bottom- that is- look for the word “else:” and note that the line above appends using the function row.append (system.date.addHours…(i,j),1)). Only the data in the range specified has been altered for hours, thus you may only need to run this for the data that was modified by specifying the same date and time period. Note that the negative parameter worked before with the 10th line: system.date.addMinutes (tempDate, -2)- that was for *minutes.
So the code specified just works on the dataset specified for your chart, not on all the data in the database (unless that is what was specified).
Daylight savings time changes back in the fall, when we may get an extra hour of sleep.

I’m not populating my chart with a databind to live data. I’m populating it with static tag historic data collection. So the data in the database is in GMT. The screen that uses queryTagHistory properly recognizes daylight savings time and converts the timestamp from GMT to EST. The chart converts it to EST without recognizing daylight savings time.

The problem is that I don’t know of a place I can adjust the time on the timestamp when using a direct bound historic tag data source.

Then as a temporary measure, you can only adjust the chart machine timezone to an incorrect one. Failing that, if your Tag source ( PLC ) has a clock- you would get relief by adjusting that PLC time by an hour- until the * time-server puts that back to right. The PLC fix would only work if no one else and no other machine could adjust the hour ( without permission ). Time is a relative thing… It can be perceived by more than one machine.
Many PLCs are tied to an Internet Time server, thus trying to adjust the time at the PLC may be a very temporary adjustment. This can also be true for database servers. Much of this is specified in IEEE1588 and under the category of CIP for Rockwell Automation PLCs. The use of Symetricon Grandmaster clocks ( now Microsemi ) may have been replaced largely by time supplied by Internet Service Providers and NTP services. Not all of these physical clocks account for an hour of daylight savings time change…

Guess what I'm downloading right now! Yup, 8.0.3 RC2
I'm going to try out the new charting component! I have my fingers crossed that the queryTagHistory isn't going to cause the same issue, but we will see.

You may as well wait a few hours and get the full release - there’s a few critical bug fixes that aren’t in the RC but will be in the final, which should be out today.

1 Like

Thank you! I’ll wait for the full release.

Well that’s good news :slight_smile:

FYI, downloaded 8.0.3 today and created a few simple charts using the Time Series Chart. The time axis problem I had is resolved using the new chart. I plotted with historical tags and with the queryTagHistory function. Both are displaying proper times on the x-axis. I still have a lot to figure out but so far so good!

2 Likes