Странное поведение запроса в sqlite

Question

Есть таблица со столбцами date, value. В дате формат 13-09-2020 00:00 итп. Запрос:

SELECT date, value from tablename WHERE date >= strftime('%d-%m-%Y %H:%M', 'now', 'localtime', '-1 month');

почему то выводит данные за сегодняшний день. Есть нюанс что данных ранее 6 сентября в таблице нет. По логике должно вывестись пусто? Почему такое поведение? P.S. запрос вида

SELECT date, value from tablename WHERE date >= strftime('%d-%m-%Y %H:%M', 'now', 'localtime', '-5 hours');

работает хорошо и выводит верные данные.

Answer 1

Начну, если не возражаете, с рекомендаций:

Никогда не храните в SQLite даты в чём-либо кроме Unix-времени. Иначе потом наедитесь… проблем, особенно со сравнением.
Если уж вам очень нужно хранить в SQLite даты как строки, используйте только ISO 8601 в UTC. По всё тем же причинам.

Так как уже «есть, что есть», вам надо перевести свои данные в стандартный вид через костыли:

WITH tablename_with_correct_date(date, value) AS (
  SELECT SUBSTR(date, 7, 4) || '-' || SUBSTR(date, 4, 2) || '-' || SUBSTR(date, 1, 2) || ' ' || SUBSTR(date, 12, 5)  AS date
       , value
    FROM tablename
)
SELECT *
  FROM tablename_with_correct_date
 WHERE date >= DATETIME('now', '-31 day')
;

А вот уже после этого можете использовать STRFTIME, чтобы вывести итоговые данные в нужном вам формате. Хотя, честно говоря, это должно быть работой приложения, а не БД.

БЛОГ НА HUSL

Странное поведение запроса в sqlite

Ответы (1 шт):