Ред на сортиране на SQLite в колона за дата и час, попълнена от C# с DateTimeOffset

Работя върху приложение за Windows 8 Metro, което използва SQLite като своя локална база данни.

Една от таблиците в базата данни съдържа колона за дата и час. Виждам неочаквани резултати, когато изпълнявам заявки, които сортират набора от резултати по колоната за дата и час - наборът от резултати не е подреден както бихте очаквали.

Колоната за дата и час се попълва с данни, генерирани от приложението Metro в C#; например като DateTimeOffset.Now. WinRT не поддържа DateTime, така че използваме DateTimeOffset.

Можете да копирате това в проста таблица:

CREATE TABLE "Foo" ("DateOfFoo" DATETIME)

Ето някои примерни данни, базирани на данни от нашето приложение (имайте предвид, че съм в часовата зона PDT, която е UTC -7):

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 8:08:56 AM -07:00

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 8:13:42 AM -07:00

INSERT INTO "main"."Foo" ("DateOfFoo") VALUES (?1)
Parameters:
param 1 (text): 9/18/2012 12:46:36 PM -07:00

Проста заявка като:

Select * From Foo Order By DateOfFoo

връща следния набор от резултати:

9/18/2012 12:46:36 PM -07:00
9/18/2012 8:08:56 AM -07:00
9/18/2012 8:13:42 AM -07:00

когато очаквам:

9/18/2012 8:08:56 AM -07:00
9/18/2012 8:13:42 AM -07:00
9/18/2012 12:46:36 PM -07:00

и Select * From Foo Order By DateOfFoo DESC връща:

9/18/2012 8:13:42 AM -07:00
9/18/2012 8:08:56 AM -07:00
9/18/2012 12:46:36 PM -07:00

когато очаквам:

9/18/2012 12:46:36 PM -07:00
9/18/2012 8:13:42 AM -07:00
9/18/2012 8:08:56 AM -07:00

Моят колега в часовата зона CDT не можа да възпроизведе проблема. Промених часовата зона на моята машина на CDT и попълних данните за приложението и със сигурност заявката сортира правилно набора от резултати:

9/18/2012 2:46:36 PM -05:00
9/18/2012 10:13:42 AM -05:00
9/18/2012 10:08:56 AM -05:00

sqlite microsoft-metro
person George Durzi    schedule 18.09.2012    source източник

Отговори (2)


arrow_upward
2
arrow_downward

SQLite не поддържа действителен тип данни за дата/час. Датите и часовете се вмъкват в базата данни като низове и следователно се сортират по азбучен ред в оператора SELECT. SQLite поддържа някои функции за дата и час, но изисква низовете да бъдат в един от списък с формати, които използваните от вас не съвпадат. За правилните формати вижте документацията за SQLite.

person glibdud    schedule 25.09.2012
comment
Благодаря glibdud. Ще заобиколим това, като сортираме на клиента вместо на заявката към SQLite. В нашия случай C# LINQ заявка. Ще приложим OrderBy в C#, след като получим данните. - person George Durzi; 27.09.2012

arrow_upward
1
arrow_downward

SQLite съхранява дата/час като низове. Ако приемем, че стойностите са въведени в полето за дата/час, използвайки стандартен формат, можете да използвате orderby с персонализиран IComparer<string>:

public class SqliteDateComparer : IComparer<string> {
  public int Compare(string s1, string s2) => DateTime.Compare(DateTime.Parse(s1), DateTime.Parse(s2));
}

и тогава т.е.:

var res = (new MyDbContext()).MyData.OrderBy(v => v.TimeField, new SqliteDateComparer());
person kofifus    schedule 27.11.2018