CODEWITHSUNDEEP
androidsqlite
Medard
Medard

Reputation: 1059

SQLite order by Date with format EEE MMM dd HH:mm:ss zzz yyyy

My date is being saved as a string in sqlite in the format "EEE MMM dd HH:mm:ss zzz yyyy". it is quite important that is saved this way.

However, now I need to fetch the saved data from database whilst ordering them by date starting with the newest.

String selectQuery = "SELECT  * FROM " + InteractionSchema.TABLE +
                     " interaction " + " WHERE interaction." +
                     InteractionSchema.KEY_owner +
                     " =? ORDER BY datetime(" + InteractionSchema.KEY_date +
                     ") DESC LIMIT 15";

It is not working at all, I tried using all the other SQLite date function but to no avail. I am pretty sure it has to do with the date format. Has anybody dealt with this before? (PS, it is not a duplicate I have already gone through similar questions on stackoverflow)

Upvotes: 0

Views: 1381

Answers (2)

Deepesh
Deepesh

Reputation: 533

If you want to show datetime and also want to be able to sort the data without extra processings, you can have two columns TEXT and INTEGER where you can store formatted date in TEXT column and time in mills in INTEGER column and have you data sorted based on INTEGER data. This way you can achieve both things.

Upvotes: 0

BladeCoder
BladeCoder

Reputation: 12949

You should always save dates as UTC timestamps (INTEGER) in SQLite on Android devices. This way you can easily compare and sort them and they use less storage space.

Then nothing prevents you from formatting the date back to its original format using a SimpleDateFormat.

If the timezone is important to preserve (let's say you want the dates to be always formatted according to the timezone of a specific country instead of using the default locale), then you can save the UTC offset in a separate column next to the timestamp.

Upvotes: 1

Related Questions