Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                Your query is correct and you do need the datetime function, because ISO8601 strings are NOT in lexicographic order. They can include a variable amount of characters for the fractions of a second plus a time zone. BUT your date strings are NOT ISO8601 formatted. 10:43:115 is not a valid time (115 seconds?). For fractions of a second you need a dot like: 10:43:11.5. I know this is old, but it might help someone who stumbles over this.
– Toxiro
                Oct 27, 2021 at 14:16
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.
                Don't know why this was down voted. This is real useful information. And it works like a charm since I just implemented it. No mocking with Compartors. Thank you @IaaIto
– EdGs
                Dec 9, 2015 at 17:20
                "ISO 8601 datetime stamps have the nice property that the alphabetical (lexicographic) order is also temporal order." Only if date and time are giving in UTC without a timezone offset at the end.
– balu
                Aug 26, 2016 at 17:21
    //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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.