Android: android.database.sqlite.SQLiteException on rawQuery

Question

Unable to detect cause of application crash. Variables StartDate and lastTimeCalculateValues are long.

Cursor c = MyApplicationExtendsClass.database.rawQuery("SELECT * FROM " + MyApplicationExtendsClass.locationTableName + " WHERE [Date] >= '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.US).format(StartDate) + "' AND [Date] <= '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.US).format(lastTimeCalculateValues) + "' ORDER BY Id ASC", null);

Crash output from Google play console, 5% of active users and that is all I get.

android.database.sqlite.SQLiteException: 
  at android.database.sqlite.SQLiteConnection.nativePrepareStatement (Native Method)
  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement (SQLiteConnection.java:948)
  at android.database.sqlite.SQLiteConnection.prepare (SQLiteConnection.java:559)
  at android.database.sqlite.SQLiteSession.prepare (SQLiteSession.java:603)
  at android.database.sqlite.SQLiteProgram.<init> (SQLiteProgram.java:63)
  at android.database.sqlite.SQLiteQuery.<init> (SQLiteQuery.java:37)
  at android.database.sqlite.SQLiteDirectCursorDriver.query (SQLiteDirectCursorDriver.java:46)
  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory (SQLiteDatabase.java:1493)
  at android.database.sqlite.SQLiteDatabase.rawQuery (SQLiteDatabase.java:1427)
  at my.packagename.MainActivity.calculateValuesFinalFromDb (MainActivity.java:4013)
  at my.packagename.MainActivity.access$1800 (MainActivity.java:152)
  at my.packagename.MainActivity$CalculateValuesTimer.run (MainActivity.java:4112)
  at java.util.TimerThread.mainLoop (Timer.java:562)
  at java.util.TimerThread.run (Timer.java:512)

SQLite docs for DateTime SQLite docs

A time string can be in any of the following formats:  
  
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

Documentation says yyyy-MM-dd HH:mm:ss.SSS can be used

Answer 1

Try to change

SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.US)

To

SimpleDateFormat("yyyy-MM-dd", Locale.US)

This is the default SQL format for dates.

And see my answer here: Can't get data from date to date SQLite

---

Update:

First part of your link:

SQLite supports five date and time functions as follows:

1. date(timestring, modifier, modifier, ...)
2. time(timestring, modifier, modifier, ...)
3. datetime(timestring, modifier, modifier, ...)
4. julianday(timestring, modifier, modifier, ...)
5. strftime(format, timestring, modifier, modifier, ...)

To get presicion in milliseconds you could use following conversion:

CAST((julianday('your_date') - 2440587.5)*86400000 As INTEGER)

Explanation:

1. julianday('your_date') returns number of days since noon in Greenwich on November 24, 4714 B.C.
2. julianday('your_date') - 2440587.5 returns number of days in Epoch time (1970)
3. *86400000 converts it to milliseconds (24* 60* 60* 1000)

Found here.

Then you can do Integer comparison with >= or also with BETWEEN .. AND ..