Skip to main content
S
SKannapiran12-Amethyst
12-Amethyst
July 12, 2023
Solved

UTC time issue in Thingworx 9.3

  • July 12, 2023
  • 2 replies
  • 2614 views

In ThingWorx 9.3, we are encountering issue with UTC time. When my application is requesting DB data from MashUp, ThingWorx internally (dont know how it is happening) converting the IST time to UTC i.e., adding 5 hrs 30 mins to IST and showing the data for the converted time range.

When we are running the DB SP from the Database directly, the DB SP is returning the result accurately.

We are trying to fix the issue in the Apache Tomcat configuration by updating the entry as

"-Duser.timezone=UTC+5:30". But no impact. This ThingWorx server resides in the IST time zone.

Can someone please quickly guide me. Thank you.

Best answer by SKannapiran

Yes, the datetime stamp in the database are IST. 

We have arrived to an approach, earlier, from ThingWorx when an user input the datetime( picker control), internally it is converting into UTC and send as a input to SQL. 

Now we changed the datetime as a string value to the SQL, it fixed the issue. Thanks @DanZ  for your input.

2 replies

V
24-Ruby III
VladimirN24-Ruby III
24-Ruby III
July 12, 2023

Articles:

Discussion - "Timezone Problem": https://community.ptc.com/t5/ThingWorx-Developers/Timezone-Problem/td-p/814100

S
SKannapiran12-AmethystAuthor
12-Amethyst
July 13, 2023

Hi @VladimirN, thank you for your reply.

Rather updating the configuration, We have arrived to an approach, changing the datetime as a string value from the Mashup to the SQL, it fixed the issue. 

D
DanZ15-Moonstone
15-Moonstone
July 12, 2023

Do I understand correctly that the timestamps in your database are stored with IST timezone and not in UTC? Generally that should be avoided and the UTC time should be used. But sometimes you have to deal with the given cards.

 

We faced the same issue and solved it by converting the timestamp input parameter of the Thingworx SQL query. The following example was done for a MS SQL database:

 

SELECT [...]

WHERE (<dbTimestampColumn> AT TIME ZONE 'Central European Standard Time' >= [[lowerDate]] AT TIME ZONE 'UTC'

    AND <dbTimestampColumn> AT TIME ZONE 'Central European Standard Time' <= [[upperDate]] AT TIME ZONE 'UTC')
 
Thingworx/Tomcat is running with UTC timezone (as it should). In your case "Central European Standard Time" must be replaced with "India Standard Time". We also cast the timestamp in the SELECT statement to a string that contains the timezone information.
 
Convert(varchar(30), <dbTimestampColumn> AT TIME ZONE 'Central European Standard Time', 126) As timestamp
 
This will returns a ISO datetime string like "2023-07-12T10:30:00+02:00" and prevents the Thingworx conversion of datetime objects.
    S
    SKannapiran12-AmethystAuthorAnswer
    12-Amethyst
    July 13, 2023

    Yes, the datetime stamp in the database are IST. 

    We have arrived to an approach, earlier, from ThingWorx when an user input the datetime( picker control), internally it is converting into UTC and send as a input to SQL. 

    Now we changed the datetime as a string value to the SQL, it fixed the issue. Thanks @DanZ  for your input.

      Terms & ConditionsCookie settingsAccessibility statement