The best way is to store the dates as a number, received by using the Calendar command.
//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.
It's worth mentioning that the values that come out of this should be long, not int. Integer in sqlite can mean many things, anything from 1-8 bytes, but for almost all dates 64 bits, or a long, is what works.
EDIT: As has been pointed out in the comments, you have to use the cursor.getLong() to properly get the timestamp if you do this.
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;
}
You can use a text field to store dates within SQLite.
Storing dates in UTC format, the default if you use datetime('now')(yyyy-MM-dd HH:mm:ss) will then allow sorting by the date column.
Retrieving dates as strings from SQLite you can then format/convert them as required into local regionalised formats using the Calendar or the android.text.format.DateUtils.formatDateTime method.
Here's a regionalised formatter method I use;
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;
}
public static Long persistDate(Date date) {
如果（日期！= null）{
返回日期.getTime();
}
返回空值；
}
像这样：
ContentValues 值 = new ContentValues();
value.put(COLUMN_NAME, persistDate(entity.getDate()));
长 id = db.insertOrThrow(TABLE_NAME, null, 值);
：
另一个实用程序方法负责加载
public static Date loadDate(光标cursor, int index) {
if (cursor.isNull(index)) {
返回空值；
}
返回新日期(cursor.getLong(index));
}
可以这样使用：
entity.setDate(loadDate(cursor, INDEX));
按日期排序是简单的 SQL ORDER 子句（因为我们有一个数字列）。以下内容将按降序排列（即最新日期排在前面）：
public static final String QUERY = "SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC";
//...
游标cursor = rawQuery(QUERY, null);
光标.moveToFirst();
while (!cursor.isAfterLast()) {
// 处理结果
}
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
}
Always make sure to store the UTC/GMT time, especially when working with java.util.Calendar and java.text.SimpleDateFormat that use the default (i.e. your device's) time zone. java.util.Date.Date() is safe to use as it creates a UTC value.
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
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 can use text, real, or integer data types to store dates.
Even more, whenever you perform a query, the results are shown using format %Y-%m-%d %H:%M:%S.
Now, if you insert/update date/time values using SQLite date/time functions, you can actually store milliseconds as well.
If that's the case, the results are shown using format %Y-%m-%d %H:%M:%f.
For example:
Now, doing some queries to verify if we are actually able to compare times:
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
You can check the same SELECT using col2 and col3 and you will get the same results.
As you can see, the second row (126 milliseconds) is not returned.
Note that BETWEEN is inclusive, therefore...
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');
... will return the same set.
Try playing around with different date/time ranges and everything will behave as expected.
What about without strftime function?
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
What about without strftime function and no milliseconds?
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
What about 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
Works just fine.
Finally, when dealing with actual operations within a program (without using the sqlite executable...)
BTW: I'm using JDBC (not sure about other languages)... the sqlite-jdbc driver v3.7.2 from xerial - maybe newer revisions change the behavior explained below...
If you are developing in Android, you don't need a jdbc-driver. All SQL operations can be submitted using the SQLiteOpenHelper.
JDBC has different methods to get actual date/time values from a database: java.sql.Date, java.sql.Time, and java.sql.Timestamp.
The related methods in java.sql.ResultSet are (obviously) getDate(..), getTime(..), and getTimestamp() respectively.
For example:
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.
Since SQLite doesn't have an actual DATE/TIME/TIMESTAMP data type all these 3 methods return values as if the objects were initialized with 0:
new java.sql.Date(0)
new java.sql.Time(0)
new java.sql.Timestamp(0)
So, the question is: how can we actually select, insert, or update Date/Time/Timestamp objects? There's no easy answer.
You can try different combinations, but they will force you to embed SQLite functions in all the SQL statements. It's far easier to define an utility class to transform text to Date objects inside your Java program. But always remember that SQLite transforms any date value to UTC+0000.
In summary, despite the general rule to always use the correct data type, or, even integers denoting Unix time (milliseconds since epoch), I find much easier using the default SQLite format ('%Y-%m-%d %H:%M:%f' or in Java 'yyyy-MM-dd HH:mm:ss.SSS') rather to complicate all your SQL statements with SQLite functions. The former approach is much easier to maintain.
TODO: I will check the results when using getDate/getTime/getTimestamp inside Android (API15 or better)... maybe the internal driver is different from sqlite-jdbc...
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();
}
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;
}
希望这会对所有人有所帮助！ :)
Best way to store datein SQlite DB is to store the current DateTimeMilliseconds. Below is the code snippet to do so_
Get the DateTimeMilliseconds
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;
}
Insert the data in your DB
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();
}
Now, your data (date is in currentTimeMilliseconds) is get inserted in DB .
Next step is, when you want to retrieve data from DB you need to convert the respective date time milliseconds in to corresponding date. Below is the sample code snippet to do the same_
Convert date milliseconds in to date string.
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());
}
Now, Finally fetch the data and see its working...
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;
}
发布评论
评论（9）
最好的方法是将日期存储为数字，通过使用日历命令接收。
为什么要这样做？首先，从日期范围获取值很容易。只需将日期转换为毫秒，然后进行适当的查询即可。按日期排序也同样容易。正如我所提到的，在各种格式之间进行转换的调用也同样容易。最重要的是，使用这种方法，您可以做任何您需要做的事情，没有任何问题。读取原始值会有点困难，但它足以弥补这个轻微的缺点，易于机器读取和使用。事实上，构建一个阅读器相对容易（我知道有一些），它可以自动将时间标签转换为日期，以便于阅读。
值得一提的是，由此产生的值应该是 long，而不是 int。 sqlite 中的整数 可以表示很多东西，从 1 到 8 个字节的任何内容，但对于几乎所有日期 64位或长字节才是有效的。
编辑：正如评论中所指出的，如果执行此操作，您必须使用
cursor.getLong()才能正确获取时间戳。The best way is to store the dates as a number, received by using the Calendar command.
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.
It's worth mentioning that the values that come out of this should be long, not int. Integer in sqlite can mean many things, anything from 1-8 bytes, but for almost all dates 64 bits, or a long, is what works.
EDIT: As has been pointed out in the comments, you have to use the
cursor.getLong()to properly get the timestamp if you do this.您可以使用文本字段在
SQLite中存储日期。以 UTC 格式存储日期，默认情况下如果您使用
datetime('now')(yyyy-MM-dd HH:mm:ss)将允许按日期排序柱子。从 SQLite 中以字符串形式检索日期，然后您可以使用 Calendar 或 android.text.format.DateUtils.formatDateTime 方法根据需要将其格式化/转换为本地区域化格式。
这是我使用的区域化格式化方法；
You can use a text field to store dates within
SQLite.Storing dates in UTC format, the default if you use
datetime('now')(yyyy-MM-dd HH:mm:ss)will then allow sorting by the date column.Retrieving dates as strings from
SQLiteyou can then format/convert them as required into local regionalised formats using the Calendar or theandroid.text.format.DateUtils.formatDateTimemethod.Here's a regionalised formatter method I use;
为了存储，您可以使用实用方法
像这样：
另一个实用程序方法负责加载
可以这样使用：
按日期排序是简单的 SQL ORDER 子句（因为我们有一个数字列）。以下内容将按降序排列（即最新日期排在前面）：
始终确保存储 UTC/GMT 时间，尤其是在使用
java.util.Calendar和 < code>java.text.SimpleDateFormat 使用默认（即您设备的）时区。java.util.Date.Date()可以安全使用，因为它创建 UTC 值。For storing, you could use a utility method
like so:
Another utility method takes care of the loading
can be used like this:
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):
Always make sure to store the UTC/GMT time, especially when working with
java.util.Calendarandjava.text.SimpleDateFormatthat use the default (i.e. your device's) time zone.java.util.Date.Date()is safe to use as it creates a UTC value.SQLite 可以使用文本、实数或整数数据类型来存储日期。
更重要的是，每当您执行查询时，结果都会使用
%Y-%m-%d %H:%M:%S格式显示。现在，如果您使用 SQLite 日期/时间函数插入/更新日期/时间值，您实际上也可以存储毫秒。
如果是这种情况，结果将使用
%Y-%m-%d %H:%M:%f格式显示。例如：
现在，进行一些查询来验证我们是否确实能够比较时间：
您可以使用
col2和col3检查相同的SELECT你会得到相同的结果。如您所见，第二行（126 毫秒）未返回。
请注意，
BETWEEN是包含在内的，因此......将返回相同的集合。
尝试使用不同的日期/时间范围，一切都会按预期运行。
如果没有
strftime函数怎么办？如果没有
strftime函数并且没有毫秒怎么办？ORDER BY怎么样？效果很好。
最后，在处理程序中的实际操作时（不使用 sqlite 可执行文件...）
顺便说一句：我正在使用 JDBC（不确定其他语言）...来自<的 sqlite-jdbc 驱动程序 v3.7.2 a href="https://bitbucket.org/xerial/sqlite-jdbc/overview">xerial - 也许较新的修订会更改下面解释的行为...
如果您在 Android 中进行开发，则不需要 jdbc 驱动程序。所有 SQL 操作都可以使用 SQLiteOpenHelper 提交。
JDBC 有不同的方法从数据库获取实际日期/时间值：
java.sql.Date、<代码>java.sql.Time和java.sql.Timestamp。java.sql.ResultSet中的相关方法（显然）是getDate(..)、getTime(..)和getTimestamp() 分别。例如：
由于 SQLite 没有实际的 DATE/TIME/TIMESTAMP 数据类型，所有这 3 个方法都返回值，就好像对象是用 0 初始化的：
所以，问题是：我们如何实际选择、插入、或更新日期/时间/时间戳对象？没有简单的答案。
您可以尝试不同的组合，但它们会迫使您在所有 SQL 语句中嵌入 SQLite 函数。在 Java 程序中定义一个实用程序类来将文本转换为 Date 对象要容易得多。但请记住，SQLite 会将任何日期值转换为 UTC+0000。
总之，尽管一般规则是始终使用正确的数据类型，或者甚至是表示 Unix 时间（自纪元以来的毫秒数）的整数，但我发现使用默认的 SQLite 格式（
'%Y-%m-%d %H:%M:%f'或 Java'yyyy-MM-dd HH:mm:ss.SSS')，而不是使用 SQLite 函数使所有 SQL 语句复杂化。前一种方法更容易维护。TODO：我将在 Android（API15 或更好）中使用 getDate/getTime/getTimestamp 时检查结果...也许内部驱动程序与 sqlite-jdbc 不同...
SQLite can use text, real, or integer data types to store dates.
Even more, whenever you perform a query, the results are shown using format
%Y-%m-%d %H:%M:%S.Now, if you insert/update date/time values using SQLite date/time functions, you can actually store milliseconds as well.
If that's the case, the results are shown using format
%Y-%m-%d %H:%M:%f.For example:
Now, doing some queries to verify if we are actually able to compare times:
You can check the same
SELECTusingcol2andcol3and you will get the same results.As you can see, the second row (126 milliseconds) is not returned.
Note that
BETWEENis inclusive, therefore...... will return the same set.
Try playing around with different date/time ranges and everything will behave as expected.
What about without
strftimefunction?What about without
strftimefunction and no milliseconds?What about
ORDER BY?Works just fine.
Finally, when dealing with actual operations within a program (without using the sqlite executable...)
BTW: I'm using JDBC (not sure about other languages)... the sqlite-jdbc driver v3.7.2 from xerial - maybe newer revisions change the behavior explained below...
If you are developing in Android, you don't need a jdbc-driver. All SQL operations can be submitted using the
SQLiteOpenHelper.JDBC has different methods to get actual date/time values from a database:
java.sql.Date,java.sql.Time, andjava.sql.Timestamp.The related methods in
java.sql.ResultSetare (obviously)getDate(..),getTime(..), andgetTimestamp()respectively.For example:
Since SQLite doesn't have an actual DATE/TIME/TIMESTAMP data type all these 3 methods return values as if the objects were initialized with 0:
So, the question is: how can we actually select, insert, or update Date/Time/Timestamp objects? There's no easy answer.
You can try different combinations, but they will force you to embed SQLite functions in all the SQL statements. It's far easier to define an utility class to transform text to Date objects inside your Java program. But always remember that SQLite transforms any date value to UTC+0000.
In summary, despite the general rule to always use the correct data type, or, even integers denoting Unix time (milliseconds since epoch), I find much easier using the default SQLite format (
'%Y-%m-%d %H:%M:%f'or in Java'yyyy-MM-dd HH:mm:ss.SSS') rather to complicate all your SQL statements with SQLite functions. The former approach is much easier to maintain.TODO: I will check the results when using getDate/getTime/getTimestamp inside Android (API15 or better)... maybe the internal driver is different from sqlite-jdbc...
通常（与我在 mysql/postgres 中一样）我将日期存储在 int(mysql/post) 或 text(sqlite) 中，以时间戳格式存储它们。
然后我将它们转换为 Date 对象并根据用户时区执行操作
Usually (same as I do in mysql/postgres) I stores dates in int(mysql/post) or text(sqlite) to store them in the timestamp format.
Then I will convert them into Date objects and perform actions based on user TimeZone
在SQlite DB中存储
日期的最佳方法是存储当前的DateTimeMilliseconds。下面是执行此操作的代码片段_现在，您的数据（日期以 currentTimeMilliseconds 为单位）已插入数据库。下一步是，当您想从数据库检索数据时，您需要转换相应的日期时间毫秒到相应的日期。下面是执行相同操作的示例代码片段_
希望这会对所有人有所帮助！ :)
Best way to store
datein SQlite DB is to store the currentDateTimeMilliseconds. Below is the code snippet to do so_Now, your data (date is in currentTimeMilliseconds) is get inserted in DB .Next step is, when you want to retrieve data from DB you need to convert the respective date time milliseconds in to corresponding date. Below is the sample code snippet to do the same_
Hope this will help all! :)
1 -正如 StErMi 所说。
2 - 请阅读以下内容：http://www.vogella.de/articles/AndroidSQLite/article .html
3 -
请参阅此处：
SQLiteDatabase 中的 Query()
4 - 请参阅答案 3
1 -Exactly like StErMi said.
2 - Please read this: http://www.vogella.de/articles/AndroidSQLite/article.html
3 -
see here:
Query() in SQLiteDatabase
4 - see answer 3
我更喜欢这个。这不是最好的方法，但却是一个快速的解决方案。
I prefer this. This is not the best way, but a fast solution.