問題描述

我正在使用以下代碼:

使用 new SQLite.Net.SQLiteConnection(new SQLitePlatformWinRT(), DBPath) 獲取 SQLiteConnection并創(chuàng)建表.

類包含日期時間數據類型

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 不包含創(chuàng)建時區(qū)的信息，它只知道它是 UTC 時間還是本地時間，如果數據要在不同的地方使用.

DateTimeOffset 不僅包含時間和日期信息，還包含時區(qū)，這意味著結果將始終如您所愿.

使用方式沒有區(qū)別，只是改變了類型:

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);

這篇關于SQLite.Net-PCL 如何處理 UTC 時間的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！