Log in to AnswerLog in to Follow
2017-07-24 06-43-32
Erik Brzozowski
124Views
4Comments
Solved
SQL convert UTC to local
Question
Reactive
Application Type
Reactive
Service Studio Version
11.12.7 (Build 51940)

I tried the following 2 SQL conversion from UTC to local time. In SQL server and in the test of the advanced SQL this is working however in the UI  it shows the wrong date 


,CONVERT(datetime, SWITCHOFFSET({Registration}.[CreatedOn], DATEPART(TZOFFSET, {Registration}.[CreatedOn] AT TIME ZONE 'W. Europe Standard Time')))
,Convert(DATETIME, {Registration}.[CreatedOn] AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time')


I created a new record: 2021-11-11 10:48:57.000 (local time)

In the database it is 2021-11-11 09:48:57.000 (UTC)


When testing the SQL in service studio, last record is the correct time


in the UI: it shows the wrong time 


Also the older record in the summer time have a difference of 2 hours. 

My question, why is this working when i test the query in service studio, but not when it's running in the application? 

0
0
11 Nov 2021
2018-10-29 08-31-03
João Marques
mvp_badge
MVP
Solution

Hi Erik,


Date times are stored in the database in the server time, which is commonly UTC for servers in Europe.

In reactive and mobile applications, the date time is automatically converted to the client timezone. For example, if you have 2021-11-11 08:00:00 in the database in UTC and you open the app in a device with UTC + 1 timezone, then you will be seeing 2021-11-11 09:00:00. The same way if your device is in UTC + 10, then you would be seeing 2021-11-11 18:00:00.

When you are testing the query, there is no client-side to convert it automatically, so you see the date time as it is in the database (in the aforementioned example in UTC,  2021-11-11 08:00:00).


Kind Regards,
João

1
0
11 Nov 2021
2020-04-15 12-37-36
Daniël Kuhlmann
mvp_badge
MVP

Still helping out your ex-colleagues!

1
0
11 Nov 2021

Hi Erik,

when using Reactive application, the platform is already doing this conversion from server time to the device time for you.  

So, as you already do conversion from UTC to W. Europe, in your SQL, if you test this while being for example in the Netherlands, it is now done twice.

And yes, in the summer time, the diffence is 2 hours because of daylight savings time.

Dorine

0
0
11 Nov 2021

Thanks everybody. I did not know that the conversion was already done in reactive. 


0
0
12 Nov 2021
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.