Time difference in database and application

[javed_a]

Hello,

I am using MS SQL Server with Thingworx (AMU).

I have a table, in which I have start time column, which data type is time(0).

Once Start time shown on application, Thingworx is automatically converting UTC values to IST but there is approximately 58 min lagging and some other issues as well. Refer the attachment.

I tried changing time-zone to UTC of my local system, then time in application is 29 min behind.

Please let me know any work around for this.

[Surya_Tiwari]

Hi @javed_a  Can you take a look at the below article?

https://www.ptc.com/fr/support/article/cs340045

[javed_a]

Thank you  @Surya_Tiwari  for the link, I have gone through it, in my case offset comes 0.

I do not have any issue with UTC and IST conversion, but the conversion is not correct.

Any other thought on it.

[Rocko]

I think there may be some time conversion depending on where the site is located the asset is assigned to, you can look into that. There is also Daylight Savings Time which can be an issue bc not all location switch on the same date.

What is weird is the 1:10 minutes off. Have you checked the system times of db server and TWX server? Looks like some clock is not properly synced to NTP or someone configured an offset in system properties.

[javed_a]

Hi @Rocko ,

Same issue is there, even if I directly accessing the localhost, time is also synced.

Also, Database and Thingworx is on same server. Table is inside the same database for AMU (Thingworx Apps).

I tired some debugging which results as below:

1. Current Time on Database - Displays correct for IST
2. Current Time in Thingworx Service output - Displays correct for IST
3. Current Time in Thingworx Service logger- logs correct but in UTC

[Rocko]

Is your TWX server not configured to be in UTC but IST? I think recommendation is UTC and for AMU even an requirement.

This might be a topic for PTC support.

[javed_a]

Hello @Rocko , 

AMU is configured with default setting (UTC). 

[mstarnaud]

Hi Javed

A few notes : 

- In the AMU database, all time-typed columns should be in DATETIMEOFFSET, not in TIME(0).

- This leads me to ask : in what screen/Mashup do you see the values at the wrong time? An existing AMU screen, or a custom one that you made?

I must say I'm not familiar with TIME(0) and how reliable it converts the time in the Mashups. I would like you to go to one of the AMU screens, such as Operations -> Alarms, or Tools -> Troubleshooter, and check if the times listed there are correctly translated into your local time. This could help confirm if it's one of my 2 suspects (custom Mashup/service issue, datatype conversion issue) or if it's something else. Another test you could do is temporarily change your computer's timezone to something else, like Europe time, and see if that conversion works correctly or if there's still a 30m mismatch.

[javed_a]

Hello @mstarnaud ,

This issue is for a custom page.

I am creating a page to configure shift, and I want to store only time and not date, so I am using time(0) as datatype for column.

As mentioned earlier, when I tried changing time-zone to UTC of my local system, then time in application is 29 min behind.

At other places it is correct, however those are datetime and not only time.

[mstarnaud]

Thanks for the clarification. So it has nothing to do with AMU, but it's still related to Thingworx. But we can still use AMU to compare and test!

As I explained in my last message, can you please go to one of the AMU screens that show timestamps (like the Operations -> Alarms screen) and confirm if the conversions are correct there? If those values are also 30 minutes off, it would indicate that Thingworx has an issue converting to your timezone, for example maybe it detects it as something else like PKT instead of IST.

You can also execute the SQL service on its own, in Composer, to see if it returns the data correctly converted or not. Even in Composer it should be converted to IST when it shows you the results. If it's correct here but wrong in the Mashup, then it would mean the issue is later in the code (like in a Javascript service) or in the Mashup.

I tested on my side and I was able to create a Time(0) column, use it in a Mashup, set my computer's timezone to IST, and everything converted correctly.

[javed_a]

Thanks for explaining @mstarnaud 

I tried some examples, refer the attached snapshot.

DateTime column is being stored in UTC and shown on UI in UTC, however, time get converted to GMT+0521.

There is difference of 08min 50 sec, in converted time as its taking GMT+0521 instead of +0530 for IST.

I tested AMU, it is storing data in UTC and displaying in UTC, so there is no issue.

[javed_a · Accepted solution]

Hello @mstarnaud ,

I used a workaround and issue is resolved now.

1.  While fetching the data (time), I added Date and type as datetimeoffset(7).

SELECT

DATEADD (day, DATEDIFF (day, 0, GETUTCDATE()), CONVERT datetimeoffset, (starttime) as starttine,

FROM dbo.tblName;

2. While displaying on the grid, used formatting as hh:mm:ss A

Now the value is being stored in UTC and displaying in IST on UI with correct values.

Thank you all, once again!