比较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

这是支持的格式之一(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 是今年的日期，并且如您所见，开始日期和结束日期非常合适.也试过这个(使用日期时间而不是日期):

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))

和这个(使用 between 而不是 <= 和 >=)

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 文档

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屋！