我在使用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不同…
其他回答
我更喜欢这个。这不是最好的方法，但却是一个快速的解决方案。
//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);
}
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不同…
在SQlite DB中存储日期的最佳方法是存储当前的datetimemillisseconds。下面是执行so_的代码片段
获取datetimemillisseconds
public static long getTimeMillis(String dateString, String dateFormat) throws ParseException {
/*Use date format as according to your need! Ex. - yyyy/MM/dd HH:mm:ss */
String myDate = dateString;//"2017/12/20 18:10:45";
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);
Date date = sdf.parse(myDate);
long millis = date.getTime();
return millis;
}
在数据库中插入数据
public void insert(Context mContext, long dateTimeMillis, String msg) {
//Your DB Helper
MyDatabaseHelper dbHelper = new MyDatabaseHelper(mContext);
database = dbHelper.getWritableDatabase();
ContentValues contentValue = new ContentValues();
contentValue.put(MyDatabaseHelper.DATE_MILLIS, dateTimeMillis);
contentValue.put(MyDatabaseHelper.MESSAGE, msg);
//insert data in DB
database.insert("your_table_name", null, contentValue);
//Close the DB connection.
dbHelper.close();
}
现在，您的数据(日期在currenttimemillisecseconds中)被插入到DB中。
下一步是，当您想从DB检索数据时，您需要将各自的日期时间毫秒转换为相应的日期。下面是执行相同操作的示例代码片段_
将日期毫秒转换为日期字符串。
public static String getDate(long milliSeconds, String dateFormat)
{
// Create a DateFormatter object for displaying date in specified format.
SimpleDateFormat formatter = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);
// Create a calendar object that will convert the date and time value in milliseconds to date.
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(milliSeconds);
return formatter.format(calendar.getTime());
}
现在，最后获取数据，看看它的工作…
public ArrayList<String> fetchData() {
ArrayList<String> listOfAllDates = new ArrayList<String>();
String cDate = null;
MyDatabaseHelper dbHelper = new MyDatabaseHelper("your_app_context");
database = dbHelper.getWritableDatabase();
String[] columns = new String[] {MyDatabaseHelper.DATE_MILLIS, MyDatabaseHelper.MESSAGE};
Cursor cursor = database.query("your_table_name", columns, null, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()){
do{
//iterate the cursor to get data.
cDate = getDate(cursor.getLong(cursor.getColumnIndex(MyDatabaseHelper.DATE_MILLIS)), "yyyy/MM/dd HH:mm:ss");
listOfAllDates.add(cDate);
}while(cursor.moveToNext());
}
cursor.close();
//Close the DB connection.
dbHelper.close();
return listOfAllDates;
}
希望这对大家有所帮助!：）
As presumed in this comment, I'd always use integers to store dates. For storing, you could use a utility method public static Long persistDate(Date date) { if (date != null) { return date.getTime(); } return null; } like so: ContentValues values = new ContentValues(); values.put(COLUMN_NAME, persistDate(entity.getDate())); long id = db.insertOrThrow(TABLE_NAME, null, values); Another utility method takes care of the loading public static Date loadDate(Cursor cursor, int index) { if (cursor.isNull(index)) { return null; } return new Date(cursor.getLong(index)); } can be used like this: entity.setDate(loadDate(cursor, INDEX)); Ordering by date is simple SQL ORDER clause (because we have a numeric column). The following will order descending (that is newest date goes first): public static final String QUERY = "SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC"; //... Cursor cursor = rawQuery(QUERY, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { // Process results }
始终确保存储UTC/GMT时间，特别是在使用java.util.Calendar和java.text.SimpleDateFormat使用默认(即您的设备的)时区时。 使用java.util.Date.Date()是安全的，因为它创建了一个UTC值。
最好的方法是将日期存储为使用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()来正确地获取时间戳。
推荐文章
- django test app error -在创建测试数据库时出现错误:创建数据库的权限被拒绝
- 在XML中“图像上缺少contentDescription属性”
- 在Android SQLite中处理日期的最佳方法
- 如何在MySQL表中移动列?
- 读取Android APK的包名
- Android-Facebook应用程序的键散列
- 登出时，清除活动历史堆栈，防止“返回”按钮打开已登录的活动
- 如何改变标题的活动在安卓?
- 在SQL Server中查找重复的行
- 从DateTime中提取小时(SQL Server 2005)
- 如何隐藏动作栏之前的活动被创建，然后再显示它?
- 是否有一种方法以编程方式滚动滚动视图到特定的编辑文本?
- 在Android中将字符串转换为Uri
- LEFT OUTER JOIN如何返回比左表中存在的记录更多的记录?
- 如何用SQL语句计算百分比
