Порядок по дате в SQLite3

ProgramBox

Порядок по дате в SQLite3

Post author:admin
Запись опубликована:17 января, 2022
Post category:Вопросы по программированию

#sqlite #date

Вопрос:

Я создаю простую таблицу в SQLite, которую я хотел бы упорядочить по убыванию даты. Вот SQL, который я запускаю для создания таблицы:

 create table projects("project_creation_date" text);
insert into projects ("project_creation_date") values ("08/05/2019");
insert into projects ("project_creation_date") values ("05/12/2018");
insert into projects ("project_creation_date") values ("06/08/2015");
insert into projects ("project_creation_date") values ("01/01/2020");
insert into projects ("project_creation_date") values ("02/03/2020");
insert into projects ("project_creation_date") values ("02/03/2019");

Вот мое заявление о выборе:

 select "project_creation_date" from projects order by strftime("project_creation_date", '%d/%m/%Y') desc;

Это все еще неправильно упорядочивает мои свидания:

Что я здесь делаю не так?

1. Первым аргументом функции ‘strtime’ является формат 😉

2. Переключение аргументов по-прежнему приводит к тому же результату….

3. Почему такие низкие голоса? Что может быть не так с этим вопросом?

Ответ №1:

Решение вашей проблемы состоит в том, чтобы прекратить вставлять литералы даты, не соответствующие стандарту ISO, в таблицы SQLite. Всегда используйте дату в стиле ISO, которая будет правильно отсортирована:

 create table projects("project_creation_date" text);
insert into projects ("project_creation_date") values ('2019-05-08');
insert into projects ("project_creation_date") values ('2018-12-05');
insert into projects ("project_creation_date") values ('2015-08-06');
insert into projects ("project_creation_date") values ('2020-01-01');
insert into projects ("project_creation_date") values ('2020-03-02');
insert into projects ("project_creation_date") values ('2019-03-02');

Обратите внимание, что в других базах данных, таких как Oracle, ваши инструкции insert полностью завершились бы ошибкой, исходные строки даже не были бы распознаны как литералы даты. Это особенность SQLite, которая на самом деле не имеет формального типа даты, только сходство с датой, что даты хранятся в виде текста. Следовательно, важно также использовать правильный формат, если вы заботитесь о таких вещах, как сортировка.

Если вы должны придерживаться своего текущего дизайна, то вам понадобится какой-нибудь уродливый SQL-код, чтобы получить нужный вам заказ. В SQLite нет strptime или другой подобной функции преобразования текста в дату, поэтому при сортировке вам придется собрать дату в правильном порядке:

 SELECT project_creation_date
FROM projects
ORDER BY
    SUBSTR(project_creation_date, 7, 4) ||
    SUBSTR(project_creation_date, 4, 2) ||
    SUBSTR(project_creation_date, 1, 2);

Опять же, взгляните на то, что вам нужно сделать, чтобы ваша текущая модель данных работала, и подумайте, стоит ли менять формат даты.

1. Что делать, если мой набор данных уже содержит литералы даты, не соответствующие стандарту ISO? Что делать, если у меня есть пользовательский формат даты, который я хочу использовать? Разве это не то strftime , для чего, как объяснено в sqlite.org/lang_datefunc.html ?

2. Если вы абсолютно вынуждены придерживаться своего текущего дизайна, то да, вам, возможно, придется использовать что-то вроде strftime первого преобразования нестандартных дат в правильный формат перед сортировкой. Но вам следует серьезно подумать об изменении способа хранения ваших дат; это значительно облегчит вашу жизнь.

3. это то, что я ищу здесь, и почему я подумал strftime , что это уместно. Существует ли альтернативное решение, которое либо 1) сначала преобразует даты в дату в стиле ISO, а затем сортирует по убыванию, либо 2) использует strftime для прямого упорядочения дат в порядке убывания?

4.@user32882 Нет, здесь strftime не будет работать, потому что эта функция начинается с действительной даты, а затем генерирует строку в качестве вывода. Вам нужно противоположное направление, а именно начать со строки и сгенерировать дату. Подумайте о моем обновленном ответе, который является одним из способов сделать это.

5. Вы можете сортировать по возрастанию или по убыванию, это зависит от вас, в любом случае это не суть моего ответа (или проблемы).

Метки: Порядок по дате в SQLite3