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?
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
Still helping out your ex-colleagues!
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
Thanks everybody. I did not know that the conversion was already done in reactive.