Неправильное сравнение дат в SQLite

#android #sql #sqlite

В бд есть столбец DATE где дата хранится в формате dd.mm.yyyy. Пытаюсь таким запросом:
DELETE FROM PRODUCTS WHERE DATE <= date('now','-30 day')
удалить все строки старше 30 дней, но удаляются все строки. Что не так?
    

Ответы

Ответ 1

В SQLite нет "настоящего" типа данных DATE - данные типа DATE/TIME могут храниться
в одном из следующих видов:


строк/text (обычно используют ISO8601: "YYYY-MM-DD HH:MM:SS.SSS")
целых чисел (UNIX epoch - число секунд прошедших с 1970-01-01 00:00:00 UTC)
вещественных чисел (as Julian day numbers, the number of days since noon in Greenwich
on November 24, 4714 B.C. according to the proleptic Gregorian calendar).


Соответственно, при использовании строк при сравнении будет использоваться лексикографическое
сравнение. Например, "2" > "1999999", т.к. первый символ первой строки (двойка) больше
единицы, первого символа во второй строке.

Это одна из причин почему в SQLite надо хранить дату/время только в одном из следующих
форматов:


YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD 


Вывод: если вам нужен другой формат даты, то форматируйте его только для вывода,
но не для хранения.


Ответ 2

Date возвращает строку вида yyyy-mm-dd. Ваша дата, видимо, хранится в виде текста.
Сравниваются строки, и, получается, что любой dd.mm.yyyy меньше любого yyyy-mm-dd,
так как . считается меньше чем любая цифра.
Попробуйте так:

DELETE FROM PRODUCTS WHERE date(DATE) <= date('now','-30 day');