iPhone SQLite日期问题 [英] iPhone SQLite date problem

问题描述

数据库被创建得很好，插入功能很好。我在下面的select语句中遇到问题。它不返回记录，没有错误。任何其他领域的选择都可以正常工作。任何人都可以发现我做错了什么？

 创建表如果不存在数据（pkey整数主键，文档日期） 
 
 [db executeUpdate：@insert into data（doc）values（？），[NSDate date]]; 
 
select * FROM data WHERE doc between'2009-08-23'and'2009-08-23'
  

解决方案

2009-08-23和2009-08-23之间的部分总是假的，因为这是零时间的范围，因为缺乏更好的描述。

如果你想要的项目发生在2009-08-23，选择在'2009-08-23'和'2009-08-24'之间，范围从24日凌晨的午夜到24日的午夜。

这是一个例子：

  sqlite>创建表t（d DATETIME）; 
 sqlite> SELECT DATETIME（'now'）; 
 2009-08-24 02:32:20 
 sqlite> INSERT INTO t VALUES（datetime（'now'））; 
 sqlite> SELECT * FROM t; 
 2009-08-24 02:33:42 
 sqlite> SELECT * FROM t其中d BETWEEN'2009-08-24'和'2009-08-24'; 
 sqlite> SELECT * FROM t其中d BETWEEN'2009-08-24'和'2009-08-25'; 
 2009-08-24 02:33:42 
  

Database gets created just fine, and the insert works just fine. I'm having problem with the select statement below. It returns no records and no error. A select on any other field works just fine. Can anyone spot what I'm doing wrong?

"create table if not exists data (pkey integer primary key, doc date)"

[db executeUpdate:@"insert into data ( doc) values (?)" , [NSDate date]];

"select * FROM data WHERE doc between '2009-08-23' and '2009-08-23' "

解决方案

The part between '2009-08-23' and '2009-08-23' is always false, because that's a range of zero time, for lack of a better description.

If you want items occurring on exactly 2009-08-23, select between '2009-08-23' and '2009-08-24' which will range from midnight on the 23rd to midnight on the 24th.

Here's an example of this in action:

sqlite> CREATE TABLE t (d DATETIME);
sqlite> SELECT DATETIME('now');
2009-08-24 02:32:20
sqlite> INSERT INTO t VALUES (datetime('now'));
sqlite> SELECT * FROM t;
2009-08-24 02:33:42
sqlite> SELECT * FROM t where d BETWEEN '2009-08-24' and '2009-08-24';
sqlite> SELECT * FROM t where d BETWEEN '2009-08-24' and '2009-08-25';
2009-08-24 02:33:42

这篇关于iPhone SQLite日期问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！