在Android SQLite中处理日期的最佳方法

我在使用SQLite的Android应用程序上处理日期遇到了一些麻烦。我有几个问题:

我应该使用什么类型存储日期在SQLite(文本，整数，…)? 给出了存储日期的最佳方式，我如何使用ContentValues正确存储它? 从SQLite数据库中检索日期的最佳方法是什么? 如何在SQLite上进行sql选择，按日期排序结果?

当前回答

SQLite可以使用文本、实数或整数数据类型来存储日期。而且，无论何时执行查询，结果都使用格式%Y-%m-%d %H:% m:%S显示。

现在，如果您使用SQLite日期/时间函数插入/更新日期/时间值，实际上也可以存储毫秒。如果是这种情况，则使用格式%Y-%m-%d %H:% m:%f显示结果。例如:

sqlite> create table test_table(col1 text, col2 real, col3 integer);
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123')
        );
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126')
        );
sqlite> select * from test_table;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

现在，做一些查询来验证我们是否真的能够比较时间:

sqlite> select * from test_table /* using col1 */
           where col1 between 
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.121') and
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

您可以使用col2和col3检查相同的SELECT，将得到相同的结果。可以看到，第二行(126毫秒)没有返回。

注意BETWEEN是包含的，因此…

sqlite> select * from test_table 
            where col1 between 
                 /* Note that we are using 123 milliseconds down _here_ */
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123') and
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');

．.．将返回相同的集合。

尝试不同的日期/时间范围，一切都将按照预期进行。

如果没有strftime函数呢?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01.121' and
               '2014-03-01 13:01:01.125';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

如果没有strftime函数，没有毫秒呢?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01' and
               '2014-03-01 13:01:02';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

ORDER BY呢?

sqlite> select * from test_table order by 1 desc;
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
sqlite> select * from test_table order by 1 asc;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

工作得很好。

最后，当处理程序中的实际操作时(不使用sqlite可执行文件…)

顺便说一句:我正在使用JDBC(不确定其他语言)…来自xial的sqlite-jdbc驱动程序v3.7.2 -可能更新的版本会改变下面解释的行为… 如果您在Android上开发，则不需要jdbc驱动程序。所有SQL操作都可以使用SQLiteOpenHelper提交。

JDBC有不同的方法从数据库中获取实际的日期/时间值:日期、java.sql。Time和java.sql.Timestamp。

java.sql.ResultSet中的相关方法(显然)分别是getDate(..)、getTime(..)和getTimestamp()。

例如:

Statement stmt = ... // Get statement from connection
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_TABLE");
while (rs.next()) {
    System.out.println("COL1 : "+rs.getDate("COL1"));
    System.out.println("COL1 : "+rs.getTime("COL1"));
    System.out.println("COL1 : "+rs.getTimestamp("COL1"));
    System.out.println("COL2 : "+rs.getDate("COL2"));
    System.out.println("COL2 : "+rs.getTime("COL2"));
    System.out.println("COL2 : "+rs.getTimestamp("COL2"));
    System.out.println("COL3 : "+rs.getDate("COL3"));
    System.out.println("COL3 : "+rs.getTime("COL3"));
    System.out.println("COL3 : "+rs.getTimestamp("COL3"));
}
// close rs and stmt.

由于SQLite没有实际的DATE/TIME/TIMESTAMP数据类型，所以这3个方法的返回值就像对象初始化为0一样:

new java.sql.Date(0)
new java.sql.Time(0)
new java.sql.Timestamp(0)

因此，问题是:我们如何实际选择、插入或更新日期/时间/时间戳对象?这个问题没有简单的答案。您可以尝试不同的组合，但它们将迫使您在所有SQL语句中嵌入SQLite函数。在Java程序中定义一个实用程序类来将文本转换为Date对象要容易得多。但是请记住，SQLite将任何日期值转换为UTC+0000。

总之，尽管一般规则总是使用正确的数据类型，或者，甚至整数表示Unix时间(从epoch开始的毫秒)，我发现使用默认的SQLite格式('%Y-%m-%d %H:% m: %f'或在Java中'yyyy-MM-dd HH:mm:ss.SSS')更容易，而不是用SQLite函数使所有SQL语句复杂化。前一种方法更容易维护。

我将检查结果时使用getDate/getTime/getTimestamp在Android (API15或更好)…也许内部驱动程序与sqlite-jdbc不同…

2014-03-14 01:45:16

其他回答

最好的方法是将日期存储为使用Calendar命令接收的数字。

//Building the table includes:
StringBuilder query=new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_DATETIME+" int)");

//And inserting the data includes this:
values.put(COLUMN_DATETIME, System.currentTimeMillis());

Why do this? First of all, getting values from a date range is easy. Just convert your date into milliseconds, and then query appropriately. Sorting by date is similarly easy. The calls to convert among various formats are also likewise easy, as I included. Bottom line is, with this method, you can do anything you need to do, no problems. It will be slightly difficult to read a raw value, but it more than makes up that slight disadvantage with being easily machine readable and usable. And in fact, it is relatively easy to build a reader (And I know there are some out there) that will automatically convert the time tag to date as such for easy of reading.

值得一提的是，这个函数的值应该是long，而不是int。sqlite中的整数可以表示很多东西，从1-8字节的任何东西，但对于几乎所有日期来说，64位或长位是可行的。

编辑:正如评论中所指出的，如果要这样做，必须使用cursor.getLong()来正确地获取时间戳。

2012-12-04 01:38:02

通常(与我在mysql/postgres中所做的一样)，我将日期存储在int(mysql/post)或文本(sqlite)中，以时间戳格式存储它们。

然后，我将它们转换为日期对象，并根据用户时区执行操作

2011-09-09 15:03:34