Java 如何在sqlite数据库中插入数据类型日期（yyyy-MM-dd）并在android中检索两个日期之间的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18206257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to insert data type date(yyyy-MM-dd) in sqlite database and retrive data between two dates in android
提问by chimbu
I have a Sqlite3 database table contains name,address,date of birth details.i want to display 1990-01-01 to 1995-01-01 details.
我有一个 Sqlite3 数据库表，包含姓名、地址、出生日期详细信息。我想显示 1990-01-01 到 1995-01-01 的详细信息。
but Sqlite3 database stores only following data types.
但 Sqlite3 数据库只存储以下数据类型。
TEXT
NUMERIC
INTEGER
REAL
NONE
Any one have some hint to store and retrieve date format data..?
任何人都有一些提示来存储和检索日期格式数据..？
采纳答案by Budius
Use this code to convert your date into millisecond format and store it into your database as INTEGER types
使用此代码将您的日期转换为毫秒格式并将其作为 INTEGER 类型存储到您的数据库中
String someDate = "1995-01-01";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse(someDate);
System.out.println(date.getTime());
date.getTime()-give the millisecond format
date.getTime()-给出毫秒格式
At the same way to convert your input (i.e from 1990-01-01 and to date 1995-01-01)
以同样的方式转换您的输入（即从 1990-01-01 到 1995-01-01）
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date1 = sdf.parse(1990-01-01);
value1=date.getTime();
Date date2 = sdf.parse(1995-01-01);
value2=date.getTime();
Retrieve from database using following query
使用以下查询从数据库中检索
db.rawQuery("SELECT * FROM table_name WHERE column_name BETWEEN "+value1+" AND "+value2+"",null);
or
db.rawQuery("SELECT * FROM table_name WHERE column_name<="+value1+" AND column_name>="+value2+"",null);
回答by Ruchira Gayan Ranaweera
You can do something like this
你可以做这样的事情
DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Date date1=df.parse("1990-01-01");
Date date2=df.parse("1995-01-01");
Date myDate=df.parse("1992-01-01"); // checking date
if((date1.getTime()<myDate.getTime())&&(myDate.getTime()<date2.getTime())){
System.out.println(df.format(myDate)+" is in this range");
}else{
System.out.println(df.format(myDate)+" is not in this range");
}
回答by Evgeniy Dorofeev
You can use dates in yyyy-MM-dd format directly, JDBC will understand it. Assuming we a have a table t1 with c1 of DATE type
你可以直接使用yyyy-MM-dd格式的日期，JDBC会理解的。假设我们有一个表 t1 和 DATE 类型的 c1
PreparedStatement ps = conn.prepareStatement("insert into t1 (c1) values (?)");
ps.setString(1, "2001-01-01");
ps.executeUpdate();
Reading dates is simple too
阅读日期也很简单
ResultSet rs = st.executeQuery("select c1 from t1");
rs.next();
Date date = rs.getDate(1);
ResultSet.getDate returns result as java.sql.Date whose toString method returns date in yyyy-MM-dd format
ResultSet.getDate 返回结果为 java.sql.Date 其 toString 方法以 yyyy-MM-dd 格式返回日期
回答by lucian.pantelimon
Since the format you want to use (yyyy-MM-dd) is ordered in the same way as a String(i.e. for any dates xand yyou would choose, if x < yas a Date, then x < yas a String), you can simply store the dates as Strings (TEXT) in your database.
由于您要使用的格式 ( yyyy-MM-dd) 的排序方式与 a 相同String（即对于任何日期x，y您可以选择，如果x < y作为 a Date，则x < y作为 a String），您可以简单地将日期存储为Strings ( TEXT) 在您的数据库中。
When selecting the values between them, you would just have to use a WHEREclause in your SELECTstatement like this:
在选择它们之间的值时，您只需WHERE在您的SELECT语句中使用一个子句，如下所示：
SELECT * FROM yourTable WHERE yourDateFieldName > ? and yourDateFieldName < ?
You can then use DateFormat.formatto set the values for the ?parameters of your prepared statement. The first parameter would be the "start" date, and the second would be the "end" date. You can replace <with <=and >with >=if you want the items on start and end dates included.
然后，您可以使用DateFormat.format来设置?准备好的语句的参数值。第一个参数是“开始”日期，第二个参数是“结束”日期。您可以替换<使用<=，并>与>=如果你想包括在开始和结束日期的项目。
This gives you a Stringrepresentation of a Date. To convert from that to an actual Dateobject you can use date formatter's parsemethod (i.e. SimpleDateFormat.parse).
这为您提供String了一个Date. 要将其转换为实际Date对象，您可以使用日期格式化程序的parse方法（即SimpleDateFormat.parse）。
Another, "cleaner", approach would be to use the SQLite date and time functions (see here). While SQLite doesn't have a DATEtype for storing date values, it has helper functions that you can use to interpret TEXTand NUMBERvalues as date in your statements.
另一种“更干净”的方法是使用 SQLite 日期和时间函数（请参阅此处）。虽然SQLite没有一个DATE用于存储日期值的类型，它具有辅助功能，您可以使用来解释TEXT和NUMBER值日期在你的语句。
If you don't need extra processing for your date values, I'd recommend going for the first solution as it should be faster because it merely compares TEXTs rather than parsing and extracting a date from them, then comparing the extracted date (I haven't compared the speed of the two approaches, so don't take my word for it on this one). This approach also has less code to write and maintain and the code is easier to read.
如果您不需要对日期值进行额外处理，我建议使用第一个解决方案，因为它应该更快，因为它只是比较TEXTs 而不是从中解析和提取日期，然后比较提取的日期（我没有没有比较两种方法的速度，所以不要相信我的话）。这种方法还需要编写和维护的代码较少，代码更易于阅读。
Sources:
资料来源：
SQLite data type- for the validity of comparing two TEXTvalues
SQLite 数据类型- 用于比较两个TEXT值的有效性
SimpleDateFormat- Android documentation
SimpleDateFormat- Android 文档
回答by Budius
From my own experience on doing several projects with database in Android my answer is:
根据我自己在 Android 中使用数据库进行多个项目的经验，我的答案是：
Do not store the date as a string. Never! Ever! Store them as Unix timestamps and format them as needed during runtime.
不要将日期存储为字符串。绝不！曾经！将它们存储为 Unix 时间戳并在运行时根据需要格式化它们。
the important thing here is to separate what is your dataand what is the on-screen representation of your data. Storing in a database the on-screen representation of your data is wrong.
这里重要的是将什么是您的数据以及什么是您的数据在屏幕上的表示。将数据的屏幕表示存储在数据库中是错误的。
You'll always store your dates as INTEGER types.
您将始终将日期存储为 INTEGER 类型。
So for example to store the date now you'll store the value System.currentTimeInMilis
因此，例如现在存储日期，您将存储值 System.currentTimeInMilis
To select between 1990-01-01 and 1995-01-01 you will:
要在 1990-01-01 和 1995-01-01 之间进行选择，您将：
long val1 = new GregorianCalendar(1990, 01, 01).getTimeInMillis();
long val2 = new GregorianCalendar(1995, 01, 01).getTimeInMillis();
and then you'll do the normal SELECTstatement between those 2 values.
然后你会SELECT在这两个值之间做正常的陈述。
to show those values in the screen as yyyy-MM-ddyou'll use the SimpleDateFormatclass:
在yyyy-MM-dd您将使用SimpleDateFormat该类时在屏幕上显示这些值：
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
long longDate = cursor.getLong(colNumber); // from the database
String stringDate = dateFormat.format(new Date(longDate));
