Android Kotlin Room query to select record from last seven days

Question

I am writing a report that would fetch data from Room database for last 7 days. Here is my simplified application structure.

Entity Class:
@Entity(tableName = "cleaning")
data class Cleaning(
    @PrimaryKey(autoGenerate = true)
    val id: Long,
    val task: String,
    val timestamp: Date
)

TypeConverter: class DateTypeConverter {

@TypeConverter
fun fromTimeStampToDate(timestamp: Long?): Date? {
    return timestamp?.let {
        Date(it)
    }
}

@TypeConverter
fun fromDateToTimeStamp(date: Date?): Long? {
    return date?.time?.toLong()
}

Data Access Object Class Extract:

@Query("SELECT * from cleaning WHERE (timestamp=Calendar.getInstance().time - 7) ORDER BY id DESC")
fun readSevenDaysData(): LiveData<List<CleaningRecord>>

The problem is (timestamp=Calendar.getInstance().time - 7). I do not know how to give range to extract data for last 7 days when I have stored date using Calendar.getInstance().time

Any guidance in the right direction is much appreciated.

Answer 1

I believe that the following will do what you want :-

@Query("SELECT * from cleaning WHERE CAST((timestamp / 1000) AS INTEGER) BETWEEN strftime('%s','now','-7 days') AND strftime('%s','now')  ORDER BY id DESC;")

That is the timestamp is stored down to the millisecond so dividing by 1000 reduces the value to seconds.

• strftime('%s' .... returns the time in seconds

• 'now' returns the current datetime

• '-7 days' modifies the the value accordingly

  • see https://sqlite.org/lang_datefunc.html for more comprehensive information.

• BETWEEN is equivalent to >= AND <=

  • see https://sqlite.org/lang_expr.html#the_between_operator

• CAST is to ensure that like for like (integer values) are being compared.

  • see https://sqlite.org/lang_expr.html#cast_expressions

SQLite has no understanding of what the expression (timestamp=Calendar.getInstance().time - 7) means, even if it did it would only get the data that EXACTLY matches the time less 7 days down to the millisecond.

The was tested using your code plus or overridden by the following:-

@Dao
interface Extract {

    @Insert
    fun insert(cleaning: Cleaning)

    @Query("SELECT * from cleaning WHERE CAST((timestamp / 1000) AS INTEGER) BETWEEN strftime('%s','now','-7 days') AND strftime('%s','now')  ORDER BY id DESC;")
    fun readSevenDaysData(): List<Cleaning>
}

• obviously the SQL has been changed,

• additionally LiveData has not been used for brevity and convenience,

• and just Cleaning objects have been returned as CleaningRecord objects were not provided.

  @Database(entities = [Cleaning::class], version = 1, exportSchema = false) @TypeConverters( value = [DateTypeConverter::class]) abstract class TheDatabase: RoomDatabase() { abstract fun getExtractDao(): Extract

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
  

  }

• The @Database annotated class, along with a getInstance function, noting that again for brevity and convenience allowMainThreadQueries has been used.

Finally an Activity that adds some data and the extracts data using the respective Query:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: Extract
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getExtractDao()

        dao.insert(Cleaning(100,"TASK001",Calendar.getInstance().time))
        dao.insert(Cleaning(200,"TASK002",Calendar.getInstance().time))

        for (c: Cleaning in dao.readSevenDaysData()) {
            Log.d("DBINFO","Cleaning Task = ${c.task} ID = ${c.id} ${c.timestamp.time}")
        }
    }
}

resulting in :-

D/DBINFO: Cleaning Task = TASK002 ID = 200 1647400757156
D/DBINFO: Cleaning Task = TASK001 ID = 100 1647400757125

AppInspection shows :-

Additionally using App Inspection the query

SELECT *, timestamp /1000 AS timetosecond,  strftime('%s','now','-7 days') AS fromtime,  strftime('%s','now') AS totime   from cleaning WHERE CAST((timestamp / 1000) AS INTEGER) BETWEEN strftime('%s','now','-7 days') AND strftime('%s','now')  ORDER BY id DESC

was run to further show a) how useful App Inspection can be and b) what the various parts do the query do with the actual data.

i.e. the above resulted in :-