比较android sqlite数据库中的日期（存储为字符串）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10832864/
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
Compare dates (stored as string) in android sqlite database?
提问by qkx
I store dates as Stringin my database, in this format:
我将日期存储String在我的数据库中，格式如下：
YYYY-MM-DD HH:MM:SS
which is one of supported formats (http://www.sqlite.org/lang_datefunc.html). Now I want to compare these stored dates (well, strings) with another dates. My dates are stored in column column_date, so I'm trying this:
这是支持的格式之一（http://www.sqlite.org/lang_datefunc.html）。现在我想将这些存储的日期（好吧，字符串）与另一个日期进行比较。我的日期存储在 column 中column_date，所以我正在尝试：
SELECT * FROM MyTable
WHERE
(Date(column_date) >= Date (2000-01-01) AND Datetime(column_date) <= Datetime (2050-01-01))
As i read documentation, The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD.so I suppose, I'm doing it right - I create date from stored string and compare it with date created from another strings.
当我阅读文档时，The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD.我想，我做对了 - 我从存储的字符串创建日期并将其与从另一个字符串创建的日期进行比较。
But it doesn't work, even when column_dateis date from this year, and as u can see the start and end dates are very benevolent. Tried also this (used datetime instead of date):
但它不起作用，即使column_date是今年的日期，而且正如您所见，开始日期和结束日期非常友好。也试过这个（使用日期时间而不是日期）：
SELECT * FROM MyTable
WHERE
(datetime(column_date) >= Date (2000-01-01) AND datetime(column_date) <= Datetime (2050-01-01))
and this (use between instead of <= and >=)
和这个（使用之间代替<=和>=）
SELECT * FROM MyTable
WHERE
(Date(column_date) between Date (2000-01-01) AND Datetime (2050-01-01))
and all other possible combinations. What the hell I'm doing wrong, am I stupid, or the documentaion lies, or I missed something very important? Trying to find solution few hours, but nothing works...
以及所有其他可能的组合。我到底做错了什么，是我愚蠢，还是文件在撒谎，或者我错过了一些非常重要的东西？试图找到解决方案几个小时，但没有任何效果......
回答by K_Anas
if you are storing dates as strings in the format
如果您将日期存储为格式的字符串
YYYY-MM-DD HH:mm:ss
====> then your date field is a DateTime datatype
====> 那么你的日期字段是 DateTime 数据类型
Thus you have to use such query
因此你必须使用这样的查询
select *
from MyTable
where mydate >= Datetime('2000-01-01 00:00:00')
and mydate <= Datetime('2050-01-01 23:00:59')
you can also use the snippet given by @Joop Eggen with th between operator it's th same approche.
您还可以使用@Joop Eggen 给出的代码段，并在运算符之间使用相同的方法。
The BETWEENoperator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z"is equivalent to "x>=y AND x<=z"except that with BETWEEN, the x expression is only evaluated once. see sqlite3 docs
该BETWEEN操作者在逻辑上等同于一对比较。“x BETWEEN y AND z”等价于“x>=y AND x<=z”，不同的是对于 BETWEEN，x 表达式只计算一次。参见 sqlite3文档
回答by Joop Eggen
SELECT *
FROM MyTable
WHERE
column_date BETWEEN '2000-01-01 00:00:00' AND '2050-01-01 23:00:59'
Should do the trick. This only uses strings but you said that would be fitting.
应该做的伎俩。这仅使用字符串，但您说这很合适。
The error was the missing time part or not doing a substr on the date part only. And then data/time conversion functions might give wrong things too.
错误是缺少时间部分或未仅在日期部分执行 substr。然后数据/时间转换函数也可能给出错误的东西。
回答by Barak
I think your issue is here...
我想你的问题在这里......
AND Datetime(column_date) <= Datetime (2050-01-01))
Why are you using Datetime here instead of Date?
你为什么在这里使用日期时间而不是日期？
Try this:
尝试这个：
SELECT * FROM MyTable WHERE (Date(column_date) >= Date (2000-01-01) AND Date(column_date) <= Date (2050-01-01))
回答by Hasanur
Date filed in the SQLite table will be TEXT and date data should be stored as "2012-12-31 00:12:00" format that comparison dose not give you trouble and if we consider that fields are date_from and date_to and we are storing current date at to_date then we should get the current date as bellow and
SQLite 表中归档的日期将是 TEXT 并且日期数据应存储为“2012-12-31 00:12:00”格式，这样比较不会给您带来麻烦，如果我们认为字段是 date_from 和 date_to 并且我们正在存储当前日期在 to_date 那么我们应该得到当前日期如下
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String to_date = sdf.format(new Date());
and the SQL query should be
并且 SQL 查询应该是
crs = db.rawQuery(
"SELECT _id, date_from, date_to, done_or_not, list_name " +
"FROM list_tbl " +
"WHERE " +
"date_from <= Datetime('"+to_date+"') AND date_to >= Datetime('"+to_date+"')", null);
回答by user3571876
Compare dates (stored as string) in android SQLite database. Use date in 12/07/2015format and use a query like this:
比较 android SQLite 数据库中的日期（存储为字符串）。在12/07/2015格式中使用日期并使用如下查询：
SELECT * FROM tableName WHERE Date >=('12/07/2015') and appointmentDate <('13/07/2015')";
This works for me.
这对我有用。
