Sort datetime in HTML5 Web SQL Database

Question

I can't find a solution for my problem.

I have an HTML5 Web SQL Database with a table like this:

db.transaction(function(tx) {   
        tx.executeSql("CREATE TABLE IF NOT EXISTS todo " +
        "(todoId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
        "note VARCHAR(100) NOT NULL, " + 
        "state VARCHAR(100) NOT NULL, " + 
        "todoDate DATETIME NOT NULL)");
});

When I add values to this database (notation = dd-MM-yyyy), it looks like the todoDate is added as a string to the database. When I collect and sort some todoDate values from the database with the following query, the values are sorted in the wrong order:

sql = "select * FROM todo order by todoDate asc";

Output:

             todoId -     note        - state         - todoDate  
             3      -     blabla      - someinfo     - 01-01-2013
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012

I would like to get the following order:

             todoId -     note        - state         - todoDate  
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012
             3      -     blabla      - someinfo     - 01-01-2013

How can I achieve this?

I found the function str_to_date but it doesn't work or I did something wrong.

Thanks in advance!

score 5 · Answer 1 · answered Nov 14 '12 at 20:22

The HTML5 Web SQL Database is actually SQLite under the hood. SQLite doesn't have a DATETIME type. If you send them Strings, it'll store them as Strings. SQLite recommends that you use ISO-8601 canonical format (e.g. "2012-09-22") so comparisons work as expected. SQLite provides a bunch of useful date time functions for working with date-time values in the database. See here. Alternatively, you can store milliseconds but I personally prefer to store the strings because they are human readable which helps with debugging.

score 0 · Answer 2 · answered Sep 27 '12 at 12:15

0

Like that query you can fire :
select * from (select column1,...,convert(date,column) from table) as a order by dateColumn

answered Sep 27 '12 at 12:15

Vikram Jain

5,498
1
19
31

I get an error when I change my query to: sql = "select * from (select todoDate, note convert(date, todoDate) from todo) order by todoDate"; What do I wrong? – StackFlower Sep 27 '12 at 12:24

score 0 · Accepted Answer · answered Sep 27 '12 at 13:59

0

I found the solution.

Here is the code:

SELECT * FROM tablename ORDER by substr(dateColumn,7)||substr(dateColumn,4,2)||substr(dateColumn,1,2);

answered Sep 27 '12 at 13:59

StackFlower

691
1
13
29

Sort datetime in HTML5 Web SQL Database

3 Answers3

Linked