Order Room results by Date as String

Question

I'm working on an app where Dates are entered in the Room database as Strings, with a SimpleDateFormat fixed to '%d/%m/%Y %H:%M:%S' (e.g. "26/12/2018 10:33:31").

Without changing the structure of the database, I'd like to query some results, ordered by date. This should probably look like this (inside a Dao):

@Query("SELECT * FROM results ORDER BY stringToDate(startTime) DESC LIMIT :count")
List<Results> getLast(int count);

Unfortunately, this throws a compile error: "no such function: stringToDate", no matter if the function exists, even if it is annotate as TypeConverter in due form.

Moreover, since String is accepted as a Room type, I'm not event sure I could use TypeConverter here to begin with.

Is there any way I could order by Date as String without modifying the database structure ?

Answer 1

Try:

@Query("SELECT * FROM results ORDER BY strftime('%Y-%d-%m-%Y', startTime) DESC LIMIT :count")

Make sure that the format passed to strftime match your time format.

For reference formats can be found here: https://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

Answer 2

Can you try changing stringToDate(startTime) to datetime(startTime)?

Can you provide a sample of startTime object?