CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Moritz L.
Moritz L.

Reputation: 177

How can I sort a cursor or sqlite database by date with different formats?

I'm trying to sort my data by increasing date on the list View. Depending on the user's settings, the saved dates are followed as following DD.MM.YYYY or MMM DD, YYYY. Right now the order is set to "date ASC".

Cursor cursor = getContentResolver().query(EventEntry.CONTENT_URI,
           projection, null, null, "date ASC");

So as I sort by ascending date, 26.07.2019 would be displayed above 31.03.2019 despite being later. In the american format, the sorting doesn't work anyways as for example Apr is always shown above any other month. I'd like to support both date formats for a better user experience.

Is there any way to sort the dates in the cursor or the database in the correct order?

Upvotes: 0

Views: 367

Answers (2)

forpas
forpas

Reputation: 164154

This will change the format of the date to YYYYMMDD in the order by argument: 

Cursor cursor = getContentResolver().query(
    EventEntry.CONTENT_URI,
    projection, null, null, 
    "substr(date, 7) || substr(date, 4, 2) || substr(date, 1, 2) ASC");

But the problem of date comparing will remain.
The best thing to do is either change the format of the dates to YYYY-MM-DD or store the dates as integers (epoch milis).

Upvotes: 3

Csaba Farkas
Csaba Farkas

Reputation: 882

Try converting your date to timestamp before sorting.

Cursor cursor = getContentResolver().query(EventEntry.CONTENT_URI,
           projection, null, null, "strftime('%s', date) ASC");

Upvotes: 0

Related Questions