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.

CodePudding user response：

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

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