ReSQL.ru
     
powered by simpleCommunicator - 2.0.38     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / SQLite, поле Date и индекс по этому полю
12 сообщений из 12, страница 1 из 1
SQLite, поле Date и индекс по этому полю
    #38501021
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
Есть таблица с индексом по полю типа "Date"

EXPLAIN QUERY PLAN показывает, что:
1.Индекс срабатывает:
Код: sql
1.
WHERE DocDate BETWEEN ...


2.Индекс НЕ срабатывает:
Код: sql
1.
WHERE date(DocDate) BETWEEN ...



Корректно отображает данные именно второй вариант.
Если в таблице "лимоны" записей, то бегать переборам СЛИШКОМ долго, так что второй вариант исключается, а первый отображает не правильно.

Что же делать ... ((((((((((((((((((
Кто что подскажет ???
...
Рейтинг: 0 / 0
14.12.2013, 21:13
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501074
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Второй вариант - функция на поле. А индекс создается на поле.

А почему у тебя DocDate некорректно отображает? Что мешает писать туда дату в корректном виде?

Сделай вторичное поле в котором пиши результат от date(DocDate). И поиски-сортировки делай по нему.
...
Рейтинг: 0 / 0
14.12.2013, 22:31
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501431
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
авторА почему у тебя DocDate некорректно отображает? Что мешает писать туда дату в корректном виде?

Юзаю ASP.NET
Пишу корректно:
Код: c#
1.
2.
3.
//field.DocDate - это string
//потому, что может прийти и пустое значение.
SQLiteParameter parDocDate = new SQLiteParameter("@DocDate", System.Data.DbType.Date) { Value = field.DocDate.ToString("yyyy-MM-dd") }; cmd.Parameters.Add(parDocDate);



Если не указать функцию date() .
То выборка данных с использованием BETWEEN или <= and >=,
будет аналогична < and >,
то есть теряется знак "=".

Пример: BETWEEN 2013-12-01 and 2013-12-10
войдут только даты [2013-12-02, 2013-12-09]
и потеряются аж 2-е даты 2013-12-01 и 2013-12-10

П.С.1 Тестил в разных менеджерах, например SQLiteExpert ( http://www.sqliteexpert.com/) - теряются 2-е даты.
П.С.2 Можно конечно извращаться и брать дату AddDay(-1) and AddDay(+1) ... )))
...
Рейтинг: 0 / 0
15.12.2013, 16:37
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501467
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
potkinавторА почему у тебя DocDate некорректно отображает? Что мешает писать туда дату в корректном виде?

Юзаю ASP.NETЭто не важно. У тебя не работает BETWEEN, а это сам sqlite.
Поэтому показывай как у тебя хранятся даты в базе.

potkinТо выборка данных с использованием BETWEEN или <= and >=,
будет аналогична < and >,
то есть теряется знак "=".

Пример: BETWEEN 2013-12-01 and 2013-12-10
войдут только даты [2013-12-02, 2013-12-09]
и потеряются аж 2-е даты 2013-12-01 и 2013-12-10

П.С.1 Тестил в разных менеджерах, например SQLiteExpert ( http://www.sqliteexpert.com/) - теряются 2-е даты.
Этого не может быть. В случае если у тебя даты в базе записаны как строки 2013-12-01, 2013-12-02 и так далее, то описанное поведение невозможно.
Значит у тебя в базе лежит нечто, а аргументами в between ты отдаешь нечто другое.
Так как в sqlite нету типа "дата" и он соответственно не делает автоматическую конвертацию из того "нечто" что записано в базе в то "нечто" что ты используешь в качестве аргументов - то оба "нечто" приводятся в строки и сравниваются как строки.
Вывод: приведи аргументы для between в тот же формат что и даты записанные в базе и проблема исчезнет.
...
Рейтинг: 0 / 0
15.12.2013, 18:02
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501589
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
White OwlПоэтому показывай как у тебя хранятся даты в базе.

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE [Doc] (
  [DocID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [DocDate] DATE, 
  ...
);
CREATE INDEX [IDX_DocDate] ON [Doc] ([DocDate] DESC);  //И так пробовал
CREATE INDEX [IDX_DocDate] ON [Doc] ([DocDate] COLLATE NOCASE DESC);   //И так пробовал


Создавал таблицу с индексами и сам (с помощью .NET провайдера скаченного с сайта СкуЛайта) и в разных менеджерах пробовал.
В Менеджерах показывает дату так: "2013-08-03"

Выборку делаю так (точный кусок запроса):
Код: sql
1.
2.
3.
4.
5.
SELECT Doc.DocID, Doc.ListDocID, Doc.DocDate, ...
FROM Doc, ...
WHERE (date(Doc.DocDate) BETWEEN date(@DateS) and date(@DatePo))
ORDER BY date(Doc.DocDate) COLLATE NOCASE DESC, time(Doc.DocTime) DESC  
LIMIT 0, 9
...
Рейтинг: 0 / 0
15.12.2013, 21:58
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501717
anvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvg
Гость
potkin,
В таком случае дата хранится у вас как текст в формате 'yyyy-mm-dd hh:MM:ss.000'. Соответственно нужно и формировать фильтр
Код: sql
1.
WHERE DocDate BETWEEN '2013-12-01 00:00:00.000' And '2013-12-10 00:00:00.000'
...
Рейтинг: 0 / 0
16.12.2013, 05:05
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38501742
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
potkinWhite OwlПоэтому показывай как у тебя хранятся даты в базе.

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE [Doc] (
  [DocID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [DocDate] DATE, 
  ...
);
CREATE INDEX [IDX_DocDate] ON [Doc] ([DocDate] DESC);  //И так пробовал
CREATE INDEX [IDX_DocDate] ON [Doc] ([DocDate] COLLATE NOCASE DESC);   //И так пробовал


Создавал таблицу с индексами и сам (с помощью .NET провайдера скаченного с сайта СкуЛайта) и в разных менеджерах пробовал. Это не важно. Во первых, типа DATE в SQLite не существует. Во вторых, collate имеет смысл только для текстовых данных (и только для случая SQLite+ICU).

potkinВ Менеджерах показывает дату так: "2013-08-03"Это уже ближе к истине. А как эту дату показывает стандартный консольный sqlite3?
Менеджеры они "все для удобства" и прячут многие частности. Некоторые из этих частностей - важны, поэтому менеджеры в целом не удобны. Хочешь добраться до истины - бери самые примитивные, но и самые честные утилиты. А еще лучше, бери C и пиши сам.


potkinВыборку делаю так (точный кусок запроса):
Код: sql
1.
2.
3.
4.
5.
SELECT Doc.DocID, Doc.ListDocID, Doc.DocDate, ...
FROM Doc, ...
WHERE (date(Doc.DocDate) BETWEEN date(@DateS) and date(@DatePo))
ORDER BY date(Doc.DocDate) COLLATE NOCASE DESC, time(Doc.DocTime) DESC  
LIMIT 0, 9

COLLATE и NOCASE тут бессмысленны. Если бы ты работал с текстом в национальных алфавитах, то тогда бы они дали эффект. А для строки цифр - это бессмысленная трата ресурсов.
...
Рейтинг: 0 / 0
16.12.2013, 07:35
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38503195
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
anvg
White Owl
Спасибо.
Сегодня попробую и отпишусь.
...
Рейтинг: 0 / 0
17.12.2013, 11:07
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38505896
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
sqlite3.exe показал такой формат даты:
2013-12-27 00:00:00
Индекс для даты:
Код: sql
1.
CREATE INDEX [IDX_DocDate] ON [Doc] ([DocDate] DESC)


Запрос:
Код: sql
1.
2.
3.
4.
5.
SELECT Doc.DocID, Doc.ListDocID, Doc.DocDate, ...
FROM Doc, ...
WHERE (Doc.DocDate BETWEEN @DateS and @DatePo)
ORDER BY Doc.DocDate DESC, Doc.DocTime DESC  
LIMIT 0, 9
...
Рейтинг: 0 / 0
18.12.2013, 22:17
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38505921
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
potkin sqlite3.exe показал такой формат даты:
2013-12-27 00:00:00
Ну значит и искать ты должен по этому полю с учетом времени.
Задавай для своих @DateS = "2013-12-27 00:00:00" и @DatePo = "2013-12-29 23:59:59"
...
Рейтинг: 0 / 0
18.12.2013, 22:57
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38516821
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
Ещё вопросик.
Тестирую Запросы.
Иногда индексы не задействуются.
А задействуются только после пересоздания Индекса (удаления и повторного создания)!
Таблица:
Код: sql
1.
2.
3.
4.
5.
CREATE TABLE [Doc] (
  [DocID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [DocDate] DATE
)
CREATE INDEX [IDX_Doc_DocDate] ON [Doc] ([DocDate] DESC);


Запрос:
Код: sql
1.
2.
3.
4.
5.
EXPLAIN QUERY PLAN

SELECT *  FROM Doc 
WHERE DocDate BETWEEN @DocDate1 and @DocDate2
ORDER BY DocDate


Результат:
selectid order from detail0 0 0 SCAN TABLE Doc (~72 rows)0 0 0 USE TEMP B-TREE FOR ORDER BY

Пересоздание индекса (удаление и повторное создание) и результат
selectid order from detail0 0 0 SEARCH TABLE Doc USING COVERING INDEX IDX_Doc_DocDate (DocDate>? AND DocDate<?) (~4 rows)

Забил таблицу Doc несколькими лимонами записей, результат на лицо!
...
Рейтинг: 0 / 0
01.01.2014, 16:44
| Ответить | Цитировать | Написать  
SQLite, поле Date и индекс по этому полю
    #38516823
Фотография potkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
potkin
Участник
Это происходит после команды Vacuum .
Все индексы перестают "работать".
Надо делать удаление и создание всех индексов в Базе.
И команда Reindex не помогает.

П.С.
Может быть я что-то не дочитал ...
...
Рейтинг: 0 / 0
01.01.2014, 16:59
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / SQLite, поле Date и индекс по этому полю
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=38501021&tid=2008835]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 67ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 15ms
total:  177ms
созд. / загр.: 177ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]