How to use this UserDao with Date?

Question

The app i'm trying to do is based retrieving current day, week and month data from room database, so my entity only contains id and date, but in an older asked question there was an answer like this:

Date Converter:

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

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

UserDao:

@Dao
interface UserDao {
    @Query("SELECT * FROM user WHERE birthday BETWEEN :from AND :to")
    fun findUsersBornBetweenDates(from: Date, to: Date): List<User>
}

When i am using it i don't know in (from: Date, to: Date) what to put in Date and retrieve data. Is there any tutorial or any help online that i missed and might help me?

And please i'm trying to learn and understand how database and date works, so any help counts.

Answer 1

The problem is ROOM doesn't understand Date directly that is why you have to use a type converter. Your type converter is Long, so you have Long stored in the DB as Date. So what you are doing there is writing: select from this Long equals to Date up to this other Long equal to Date. Room doesn't know what to do.

You have to pass a Long in your query and it will work. You can do something like this:

@Transaction
fun findUsersBornBetweenDates(from: Date, to: Date): List<User> {
    //convert dates to long
    return findUsersBornBetweenDates(fromLong, toLong)
}

@Query("SELECT * FROM user WHERE birthday BETWEEN :from AND :to")
fun findUsersBornBetweenDates(from: Long, to: Long): List<User>

The caveat of @Transaction is that it doesn't work for LiveData so you would have to transform your Date to Long before reaching the DAO.

A very common thing to solve this problem is to re-utilize the fromTimestamp and dateToTimestamp.

If you really want to see what your DB has stored, I recommend you use the App Inspection tool on Android Studio. Is an integrated DB inspector that allows you to see your tables and perform SQL statements. That way you can see firsthand what is the actual value of the dates in the DB.