How to search for a specific date after converting the date into a timestamp and storing it in SQLite

Question

I get timeInMillis of calendar, and store it into SQLite.

My code is as follows:

fun insertExerciseModel(exerciseModel: ExerciseModel) {
    val exerciseContentValue = ContentValues()
    ...
    exerciseContentValue.put(Contract.ExerciseEntry.CALENDAR, exerciseModel.calendar.timeInMillis)
    ...
    writableDatabase.insert(
        Contract.ExerciseEntry.TABLE_NAME_EXERCISE,
        null,
        exerciseContentValue
    )

    ...
}

I can search all row from table and order by calendar.

SELECT * FROM Contract.ExerciseEntry.CALENDAR ORDER BY calendar

However, the data is INTEGER type and it is timeInMillis.

How do I search for a specific date like January 8, 2020?

Or what command can I give after WHERE?

Answer 1

You may use the DATETIME function to convert your table's milliseconds since epoch to an ISO datetime. Then, just compare that against the date literal you want:

SELECT *
FROM Contract.ExerciseEntry.CALENDAR
WHERE
    DATETIME(ROUND(calendar / 1000), 'unixepoch') >= '2020-01-08' AND
    DATETIME(ROUND(calendar / 1000), 'unixepoch') <  '2020-01-09'
ORDER BY
    calendar;