如何通过java在sqlite中插入日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2881321/
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 date in sqlite through java
提问by dimitar
I want to make a database that will hold a date in it(SQLite). Now first to ask is what is the right syntax to declare a date column. The second i want to know is how to insert date in it after that. And the third thing i want to know is how to select dates between, for example to select all rows which contain date between 01/05/2010 and 05/06/2010.
我想创建一个包含日期的数据库（SQLite）。现在首先要问的是声明日期列的正确语法是什么。我想知道的第二个是之后如何在其中插入日期。我想知道的第三件事是如何选择日期之间的日期，例如选择包含 01/05/2010 和 05/06/2010 之间日期的所有行。
Thank you
谢谢
采纳答案by BalusC
Now first to ask is what is the right syntax to declare a date column.
现在首先要问的是声明日期列的正确语法是什么。
From the SQLite Data Types documentation:
1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functionsof SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXTas ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REALas Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGERas Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
1.2 日期和时间数据类型
SQLite 没有专门用于存储日期和/或时间的存储类。相反，SQLite的内置日期和时间函数能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值：
- TEXT作为 ISO8601 字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
- REAL作为儒略日数，根据预测公历，自公元前 4714 年 11 月 24 日中午在格林威治的天数。
- INTEGER作为 Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数。
应用程序可以选择以任何这些格式存储日期和时间，并使用内置的日期和时间函数在格式之间自由转换。
Take your pick. I'd go for TEXT or INTEGER. The INTEGER will be faster. If you need to store dates past 1970 (e.g. birthdates, etc), then I'd go for TEXT. If you just need to store creationtime/modificationtime, etc for now and the future, then go for INTEGER.
随你挑。我会选择 TEXT 或 INTEGER。INTEGER 会更快。如果您需要存储 1970 年以后的日期（例如生日等），那么我会选择 TEXT。如果您现在和将来只需要存储创建时间/修改时间等，那么请选择 INTEGER。
The second i want to know is how to insert date in it after that.
我想知道的第二个是之后如何在其中插入日期。
Use PreparedStatement#setString()or #setLong()respectively.
分别使用PreparedStatement#setString()或#setLong()。
And the third thing i want to know is how to select dates between, for example to select all rows which contain date between 01/05/2010 and 05/06/2010.
我想知道的第三件事是如何选择日期之间的日期，例如选择包含 01/05/2010 和 05/06/2010 之间日期的所有行。
Use the standard SQL BETWEENclause for this. You first need to convert the date accordingly using the built-in date and time functions.
BETWEEN为此使用标准 SQL子句。您首先需要使用内置的日期和时间函数相应地转换日期。
回答by IgorGanapolsky
A convenient syntax for declaring a date column is like this:
声明日期列的便捷语法如下所示：
"CREATE TABLE "
+ "my_table"
+ "(date_time "
+ " DATETIME DEFAULT CURRENT_TIMESTAMP);");
This will default inserted values to the current datetime. Or you can use CURRENT_DATE, or CURRENT_TIMEvalues. You won't have to insert a timestamp manually each time you create a row.
这会将插入的值默认为当前日期时间。或者您可以使用CURRENT_DATE或CURRENT_TIME值。您不必在每次创建行时手动插入时间戳。
You can read about this approach here: Android insert datetime value in SQLite database
您可以在此处阅读有关此方法的信息：Android 在 SQLite 数据库中插入日期时间值
