get today data from room database

Question

get dates data from room database

i have created task db class as below

   @ColumnInfo(name = "task_start_date") var taskStartDate: Date?,
@ColumnInfo(name = "task_end_date") var taskEndDate: Date?,
@ColumnInfo(name = "task_never_end") var taskNeverEnd: Boolean?
       @PrimaryKey
    var tid: Long?,
    @ColumnInfo(name = "task_title") var TaskTitle: String?

I do insert data with below function

    fun getTodayDate(): Date {
    val calendar = Calendar.getInstance()
    return GregorianCalendar(
        calendar.get(Calendar.YEAR),
        calendar.get(Calendar.MONTH),
        calendar.get(Calendar.DATE),
        calendar.get(Calendar.HOUR_OF_DAY),
        calendar.get(Calendar.MINUTE),
        calendar.get(Calendar.SECOND)
    ).time
}

fetching data with below codes and params todayDate = above functio getTodayDate()

@Query("select * from task where task_start_date =:todayDate ")
fun getTodayTask(todayDate: Date): List

with above implementation I m getting 0 result I assume room is converting date to long and stores and also compare with long value so this thing never gonna match so can u please help me out how to get task that only matched dates not time

example

I have inserted data for 11-09-2022 12:00:00 PM,11-09-2022 1:00:00 PM,11-09-2022 2:00:00 PM,11-09-2022 3:00:00 PM,11-09-2022 4:00:00 PM in the form of Date object not string. now I want to fetch all task that match only date like 11-09-2022 not time

Converter

class TimestampConverter {


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

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

MikeT · Answer

I believe that java times when converted to long include milliseconds so you need to strip of the milliseconds,seconds,minutes and hours.

You can divide both sides of the argument by 1000 (milliseconds) * 60 (seconds per minute) * 60 (minutes per hour) * 24 (hours per day) (i.e. 86,400,000 milliseconds per day) and this will strip of the time and the comparison will be at the date level.

So try:-

@Query("select * from task where task_start_date / 86400000 =:todayDate / 86400000 ")

Regarding the comment:-

how could this possible ? ex : I got milliseconds 1662899658575 and divide by 86400000. so result would be 19246.5238261.

Here is a demonstration that shows/explains using SQLite (which room is a wrapper around).

DROP TABLE IF EXISTS example;
CREATE TABLE IF NOT EXISTS example (timestamp);
INSERT INTO example VALUES(1662899658575);
SELECT 
    timestamp, /* The actual stored value */
    strftime('%Y-%m-%d %H:%M:%S',timestamp/1000,'unixepoch') AS date, /* Using SQLite strftime function to extract the exact date time (less milliseconds) */
    timestamp / (24 * 60 * 60 * 1000) AS `daysSince1:1:1970`, /* the number of days since 1/1/1970 (based upon the unix date/time) i.e. the date part only*/
    (timestamp / (86400000 /* drop millisecs*/ * 365 /* drop years*/)) + 1970 /* add to start */ AS year /* The year factoring in unix datetime starting 1/1/1970 */
    FROM example
;
DROP TABLE IF EXISTS example; /* Cleanup Environment*/

When run the columns output are:-

As can be seen

- 1662899658575 / 86400000 = 19246 which equates to the date as it is the number of days since 1/1/1970 and thus represents the date 2022-09-11 according to unix datetime

get today data from room database

1 Answers