how can i get today in Room database queries

Question

Hi I have a query where I order the list of products based on the product_end_date. I want to get only the list of products whose end date is greater than or equal to today, how can i do that in Room

here is my query

@Query("SELECT * FROM product_data_table ORDER BY product_end_date ASC")
fun getAllProductsOrderByEndDate(): LiveData<List<Product>>

I tried this but did not work

@Query("SELECT * FROM product_data_table WHERE product_end_date >= date('now') ORDER BY product_end_date ASC")
fun getAllProductsOrderByEndDate(): LiveData<List<Product>>

Entity

@Entity(tableName = "product_data_table")
data class Product(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "product_id")
    var id: Int,
    @ColumnInfo(name = "product_name")
    var name: String,
    @ColumnInfo(name = "product_catagory")
    var catagory: String,
    @ColumnInfo(name = "product_end_date")
    var end_date: Date
)

I am not sure how to use todays date in where clause

please suggest how to fix this

your help is much appreciated

Thanks R

Answer 1

Store the end_date in Entity class as Long in Unix time

@Entity(tableName = "product_data_table")
data class Product(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "product_id")
    var id: Int,
    @ColumnInfo(name = "product_name")
    var name: String,
    @ColumnInfo(name = "product_catagory")
    var catagory: String,
    @ColumnInfo(name = "product_end_date")
    var end_date: Long // Changed the type from Date to Long
)

When inserting the product pass Date().time for end_date

Now when querying, get the Unix time of today and pass it as a parameter for the query function. Something like this:

@Query("SELECT * FROM product_data_table WHERE product_end_date >= :endDate")
fun getAllProductsOrderByEndDate(endDate: Long): List<Product>