datetime - SQLite3 split date while creating index -
i'm using sqlite3 database, , have table looks this:
the database quite big , running queries slow. i'm trying speed process indexing of columns. 1 of columns want index quote_datetime
column.
problem: want index date (yyyy-mm-dd) only, not date , time (yyyy-mm-dd hh:mm:ss), data have in quote_datetime
.
question: how can use create index
create index uses dates in format yyyy-mm-dd? should split quote_datetime
2 columns: quote_date
, quote_time
? if so, how can that? there easier solution?
thanks helping! :d
attempt 1: tried running create index id on data (date(quote_datetime))
got error error: non-deterministic functions prohibited in index expressions
.
attempt 2: ran alter table data add column quote_date text
create new column hold date only. , insert data(quote_date) select date(quote_datetime) data
. date(quote_datetime)
should convert date + time date, , insert into
should add new values quote_date
. however, doesn't work , don't know why. new column ends not having added it.
expression indexes must not use functions might change return value based on data not mentioned in function call itself.
date()
function such function because might use current time zone setting.however, in sqlite 3.20 or later, can use
date()
in indexes long not using time zone modifiers.insert adds new rows. modify existing rows, use update:
update data set quote_date = date(quote_datetime);
wiki
Comments
Post a Comment