CODEWITHSUNDEEP
androidsqliteandroid-studio
dustblue
dustblue

Reputation: 551

Sort a column with string format dd/MM/yyyy in SQLite (Android)

I have an SQLite database in an Android project with a Date column that stores the date-time as String in dd-mm-yyyy HH-mm-ss format. I need to sort it based on the descending order of the date. Or, convert it to the standard yyyy-MM-dd HH-mm-ss format and then sort it.

The general ORDER BY DATETIME(coulmn_name) doesnt work.

NOTE:

This is not a duplicate question, other answers advice to change the database schema (Which is not possible, because I have data stored already)

Upvotes: 0

Views: 467

Answers (1)

Kushan
Kushan

Reputation: 5984

I would like to suggest an alternative approach to the one you are taking. I personally ran into the same issue and solved it by not using a string date at all.

Instead i converted the date to epoch milliseconds ie unix timestamp and saved that. Then a sort is a simple order by the timestamp.

You can use the following approach:

SimpleDateFormat sdf = new SimpleDateFormat ("dd-MM-yyyy HH:mm:ss");

Date currentDate = sdf.parse(yourdatestring);

//Get the calendar in the time zone you need, generally it works off the bat with the default time zone
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("yourtz"));

cal.setTime(currentDate);

//Get the milliseconds since epoch time
long millis = cal.getTimeInMillis();

You can save this timestamp and easily sort it. It'll be more accurate and easy to use than a string and potentially gives you the ability to handle different time zones.

You can retrieve the date by setting this timestamp directly in the calendar and getting a date from it

cal.setTimeInMillis(timestamp).getTime();

Hope this helps

Upvotes: 2

Related Questions