Time difference in ThingWorx and Microsoft SQL server

Question

Hello Community, I have connected my thingworx with MS SQL and now problem is when i send the data entry from vuforia studio to my thingworx. It saves the data entry in SQL server and thingworx both but the ISSUE is here that it stores it with time 2 hours less and in thingworx exact my local time which is right. Now the problem comes when i send the data query with date and time. I am unable to fetch the data untilll or unless i would send the right time on which the entry is saved in SQL server. Anyone knows that how can i fix it? I am using datetime datatype in thingworx. The problem is if i make an entry between 1:00pm till 2:00pm. For query i have to give that time in order to fetch the entry from SQL. even though this same entry has time in thingworx 3:00 till 4:00. so SQL is saving 2 hours less for time. how can i fix it?
@Surya_Tiwari

Ivens · Accepted Answer

Hi @MA8731174 , I may have a suggestion for you. Set both (ThingWorx and DB) to UTC, let me exemplify how to do it:
1. ThingWorx: Assuming that you are running your ThingWorx on Windows, you should have something similar to the screen below to configure your Apache Tomcat, you need to include the following parameter to the Java Options, -Duser.timezone=UTC

2. On the MS SQL Server, unfortunately, we don;t have such parameter, the DB gets the timezone from the operational system. The solution here it's to configure your Windows or Linux to UTC.

With these two configurations in place ThingWorx should be able to handle the information properly and store everything in UTC on your database, the conversion will be performed automatically on the frontend when the customer opens your ThingWorx application.

Let me know if this will fix your issue.

Surya_Tiwari · Answer

Hi @MA8731174

Kindly check if your ThingWorx server time zone is UTC or other and what is your DB time zone? look at the following points.

Handle timezones on the server side (often can be avoided, and not recommended unless you need it). In ThingWorx it usually means making the time zone explicitly configurable on the User level, i.e. adding the corresponding field to the UserExtensions thing shape, implementing some logic for initializing and validating it, creating a mashup for selecting it, etc. Once you have it, you'll be able to do all date/time operations in the user's local timezone. You usually go for this option when you need to format your date/time as strings to send them out of the system (email, SMS, log files, etc.) Here we only scratch the surface, because if you operate internationally and want to do it right, you'll also need to store some regional settings like local date/time formats (e.g. YYYY-MM-DD vs MM/DD/YYYY).
Offload all timezone handling to the client side (browser, external mailing system, etc.) In ThingWorx you can do most of the date/time formatting work directly inside your mashups. Just return DATETIME objects whenever possible and make the browser do all formatting work for you. It will use user timezone automatically.

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded