how to get today ,past or future date data from room database in android?

below is the model class and there is a task_date field I have taken with Date object.

Model class

    @Entity
data class Task(
    @PrimaryKey
    val tid: Long?,
    @ColumnInfo(name = "title") val title: String?,
    @ColumnInfo(name = "task_date") val task_date: Date?,
    @ColumnInfo(name = "task_hour") val task_hour: Int?,
    @ColumnInfo(name = "task_minute") val task_minute: Int?,
    @ColumnInfo(name = "task_cat") val task_cat: String?,
    @ColumnInfo(name = "task_repeat") val task_repeat: String?,
) {
    override fun toString(): String {
        return "Task(tid=$tid, title=$title, task_date=$task_date, task_hour=$task_hour, task_minute=$task_minute, task_cat=$task_cat, task_repeat=$task_repeat)"
    }
}

Below There is query code

i am passing Date() today date to get today inserted data list

 @Query("SELECT * FROM task WHERE task_date = :targetDate")
fun getUpcomingTask(targetDate: Date): List<Task>

Data insertion code is here

  val task = Task(
                        Utils.getUniqueId(),
                        bindingActivity.inputTaskTitle.text.toString(),
                        Date(),
                        selectedHour, selectedMinute,
                        bindingActivity.mySpinnerDropdown.text.toString(),
                        Constant.REPEAT.NONE
                    )

Converter Class

class Converters {

    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }

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

}

And the final one DataBase class

 @Database(entities = [Task::class], version = 1, exportSchema = false)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

I have shown the code implementation I never worked with date object so need your help to learn this date implementation Moreover , I also want to retrieve data like upcoming data and past data .Please also give your valuable advice to learn more with date

.

Thank You.

CodePudding user response：

Your issue is with exactly what is being stored and passed due to the conversion of the date to a long.

That is the long is accurate down to a millisecond, so when query for tasks that equals the "date" it doesn't know that you only want the date part so it will be virtually impossible to get a task of the very millisecond.

Consider the following data (based upon using your code) for 3 tasks, it will look like:-

• ignore all but the task_date column
• ALL 3 rows were inserted immediately after each other at 14:10 on 2022-07-08, yet they all have different values as the value includes the milliseconds.

So SELECT * FROM task WHERE task_date = :targetDate will not probably never get any records.

Todays

However consider this query SELECT * FROM task WHERE task_date / (1000 /* drop millis*/ * 60 /* drop seconds */ * 60 /* drop minutes */ * 24 /* drop hours */) = :targetDate / 86400000

• Note comments explain how the 86400000 was derived (it's the same value applied to both sides of the comparison). i.e. stripping of every but the date from the stored long value.

Future and Past

For Future and the past it wouldn't matter about the milliseconds so you could then use SELECT * FROM task WHERE task_date > :targetDate (for Future) and SELECT * FROM task WHERE task_date < :targetDate (for past)

CodePudding user response：

You use converter for Date, it means it would be transformed to Long and store as Long in you Database. That being said, you can use >, < operator in you sql query. e.g.

For past

@Query("SELECT * FROM task WHERE task_date < :date")
fun getPastTasks(date: Date): List<Task>

For future

@Query("SELECT * FROM task WHERE task_date > :date")
fun getFutureTasks(date: Date): List<Task>