Порядок сортировки SQLite в столбце даты и времени, заполненном из С# с помощью DateTimeOffset

Я работаю над приложением Metro для Windows 8, которое использует SQLite в качестве локальной базы данных.

Одна из таблиц в базе данных содержит столбец даты и времени. Я вижу неожиданные результаты при выполнении запросов, которые сортируют набор результатов по столбцу даты и времени — набор результатов не упорядочен так, как вы ожидаете.

Столбец datetime заполняется данными, сгенерированными приложением 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
Спасибо глибдуд. Мы собираемся обойти это путем сортировки на клиенте вместо запроса к 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