Azure移动应用程序在Sqlite数据库中保存了不正确的日期时间 [英] Azure Mobile Apps saves incorrect datetime in Sqlite database

问题描述

我正在尝试在Xamarin Forms应用程序的AzureMobileServicesClient SQLite数据库中保存具有ID和DateTimeOffset的实体.

I am trying to save an Entity with an ID and an DateTimeOffset in a AzureMobileServicesClient SQLite database in a Xamarin Forms application.

只要时间戳不明确，此方法就可以正常工作.当我们从夏令时转到标准时间时，有时会因时区而导致时间戳记含糊不清.在丹麦，2016年是10月30日凌晨2:00-3:00.

This work fine as long as the time stamp is non ambiguous. When we go from daylight saving to standard time there is a period a time where a time stamp can be ambiguous depending on the timezone. In 2016 in Denmark it is 2:00-3:00 AM October 30th.

我将实体DateTimeOffset保存为UTC，但将其保存为本地时间戳.

I save the Entities DateTimeOffset as UTC, but it saves it as a local timestamp.

当我将模糊的时间戳记保存为2016-10-30 12:30 AM + 0:00时，它将返回为2016-10-30 11:30 AM +0:00. 我已经针对其他时区进行了测试，并且仅在该特定时区从夏令时转换为标准时间的过程中才出现这种模棱两可的情况.

When I save an ambiguous time stamp as 2016-10-30 12:30 AM + 0:00 it comes back as 2016-10-30 11:30 AM +0:00. I have tested this for other time zones and it only happens with the ambiguous time during the shift from daylight saving to standard time in that particular time zone.

我在这里看到了针对普通SQLite数据库解决的问题:

I have seen the issue fixed for a normal SQLite data base here: http://www.thomaslevesque.com/2015/06/28/how-to-retrieve-dates-as-utc-in-sqlite/
But because i am using AzureMobileSerives the solution don't work in this case.

在这段代码中，我初始化了AzureMobileService，保存了一个不明确的时间戳，然后再次获取它，并将两次写到屏幕上以查看不同的输出

In this code i initialize the AzureMobileService, save an ambiguous time stamp and then retreive it again, and write out the two times to the screen to see the different output

public static class SaveTimeStamp
{
    public static async Task Save()
    {
        //Init mobile service Client 
        var mobileService = new MobileServiceClient(Configuration.MobileAppsUrl);

        //init MobileService Database
        var dataStore = DependencyService.Get<IDataStore>();
        var path = dataStore.GetPath("store.db");
        dataStore.CreateFile(path);
        var store = new MobileServiceSQLiteStore(path);

        store.DefineTable<Entity>();
        await mobileService.SyncContext.InitializeAsync(store, StoreTrackingOptions.NotifyLocalAndServerOperations).ConfigureAwait(false);
        var entityTable = mobileService.GetSyncTable<Entity>();

        //Save entity with ambiguous timestamp
        var ambiguousTimestamp = new DateTimeOffset(2016, 10, 30, 0, 30, 0, TimeSpan.Zero); //UTC: 30th of October 12:30 AM + 0:00 => DK time:30th of october 2:30 AM + 2:00

        var entity = new Entity
        {
            Id = Guid.NewGuid().ToString(),
            Timestamp = ambiguousTimestamp
        };

        Debug.WriteLine("Saving datetime UTC: " + entity.Timestamp.UtcDateTime);

        await entityTable.InsertAsync(entity).ConfigureAwait(false);

        //Fetch saved entity
        var refecthedEntities = await entityTable.Where(e => e.Id == entity.Id).ToListAsync();
        var refecthedEntity = refecthedEntities.FirstOrDefault();

        if (refecthedEntity.Timestamp != ambiguousTimestamp)
        {
            Debug.WriteLine("Refecthed datetime UTC do not match: " + refecthedEntity.Timestamp.UtcDateTime);
        }
        else
        {
            Debug.WriteLine("Refecthed datetime UTC: " + refecthedEntity.Timestamp.UtcDateTime);
        }

        //output 
        //[0:]Saving datetime UTC: 10/30/2016 12:30:00 AM
        //[0:]Refecthed datetime UTC do not match: 10/29/2016 11:30:00 PM
    }
}

class Entity
{
    public string Id { get; set; }
    public DateTimeOffset Timestamp { get; set; }
}

因为这是Xamarin应用程序，所以我在xamarin.ios项目中也有一些代码可以初始化数据库.

Becuase this is a Xamarin application i also have some code in the xamarin.ios proejct to initlize the database.

//IN Xamarin.IOS  
[Register("AppDelegate")]
public partial class AppDelegate : global::Xamarin.Forms.Platform.iOS.FormsApplicationDelegate
{

    public override bool FinishedLaunching(UIApplication app, NSDictionary options)
    {

        global::Xamarin.Forms.Forms.Init();
        Microsoft.WindowsAzure.MobileServices.CurrentPlatform.Init(); //Init Azure mobileservies on IOS Current  IOS 9.3
        SQLitePCL.CurrentPlatform.Init();

        LoadApplication(new App());

        App.ScreenWidth = (int)UIScreen.MainScreen.Bounds.Width;
        App.ScreenHeight = (int)UIScreen.MainScreen.Bounds.Height;

        return base.FinishedLaunching(app, options);
    }

    public override void WillEnterForeground(UIApplication uiApplication)
    {
        base.WillEnterForeground(uiApplication);
        uiApplication.IdleTimerDisabled = true;
    }

    public override void DidEnterBackground(UIApplication uiApplication)
    {
        base.DidEnterBackground(uiApplication);
        uiApplication.IdleTimerDisabled = false;
    }
}

我尝试在实体上使用DateTime而不是DateTimeOffset，并且仅使用DateTimeKind = UTC，但是我得到了相同的结果.

I have tried to use a DateTime instead of DateTimeOffset on the entity and only use DateTimeKind=UTC, but I got the same result.

推荐答案

这似乎是已报告的错误:

This looks like an already reported bug: https://github.com/Azure/azure-mobile-apps-net-client/issues/131

随时关注该错误.

这篇关于Azure移动应用程序在Sqlite数据库中保存了不正确的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！