問題描述

我正在使用以下代碼:

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

類包含日期時(shí)間數(shù)據(jù)類型

class 交易{[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]公共 int QId { 獲取；放;}公共日期時(shí)間購(gòu)買日期{獲取；放;}公共整數(shù)金額{get;set;}公共字符串 ItemCode {get;set;}}

插入數(shù)據(jù)如下:

var db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);var newItem = new Transaction(){購(gòu)買日期 = 日期時(shí)間.現(xiàn)在，金額 = 100,項(xiàng)目代碼 = "ABC-C10"};db.Insert(newItem);

日期將存儲(chǔ)為 Ticks(例如 636071680313888433)，這是 UTC 時(shí)間.

1) 使用上面的 DateTime.Now，如果我的電腦時(shí)間設(shè)置是

1a) 英國(guó)時(shí)間，

上面的代碼:purchase = DateTime.Now 能正確轉(zhuǎn)換嗎?

1b) 在美國(guó)時(shí)間，

上面的代碼:purchase = DateTime.Now 能正確轉(zhuǎn)換嗎?

如何在 SQL 語(yǔ)句中處理這個(gè)勾號(hào)?

如何從某個(gè)日期范圍內(nèi)選擇所有交易?比如說，2016 年 7 月 10 日到 2016 年 7 月 20 日?

謝謝

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

DateTime 不包含創(chuàng)建時(shí)區(qū)的信息，它只知道它是 UTC 時(shí)間還是本地時(shí)間，如果數(shù)據(jù)要在不同的地方使用.

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

使用方式?jīng)]有區(qū)別，只是改變了類型:

class 交易{[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]公共 int QId { 獲取；放;}公共日期時(shí)間偏移購(gòu)買日期{獲取；放;}公共整數(shù)金額{get;set;}公共字符串 ItemCode {get;set;}}

對(duì)于數(shù)據(jù)庫(kù)訪問:

var db = new SQLite.Net.SQLiteConnection(新 SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath);var newItem = new Transaction(){PurchaseDate = DateTimeOffset.Now,//或使用DateTimeOffset.UtcNow作為UTC日期時(shí)間金額 = 100,項(xiàng)目代碼 = "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);

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