SQLite.Net-PCL 如何处理 UTC 时间

我正在使用以下代码:

使用 new SQLite.Net.SQLiteConnection(new SQLitePlatformWinRT(), DBPath) 获取 SQLiteConnection并创建表.

类包含日期时间数据类型

class 交易{[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]公共 int QId { 获取；放;}公共日期时间购买日期{获取；放;}公共整数金额{get;set;}公共字符串 ItemCode {get;set;}}

插入数据如下:

var db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);var newItem = new Transaction(){购买日期 = 日期时间.现在，金额 = 100,项目代码 = "ABC-C10"};db.Insert(newItem);

日期将存储为 Ticks(例如 636071680313888433)，这是 UTC 时间.

1) 使用上面的 DateTime.Now，如果我的电脑时间设置是

1a) 英国时间，

上面的代码:purchase = DateTime.Now 能正确转换吗?

1b) 在美国时间，

上面的代码:purchase = DateTime.Now 能正确转换吗?

如何在 SQL 语句中处理这个勾号?

如何从某个日期范围内选择所有交易?比如说，2016 年 7 月 10 日到 2016 年 7 月 20 日?

谢谢

解决方案

处理日期最安全的方法是使用 DateTimeOffset 类型而不是 DateTime.

DateTime 不包含创建时区的信息，它只知道它是 UTC 时间还是本地时间，如果数据要在不同的地方使用.

DateTimeOffset 不仅包含时间和日期信息，还包含时区，这意味着结果将始终如您所愿.

使用方式没有区别，只是改变了类型:

class 交易{[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]公共 int QId { 获取；放;}公共日期时间偏移购买日期{获取；放;}公共整数金额{get;set;}公共字符串 ItemCode {get;set;}}

对于数据库访问:

var db = new SQLite.Net.SQLiteConnection(新 SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);var newItem = new Transaction(){PurchaseDate = DateTimeOffset.Now,//或使用DateTimeOffset.UtcNow作为UTC日期时间金额 = 100,项目代码 = "ABC-C10"};db.Insert(newItem);

I am using below code:

using new SQLite.Net.SQLiteConnection(new SQLitePlatformWinRT(), DBPath) to get SQLiteConnection and create table.

Class contain DateTime DataType

class Transaction 
    {
        [SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
        public int QId { get; set; }
        public DateTime PurchaseDate { get; set; }  
        public int Amount {get;set;}
        Public string ItemCode {get;set;}      
    }

Insert Data As follows:

var db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);

 var newItem = new Transaction()
  {  
     PurchaseDate = DateTime.Now,            
     Amount = 100,
     ItemCode = "Abc-C10"               
  };

 db.Insert(newItem);

The date will be stored as Ticks(e.g. 636071680313888433) and this is UTC time.

1) using above DateTime.Now, If my Computer time setting is

1a) in British Time,

will the above code : purchase = DateTime.Now be converted correctly?

1b) in Usa Time,

will the above code : purchase = DateTime.Now be converted correctly?

How to handle this tick in SQL-statement?

How to select all transaction from ,say, a date range ? say , 2016-07-10 to 2016-07-20 ?

Thanks

解决方案

The safest way to work with dates is to use the DateTimeOffset type instead of DateTime.

DateTime does not contain the information about the time zone in which it was created, all it knows is whether it is in UTC or local time, which is not enough if the data is going to be used in different locations.

DateTimeOffset contains not only the time and date information, but also the time zone, which means the result will always be what you expect.

There are no differences in the way it is used, just change the type:

class Transaction 
{
    [SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
    public int QId { get; set; }
    public DateTimeOffset PurchaseDate { get; set; }  
    public int Amount {get;set;}
    Public string ItemCode {get;set;}      
}

For database access:

var db = new SQLite.Net.SQLiteConnection(
   new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);

var newItem = new Transaction()
  {  
     PurchaseDate = DateTimeOffset.Now, //or use DateTimeOffset.UtcNow for UTC datetime           
     Amount = 100,
     ItemCode = "Abc-C10"               
  };

 db.Insert(newItem);