在HTML5 Web SQL数据库中对日期时间进行排序 [英] Sort datetime in HTML5 Web SQL Database

问题描述

我找不到解决问题的方法.

I can't find a solution for my problem.

我有一个HTML5 Web SQL数据库，其表如下:

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)");
});

当我将值添加到此数据库(表示法= dd-MM-yyyy)时，todoDate似乎已作为字符串添加到数据库中.当我使用以下查询从数据库收集并排序一些todoDate值时，这些值将以错误的顺序排序:

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";

输出:

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

我想得到以下命令:

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

我该如何实现?

我找到了str_to_date函数，但是它不起作用或者我做错了什么.

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

提前谢谢！

推荐答案

我找到了解决方案.

这是代码:

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

这篇关于在HTML5 Web SQL数据库中对日期时间进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！