Android Room get all times of todays insert

Question

I'm trying to save data when user starts a specific activity and when he ends it and I want to calculate how many hours the user spent this day/week/month

I'm having a problem to extract this information.

This is my table:

    @Entity(tableName = "daily_activity_time_table")
     data class TimeTrack(


        @PrimaryKey(autoGenerate = true)
        var activityId: Long = 0L,

        @ColumnInfo(name = "start_time")     
        val startTimeMilli: Date = Date(System.currentTimeMillis()),  //i didnt find a better way to save date and time

        @ColumnInfo(name = "end_time")
        var endTimeMilli: Date = startTimeMilli,

        @ColumnInfo(name = "quality_rating")
        var sleepQuality: Int = -1
)

and I need a query to get the sum of today hours for example, I tried doing something like this:

@Query("SELECT SUM(end_time-start_time) from daily_activity_time_table where start_time = date('now') ")
        fun getTodayTime(): Long

I'm able to print to the screen the date and time with this convertor:

@SuppressLint("SimpleDateFormat")
fun convertLongToDateString(systemTime: Long): String {
    return SimpleDateFormat("EEEE MMM-dd-yyyy' Time: 'HH:mm")
        .format(systemTime).toString()
}

but cant find a way to print all the hours that the user used the activity today

Answer 1

I have made a small and very simplified project showing how to extract data from Room by a range of dates (represented as milliseconds since epoch)

https://github.com/or-dvir/RoomDate

Please note that this is NOT the proper way to set up Room database, and that this method has its limits (for example it does not take time zones into account).