CODEWITHSUNDEEP
androidsqlitesortingdatetimeandroid-sqlite
Achin
Achin

Reputation: 1252

Sqlite Sort by time not working

I am storing date format in sqlite table, I want sort by date from table.

Every record in my SQLite database contains a column which contains a date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'.

I am sharing my table structure. I am using this query to sort by datetime it not sorting by time but sort by date is working fine.

select * 
from messages_table 
where id = '444' 
order by datetime(date_time) asc

I am storing datetime as string in my below table

enter image description here

and I am getting the below wrong sorting by time output please see my date_time column in the picture, anyone guide me.

enter image description here

Upvotes: 1

Views: 426

Answers (1)

WeeHorse
WeeHorse

Reputation: 303

I have a similar problem with the TIME datatype.

If I enter time values correctly, such as 8:00 or 13:00, they are sorted as strings in an ORDER BY clause, in effect as "800" and "1300", where the string "1300" is sorted before "800" in ascending order.

One solution is to pad all times with a leading 0, so we get "0800" and "1300" which will be sorted time wise correctly.

Upvotes: 1

Related Questions