3

We have a sqlite fts3 table without enhanced syntax(Android!!!) where we use a query like this:

SELECT * FROM tableName WHERE tableName MATCH 'name*' AND validTo > 1389999600000 LIMIT 10

This works fine on iOS with fts4 and enhanced syntax, the validTo compare don't work as expected:

validTo > anyNumber => true
validTo < anyNumber => false

This has something to do with the that a full text search table contains strings. But then again - it works on iOS. On iOS we have times with a decimal value like 1389999600000.0 but we have tried that without any change.

Any clue on how to solve this?

rist
  • 578
  • 2
  • 9
Morten Holmgaard
  • 7,484
  • 8
  • 63
  • 85

1 Answers1

7

You should not use FTS tables to store non-text data.

If you really want to treat data in an FTS table as a number, you have to convert it explicitly:

SELECT * FROM MyTable WHERE CAST(validTo AS NUMERIC) > 1389999600000
CL.
  • 173,858
  • 17
  • 217
  • 259