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!

Larry Maccherone · Accepted Answer · 2012-11-14 20:22:55Z

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 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.

answered Nov 14, 2012 at 20:22

Larry Maccherone

9,5433 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikram Jain · Accepted Answer · 2012-09-27 12:15:15Z

0

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

answered Sep 27, 2012 at 12:15

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

1 Comment

StackFlower Over a year ago

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 · Accepted Answer · 2012-09-27 13:59:25Z

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, 2012 at 13:59

StackFlower

7231 gold badge13 silver badges30 bronze badges

Collectives™ on Stack Overflow

Sort datetime in HTML5 Web SQL Database

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related