Skip to main content
J
javed_a13-Aquamarine
13-Aquamarine
October 4, 2024
Solved

Time difference in database and application

  • October 4, 2024
  • 2 replies
  • 3613 views

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.

Best answer by javed_a

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!

 

2 replies

S
Surya_Tiwari16-Pearl
16-Pearl
October 4, 2024

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

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

J
javed_a13-AquamarineAuthor
13-Aquamarine
October 4, 2024

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
Rocko19-Tanzanite
19-Tanzanite
October 4, 2024

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.

    mstarnaud
    mstarnaud16-Pearl
    16-Pearl
    October 4, 2024

    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.

    J
    javed_a13-AquamarineAuthor
    13-Aquamarine
    October 7, 2024

    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
    mstarnaud16-Pearl
    16-Pearl
    October 7, 2024

    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.

    Terms & ConditionsCookie settingsAccessibility statement