Retrieving the data from room database using date string as earlist first

Question

I have a database with 5 columns and one of the column is date string of the format "dd/mm/yyyy hh:mm:ss". I want to fetch the data from DB based on the earliest date first.

Below is the query that I have used :

  @Query("SELECT * from database_table ORDER BY dateTime('%d/%m/%Y %H:%M:%S',date) DESC")
    fun getAllTransactions(): List<DataBaseItem>

With the above query I am receiving the data as it is and not as per the specified format. Please let me know how to achieve this without changing the database structure.

Answer 1

The SQLite datetime function expects the first parameter to be a date, and the subsequent parameters to be valid modifiers as per:-

• date(time-value, modifier, modifier, ...) see https://sqlite.org/lang_datefunc.html#overview

You are instead passing a format as the first parameter and the value from the column as a modifier (which it isn't).

The datetime returns a specific format i.e. YYYY-MM-DD HH:MM:SS.

IF you want a date to be returned as dd/mm/yyyy then you would have to use the strftime function so strftime('%d/%m/%Y %H:%M:%S',date). However, date MUST be in a format recognised by SQLite.

If the date is stored as dd/mm/yyyy then it is not a recognised format.

• see https://sqlite.org/lang_datefunc.html#time_values

As such you need to format it accordingly to suit the ORDERing so

ORDER BY substr(date,7,4)||substr(date,4,2)||substr(date,1,2) DESC

• see https://sqlite.org/lang_corefunc.html#substr

• NOTE this assumes 1st March 2020 is 01/03/2022 that is leading 0's. If there are no leading 0's then the reformatting is more complex.