CODEWITHSUNDEEP
androiddatekotlinandroid-roomsimpledateformat
BRDroid
BRDroid

Reputation: 4388

Room - order by date stored in string

I am having a products table in my room database which has a date field stored as a String. and I want get the data from that table order by date field. I tried the following but does not work, can you suggest how to fix this please.

date format which is stored in the database

18 Nov 2020 val dateFormat = SimpleDateFormat("dd MMM yyyy")

query I have is

    @Query("SELECT * FROM product_data_table ORDER BY strftime('%Y-%d-%m-%Y', product_end_date)")
        fun getAllProductsOrderByEndDate(): LiveData<List<Product>>

Entity table

@Entity(tableName = "product_data_table")
data class Product(

 @ColumnInfo(name = "product_end_date")
    var end_date: String,
)

Thanks R

Upvotes: 0

Views: 1132

Answers (1)

Viktor
Viktor

Reputation: 656

I implemented ordering by date like that:

DAO:

//depends on your needs you can delete "DESC", it reverses ordering
@Query("SELECT * FROM Folders ORDER BY date DESC")
fun getFoldersLV(): LiveData<List<Folders>>

Adding date to database:

//DateTime - my own converter class
// and you can format date as you wish(for example "yyyy/MM/dd")
// but don't use reversed ordering of date, because you won't be able to order it
val currentDate: String = DateTime.dateToString(DateTime.getCurrentDateTime(), "yyyy/MM/dd HH:mm:ss")

DateTime class:

fun getCurrentDateTime(): Date {
    return Calendar.getInstance().time
}

fun dateToString(date: Date, format: String, locale: Locale = Locale.getDefault()): String {
    val formatter = SimpleDateFormat(format, locale)
    return formatter.format(date)
}

//I need this class to get date as Date type again for some actions, but I think you don't need it
fun stringToDate(dateString: String?, format: String = "dd/MM/yyyy HH:mm", locale: Locale = Locale.getDefault()): Date {
    val formatter = SimpleDateFormat(format, locale)
    return formatter.parse(dateString)
}

Based on this you can rewrite your date operations.

Upvotes: 0

Related Questions