CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Anwar Shaikh
Anwar Shaikh

Reputation: 104

Sqlite sorting by date column

I am storing date in ISO8601 format example 2015-04-15T10:54:14Z in sqlite table, I want youngest date from table. below are the dates in my sqlite table 

2015-04-15T10:54:14Z 
2015-04-15T10:54:115Z
2015-04-15T10:54:216Z
2015-04-15T10:54:320Z
2015-04-15T10:54:422Z

I am trying below query:

SELECT * FROM Table1 ORDER BY datetime("date_column") DESC ;

but I am not getting appropriate result.

Upvotes: 4

Views: 7588

Answers (3)

laalto
laalto

Reputation: 152867

ISO 8601 datetime stamps normalized to UTC have the nice property that the alphabetical (lexicographic) order is also temporal order.

You don't need the datetime(), you can just ORDER BY date_column DESC to sort them newest first, and you can add LIMIT 1 to get just the newest one.

Upvotes: 11

Dmila Ram
Dmila Ram

Reputation: 1074

use this method to put date column in your db:

public String getDatetime(){
    //get time and date
    Calendar c=Calendar.getInstance();
    CharSequence s  = DateFormat.format("yyyy-MM-dd HH:mm:ss", c.getTime());

    //convert it to string array

    return s.toString();
}

then use your query as you used before, cause datetime() accepts specific formatts.

Upvotes: 0

Bernardmoes
Bernardmoes

Reputation: 340

Update your query to this:

SELECT * FROM Table1 ORDER BY date_column DESC LIMIT 1

Upvotes: 1

Related Questions