AMU - Process Troubleshooter - Timezone

[Sathishkumar_C]

I have query with AMU Process Troubleshooter.

We have configured AMU Assets with 3 levels of heirarchy.

Site --> Area --> Asset

Thingworx hosted with UTC.

Area is configures with UTC-6

AMU is accessing from browser (system time is UTC-6).

In Process troubleshooter, I'm selecting a filter "Today" It's take wrong time it seems.

System time:

If I'm selecting filter with "CUSTOM" and selecting larger time range I'm able to see the data.

How to address the issue?

mstarnaud

[mstarnaud]

Hi Satishkumar

This is strange, I tried to reproduce on my test server but it came up fine for me : the Start Time shows me 00:00 because my start-of-day setting is 0, and the End Time is my computer's time for both Today and Custom.

I have to ask what version you have. To know this you can go in Composer -> Manage (left menu) -> Installed Extensions -> select MnfgCommon_Extension -> look for the Package Version.

Second, I would like you to verify that your database is correctly set to UTC. If it's something else, it leads to some unexpected timezone issues like this. To check this, you can make this SQL query : SELECT GETDATE(), GETUTCDATE(). Both columns should be identical, if they're different then you have the issue. You can also try this query : SELECT CURRENT_TIMEZONE(); but this query doesn't always work depending on SQL version & permissions. I know there's also some UTC-related Thingworx setting required, but I'm much less familiar with that, if what we find today isn't enough then I'll try to get help for this.

Third, I want to check if the database is returning the expected results. The first step is to find the EquipmentUid (column Uid in the Equipment table) for one of the equipments having this issue. Then, execute this query (you need to change the 1st line) : 

DECLARE @EquipmentUidList NVARCHAR(MAX) = '123456789' -- CHANGE THIS
DECLARE @TimeRangeList NVARCHAR(MAX)
EXEC MnfgCommon_QuickTimeIntervalList_SP
	@op_TimeRangeList = @TimeRangeList OUTPUT,
	@p_QuickTimeIntervalUID = 3, -- 3=Today
	@p_EquipmentUIDList = @EquipmentUidList,
	@p_IsRoundingDown = 0
SELECT	[UID],
		StartTime,
		EndTime
FROM  	OPENJSON(@TimeRangeList, '$')
WITH
(
	[UID]		BIGINT		'$.UID',
	StartTime	DATETIME	'$.StartTime',
	EndTime		DATETIME	'$.EndTime'
)

This will return the results for Today, interpreted by the database. I expect the results should be Start=06:00 and End=now+06:00.

[Sathishkumar_C]

Thanks for the detailed information. Here is the required details below for your reference. What might be the issue?

[mstarnaud]

Well, I was right with my big suspect about the database's timezone. It's a requirement that a lot of people skip in the documentation and it always ends up causing weird time conversion issues like this.

To fix it (based on the previous attempts I've seen, I'm still trying to find good documentation on this) : remote desktop on the server that has the database installed on it, and set the Windows Clock's timezone to be UTC. Because SQL uses the server's windows clock timezone as the database's timezone. After doing this, in order for it to take effect you might need to restart the SQL service on that server.

After it's fixed, the query 1st query should return identical times and the 2nd query should return "(UTC) Coordinated Universal Time".

Because this requirement was missed, I'm scared that the other similar settings were missed on the Thingworx side, there's 1 or 2 settings (that are not the windows clock) that must be set for the Thingworx timezone. But I don't know this part well, I will need help for this section. So I suggest you start trying with the windows clock fix and see if it's enough to fix the issue.

[Sathishkumar_C]

Yeah, it's working as expected in process trouble shooter.

But, what about the logged data those data will be fine? We do have couple of weeks data as attributes log.

[mstarnaud]

I'm glad it's working as expected now! But sadly, this change will not fix existing data.

So if you want to fix the existing data, there's a way but keep in mind it's not officially supported and at your own risk, and it's all made using SQL queries because we have to modify the data.

#1 : identify the tables that contain data with incorrect times, and find the time difference. For the values in the attributes, that you can see in Troubleshooter, this is in the AttributeValue table (TimestampResult column). If you use the alarm system, there's the AlarmEvent table with its start & end times. If you use notifications for emails, there's the NotificationLog table. For escalations, there's the EscalationEvent table. For AMU I think that should cover all the time-based information.

#2 : for each of those tables, identify the time conversion you need to apply and also at what time you applied the windows clock change. Keep in mind that the database should contain UTC times. So as an example, you might find that for all times before 2024-02-22 08:00, you need to add 6 hours to the time.

#3 : you need to use an Update query to apply the change, putting the windows clock change time in a Where condition in order to not change the new correct times. It will probably look like this : 

UPDATE attributevalue SET timestampresult = DATEADD(HOUR, 6, timestampresult) WHERE timestampresult < '2024-02-22 08:00'

This is for the AttributeValue table, TimestampResult column, adding 6 hours. You need to repeat this for all the tables & columns where you want to apply the change.

Keep in mind that if you have millions of records in the AttributeValue table, this table might take some time (multiple minutes?) to apply the change. I expect for the other tables it should take only a few seconds, if you have a month of data.