javascript - Sort datetime in HTML5 Web SQL Database - Stack Overflow

admin管理员组

文章数量:1327971

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!

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!

• javascript
• html

Share Improve this question Follow edited Sep 27, 2012 at 11:57 StackFlower asked Sep 27, 2012 at 11:50 StackFlowerStackFlower 72111 gold badge1313 silver badges3030 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 5

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 remends that you use ISO-8601 canonical format (e.g. "2012-09-22") so parisons 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.

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

I found the solution.

Here is the code:

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

本文标签： javascriptSort datetime in HTML5 Web SQL DatabaseStack Overflow