在android sqlite数据库中比较日期(存储为字符串)? [英] Compare dates (stored as string) in android sqlite database?
问题描述
我将数据库中的日期保存为 String
,格式如下:
I store dates as String
in my database, in this format:
YYYY-MM-DD HH:MM:SS
这是支持的格式之一(< a href =http://www.sqlite.org/lang_datefunc.html> http://www.sqlite.org/lang_datefunc.html )。现在我想将这些存储的日期(以及字符串)与另一个日期进行比较。我的日期存储在列 column_date
中,所以我正在尝试:
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:
SELECT * FROM MyTable
WHERE
(Date(column_date) >= Date (2000-01-01) AND Datetime(column_date) <= Datetime (2050-01-01))
当我阅读文档时,日期和时间函数使用IS0-8601的一个子集日期和时间格式。 date()函数返回以下格式的日期:YYYY-MM-DD。
所以我想,我做的正确 - 我从存储的字符串创建日期,并将其与从另一个创建的日期进行比较字符串。
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.
但它不起作用,即使 column_date
是今年的日期,您可以看到开始和结束日期非常仁慈。尝试这个(使用datetime而不是日期):
But it doesn't work, even when column_date
is 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):
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...
推荐答案
如果您将日期存储为格式
if you are storing dates as strings in the format
YYYY-MM-DD HH:mm:ss
====>那么你的日期字段是一个DateTime数据类型
====> then your date field is a DateTime datatype
所以你必须使用这样的查询
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')
您也可以使用@Joop Eggen提供的代码与运算符之间的代码相同。
you can also use the snippet given by @Joop Eggen with th between operator it's th same approche.
BETWEEN 运算符在逻辑上等同于一对比较。 x BETWEEN y AND z等同于x> = y AND x <= z,除了使用BETWEEN,只能对x表达式进行一次评估。请参阅sqlite3 docs
The BETWEEN operator 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
这篇关于在android sqlite数据库中比较日期(存储为字符串)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!