0

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

Neo
  • 11
  • 1

1 Answers1

0

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 ..

Tobi
  • 858
  • 7
  • 15
  • What format have the dates in your database? Did you check the link? – Tobi Nov 07 '20 at 20:08
  • The format of the date column in the database is `yyyy-MM-dd HH:mm:ss` and I'm trying to record with milliseconds and it works since SQLite obviously ignores that part. I checked in the database and it doesn't exist. I don’t believe this is a problem because in that case the app would not work. The problem occurs with less than 5% of users and I cannot reproduce it. I checked the link, your suggestion is not acceptable to me because I need precision in milliseconds or in the worst case in seconds and not in days. – Neo Nov 07 '20 at 22:42
  • alright, have a look at my update, it should allow you to use millisecond precision – Tobi Nov 08 '20 at 09:04
  • Thanks for the suggestions, I'll try but I can't now, I have some urgent modification on another application. I report the result when I try. – Neo Nov 08 '20 at 11:01