Skip to main content
SKannapiran12-Amethyst
12-Amethyst
Jul 12, 2023 5:05 AM UTC
Solved

UTC time issue in Thingworx 9.3

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.

SKannapiranAnswer
12-Amethyst
Jul 13, 2023 6:41 AM UTC

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.

View in thread

4 replies

VladimirN24-Ruby IV
24-Ruby IV
Jul 12, 2023 8:22 AM UTC

Articles:

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

Translate
SKannapiran12-AmethystAuthor
12-Amethyst
Jul 13, 2023 6:42 AM UTC

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. 

Translate
DanZ15-Moonstone
15-Moonstone
Jul 12, 2023 9:47 AM UTC

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.
Translate
    SKannapiran12-AmethystAuthorAnswer
    12-Amethyst
    Jul 13, 2023 6:41 AM UTC

    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.

    Translate
      Terms & ConditionsAccessibility statement