Time difference in Thingworx and Microsoft SQL server

[Jamal8548]

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 

[Surya_Tiwari]

Hi @Jamal8548 

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

1. 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).
2. 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.

[Jamal8548]

Hi @Surya_Tiwari 

I have two usecases actually

Usecase 1: When i fetch the data from SQL and get it in thingworx mashup. Everything works fine becasue thingworx automatically convert the datetime according to him. In thingworx i have UTC means i am having right time as i am in Germany currently so its exactly right time i am getting. Now when i get entries I can also edit them and for editing of entry i send datetime to SQL and then SQL automatically converts it into his own timezone and do stuff for me. Everything is working fine. 

NOW second requriement came:

Usecase 2: I want entries which are made in 2:00pm till 4:00pm. When i send the request from thingworx with this start end date then SQL automatiically change them and then send me wrong entries why? because sQL change this 2:00pm till 4:00pm to 12:00pm and to 2:00pm.

Now I did one test that i save the entries in SQL with addition of +2 hours so that it will be same as i have in thingworx ok but now when i get entry in thingworx. Thingworx is changing it to 2 hours more haha same issue and then my time is again not the same because thingworx is changing it. I hope that you get my point now please let me know the best solution? Currently i am working on local SQL server in my pc but thingworx is on server already running. 

[PEHOWE]

@Jamal8548 ,
This issue can be very confusing. The recommended configuration of ThingWorx is to have the server set to UTC. Depending on your database it is also possible to have a Timezone set in the database. The recommendation is to set the database time zone to UTC.
When ThingWorx mashup takes a date/time field and stores it in the database it will use the time zone information in the browser to convert the date/time to UTC. The UTC value is what is stored in the database. When a Mashup extracts a Date/Time value from the database it will apply the time zone of the browser and present the information converted to the browser local time zone.

If you are interested in the actual value stored in the database change you SQL query to return the Data/Time field as a STRING type. Because it is a string type it will not get the conversion from UTC to Browser Time zone. 

One of the reasons for storing all the time in UTC is that there is no such thing as "Day Light Savings". 

In your description you are discussing a 2 hour difference between what you see displayed in ThingWorx and from a query run directly on the database. Is the difference between your local time and UTC, 2 hours? 
 

Hopefully this information helps. 

Pehowe