Retreiving sorted data by date with Room and Paging 3

Question

When I get the list that is stored I get this pattern

["12/08/2021", "12/07/2021", "12/08/2020", "11/07/2021", "11/05/2021"]

As you see the month and day are being sorted, but it looks like the year is not being read.

below is Query that I'm using with Room

  @Query("SELECT * FROM timeSheet ORDER BY date DESC")
    fun timeSheetsByDate(): PagingSource<Int, TimeSheet>

Here is a video of it

I then tried with the @TypeConverters,

  @Query("SELECT * FROM timeSheet ORDER BY date(date) DESC")
    fun timeSheetsByDate(): PagingSource<Int, TimeSheet>

@Entity(tableName = "timeSheet")
data class TimeSheet(
    @PrimaryKey var id: String = "",

    @ColumnInfo(name = "date")
    @TypeConverters(TimestampConverter::class)
    var dateSubmitted: String? = null
 
)

class TimestampConverter {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return if (value == null) null else Date(value)
    }

    @TypeConverter
    fun dateToTimestamp(dateString: String?): Date {

        val format = TimeFormatter()

        val date = Calendar.getInstance()
        date.time = format.date(dateString)!!

        return date.time
    }
}

but for some reason when refreshing the data for the second time it doesn't show the values from the first page, so I have to scroll down until all the data is fully loaded and then when I scroll up again I'm able to see the values from the first page.

Here is a video of it

Answer 1

You could use

@Query("SELECT * FROM timeSheet ORDER BY substr(date,7,4)||substr(date,4,2)||substr(1,2) DESC")

However, that is awkward.

It is strongly suggested that you do not use dd/mm/yyyy to store the timestamp but instead store it in a recognised format e.g. yyyy-mm-dd

You can then sort and compare dates and also make use of the date and time functions that are built into SQLite.

Your TypeConverters will not be used by Room (and would not work). Room expects a TypeConverter to convert from an unknown type to an underlying type known/handled by Room.

Having var dateSubmitted: String? = null means that the type is String and room can handle storing a String without it being converted, there is no need to convert it and therefore Room won't convert it.

If however you had var dateSubmitted: Date? = null

Then Room does not know how to handle the Date type and will then demand a TypeConverter to convert the Date type into a type that Room can handle. It would also need a second TypeConverter to be able to convert the type stored in the database into a Date type when extracting data.

If you want to use a recognised format, say YYYY-MM-DD then a String can hold that and no TypeConverter is required as far as Room is concerned. However, you need to provide the date as a string in that format for use by Room and handle Room returning the TimeSheet with YYYY-MM-DD in the dateSubmitted member/field.

I would suggest considering the following demonstration of TypeConverters :-

@Entity(tableName = "timeSheet")
@TypeConverters(TimestampConverter::class) /* due to scoping TypeConveters needs to be here or at @Database level */
data class TimeSheet(
    @PrimaryKey var id: String = "",
    @ColumnInfo(name = "date")
    var dateSubmitted: Date? = null,
)
{
    companion object {
        val format = SimpleDateFormat("yyyy-MM-dd")
    }
}

class TimestampConverter {
    @TypeConverter
    fun fromDateToString(value: Date): String {
        return format.format(value)
    }
    @TypeConverter
    fun toDateFromString(value: String) : Date {
        return format.parse(value)
    }
}

and your original query would then work.

For example using the above along with :-

@Dao
interface AllDao {

    @Insert
    fun insert(timeSheet: TimeSheet): Long
    @Query("SELECT * FROM timeSheet ORDER BY date DESC")
    fun getTimeSheetsOrderedByDateSubmittedDesc(): List<TimeSheet>
}

and :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()


        /* ["12/08/2021", "12/07/2021", "12/08/2020", "11/07/2021", "11/05/2021"] */
        dao.insert(TimeSheet("A",TimeSheet.format.parse("2021-08-12")))
        dao.insert(TimeSheet("B",TimeSheet.format.parse("2021-07-12")))
        dao.insert(TimeSheet("C",TimeSheet.format.parse("2021-05-11")))
        dao.insert(TimeSheet("D",TimeSheet.format.parse("2020-08-12")))
        dao.insert(TimeSheet("E",TimeSheet.format.parse("2021-07-11")))

        for (t: TimeSheet in dao.getTimeSheetsOrderedByDateSubmittedDesc()) {
            Log.d("RESULTINFO","ID = ${t.id} DateSubmitted = ${t.dateSubmitted} or ${TimestampConverter().fromDateToString(t.dateSubmitted!!)}")
        }
    }
}

Then the resultant output is :-

2021-12-09 09:20:55.299 D/RESULTINFO: ID = A DateSubmitted = Thu Aug 12 00:00:00 GMT+10:00 2021 or 2021-08-12
2021-12-09 09:20:55.299 D/RESULTINFO: ID = B DateSubmitted = Mon Jul 12 00:00:00 GMT+10:00 2021 or 2021-07-12
2021-12-09 09:20:55.300 D/RESULTINFO: ID = E DateSubmitted = Sun Jul 11 00:00:00 GMT+10:00 2021 or 2021-07-11
2021-12-09 09:20:55.300 D/RESULTINFO: ID = C DateSubmitted = Tue May 11 00:00:00 GMT+10:00 2021 or 2021-05-11
2021-12-09 09:20:55.300 D/RESULTINFO: ID = D DateSubmitted = Wed Aug 12 00:00:00 GMT+10:00 2020 or 2020-08-12