Android Question ORDER BY sqlite not working right

[Douglas Farias]

Hi all.

i m working on app where i need put all info of my db on one CLV
on my DB i have a TEXT collum with the name created_at

on created_at collum i store the dates, the dates are saved with this format
2017-02-16 (yyyy-MM-dd)

the query´s i tryed use is this

Starter.sql.ExecQueryAsync("GeraTimeline", "select * from timelineMensagens ORDER BY date(created_at) ASC LIMIT 100", Null)

select * from timelineMensagens ORDER BY date(created_at) ASC LIMIT 100

select * from timelineMensagens ORDER BY created_at ASC LIMIT 100

select * from timelineMensagens ORDER BY created_at DESC LIMIT 100

select * from timelineMensagens ORDER BY datetime(created_at) ASC LIMIT 100

all this querys dont work, the order by its ignored i think.

i m tryed change the date to

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DDTHH:MM

i m tryed all this date and time formats but still now working.





Here is the result (Today = 18 , Yesterday = 17) and the first results is day 16, i really dont know why it is not in order

this result = how is my clv

LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-16
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-02-17
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-03-12
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-04-13
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-05-25
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
LOADED DATE:2017-06-15
** Activity (timeline) Pause, UserClosed = false **

someone can help me with this pls?
I just need to put the latest content in a clv
order by lastest date

thx

 

[edgar_ortiz]

Douglas,

What database are you using?

IF is Sqlite... you can use a char field for the field "created_at" and then you can use:

select * from timelineMensagens ORDER BY substr(created_at,1,10) ASC LIMIT 100

Regards,

Edgar

 

[eps]

This should definitely work - it's a staple piece of SQL.

I use the following

DateTime.DateFormat= "yyyy-MM-dd"  'set in Globals

Cursor = SQL1.ExecQuery("SELECT _id, name, enddate, display_date FROM event order by startdate")

So no date or date time.. start date is a date field in SQLite, which is in yyyy-MM-dd format.

ETA: Actually I don't think I even set a type on that field in the database.

 

[eps]

Erm, your output as indicated above is in an order - current to in the future... or do you need a greater than clause in there?

 

[mc73]

select * from timelineMensagens ORDER BY created_at desc LIMIT 100

 

[sorex]

the result seems fine since you use ascending as sort mode. you need descending when you want today to be first

Starter.sql.ExecQueryAsync("GeraTimeline", "select * from timelineMensagens ORDER BY created_at DESC LIMIT 100", Null)