UTC time issue in Thingworx 9.3

[SKannapiran]

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.

[VladimirN]

Articles:

• "[Important] New Required Timezone setting in ThingWorx 9.x": https://www.ptc.com/en/support/article/CS331340
• "How to change the timezone of ThingWorx 9.x log timestamps": https://www.ptc.com/en/support/article/CS331416

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

[SKannapiran]

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. 

[DanZ]

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.

[SKannapiran · Accepted solution]

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.