Azure App Service - DateTime changes during Sync

Question

I'm working on an App which uses Xamarin.Forms and Azure App Service (include offline Sync).

On the client there is a piece of code like this:

appointment.StartDate = System.DateTime.Now;

Let's suppose appointment.StartDate is now 2017-07-05 12:00:00.

After the user syncs the data against the server, this happens:

Date in SqlLite database (on client): 2017-07-05 12:00:00

Date in server database: 2017-07-05 10:00:00

So, I assume that Azure changes my date to UTC. That might be technically correct because you should always store UTC in a database and handle timezone conversions in the client. But unfortunately, the server database is old and stores locale dates.

How can I make the Azure store the local date from the client in the server database and not UTC?

I tried to change the WEBSITE_TIME_ZONE property in Azure to my local timezone but this doesn't work: http://www.louischarlesgagnon.com/post/azure-app-service-set-timezone-for-your-web-application

Update 06.07.2017:

After some further research, I found out that this is a known "problem".

Look here:

https://github.com/Azure/azure-mobile-apps-net-client/issues/131

Azure Mobile Apps saves incorrect datetime in Sqlite database

https://forums.asp.net/t/1808269.aspx?DateTime+issues+with+Azure+c+javascript+sql+so+confused+

After reading this I was able to build a solution that resolves a part of the problem. On the client side, I'm now doing something like this.

 public System.DateTime? Start
    {
        get
        {
            System.DateTime? dateTime = GetValue<System.DateTime?>(_start);
            if (dateTime.HasValue)
                dateTime = System.DateTime.SpecifyKind(dateTime.Value, System.DateTimeKind.Utc);

            return dateTime;
        }
        set
        {
            System.DateTime? dateTime = value;
            if (dateTime.HasValue)
                dateTime = System.DateTime.SpecifyKind(dateTime.Value, System.DateTimeKind.Utc);

            SetValue<System.DateTime?>(_start, dateTime);
        }
    }

This tells Azure that the date is already in UTC and Azure does not have to convert. This works in the scenario that the System.DateTime, which was created on the client side, is now successfully stored in the server database without conversion.

But there is now an another problem:

When Azure returns the date stored in the server database to the client, Azure converts the "UTC date" to local date. Here is an example of the current situation:

Client Date: 06.07.2017 14:30

-> Client push date to server

Server date: 06.07.2017 14:30

-> Client gets date from server

Client date: 06.07.2017 16:30

Answer 1

But unfortunately the server database is old and stores locale dates.How can I make Azure to store the local date from the client in the server database and not UTC?

According to your description, I suggest you could store long type value DateTime.UtcNow.Ticks as your datetime not use datetime type.

This value will not be changed in your server database and it could be converted back to UTC time.

When you want to use the time, you could convert Ticks back to the datetime.

More details, you could refer to these codes:

    //get utc time ticks
    long i = DateTime.UtcNow.Ticks;
    //convert back to local time
    DateTime myDate = new DateTime(i).ToLocalTime();

---

I'm correct that I have to adapt my legacy applications so that they can deal with UTC?

In my opinion, I suggest you could store UTC time to your database. Then you could convert the UTC time to local time. This is right way to design your application. If there are different timezone customer, how you convert the database's local time to customer timezone's local time?

UTC is the time standard commonly used across the world. The world's timing centers have agreed to keep their time scales closely synchronized - or coordinated - therefore the name Coordinated Universal Time.

So I suggest you could use tolocaltime method to convert the UTC time to local time in your apps.