我在使用SQLite的Android应用程序上处理日期遇到了一些麻烦。 我有几个问题:
我应该使用什么类型存储日期在SQLite(文本,整数,…)? 给出了存储日期的最佳方式,我如何使用ContentValues正确存储它? 从SQLite数据库中检索日期的最佳方法是什么? 如何在SQLite上进行sql选择,按日期排序结果?
我在使用SQLite的Android应用程序上处理日期遇到了一些麻烦。 我有几个问题:
我应该使用什么类型存储日期在SQLite(文本,整数,…)? 给出了存储日期的最佳方式,我如何使用ContentValues正确存储它? 从SQLite数据库中检索日期的最佳方法是什么? 如何在SQLite上进行sql选择,按日期排序结果?
当前回答
通常(与我在mysql/postgres中所做的一样),我将日期存储在int(mysql/post)或文本(sqlite)中,以时间戳格式存储它们。
然后,我将它们转换为日期对象,并根据用户时区执行操作
其他回答
我更喜欢这个。这不是最好的方法,但却是一个快速的解决方案。
//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_CREATION_DATE+" DATE)");
//Inserting the data includes this:
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
values.put(COLUMN_CREATION_DATE,dateFormat.format(reactionGame.getCreationDate()));
// Fetching the data includes this:
try {
java.util.Date creationDate = dateFormat.parse(cursor.getString(0);
YourObject.setCreationDate(creationDate));
} catch (Exception e) {
YourObject.setCreationDate(null);
}
通常(与我在mysql/postgres中所做的一样),我将日期存储在int(mysql/post)或文本(sqlite)中,以时间戳格式存储它们。
然后,我将它们转换为日期对象,并根据用户时区执行操作
"SELECT "+_ID+" , "+_DESCRIPTION +","+_CREATED_DATE +","+_DATE_TIME+" FROM "+TBL_NOTIFICATION+" ORDER BY "+"strftime(%s,"+_DATE_TIME+") DESC";
您可以使用文本字段在SQLite中存储日期。
以UTC格式存储日期,默认情况下,如果使用datetime('now') (yyyy-MM-dd HH:mm:ss)将允许按日期列排序。
从SQLite中检索日期作为字符串,然后使用Calendar或android.text.format.DateUtils.formatDateTime方法将它们格式化/转换为所需的本地区域化格式。
这是我使用的一个区域化格式化器方法;
public static String formatDateTime(Context context, String timeToFormat) {
String finalDateTime = "";
SimpleDateFormat iso8601Format = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
Date date = null;
if (timeToFormat != null) {
try {
date = iso8601Format.parse(timeToFormat);
} catch (ParseException e) {
date = null;
}
if (date != null) {
long when = date.getTime();
int flags = 0;
flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;
finalDateTime = android.text.format.DateUtils.formatDateTime(context,
when + TimeZone.getDefault().getOffset(when), flags);
}
}
return finalDateTime;
}
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不同…