cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X

UTC time issue in Thingworx 9.3

SKannapiran
11-Garnet

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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 solution in original post

4 REPLIES 4

Articles:

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

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
15-Moonstone
(To:SKannapiran)

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.

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.

Top Tags