iPhone SQLite date problem

Question

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' "

Answer 1

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

Answer 2

I have had to do this exact type of lookup on sqlite and here is a simple date formatting example to get the start and end of day timestamps for your date.

In my case I am storing the timestamps with this timestamp format in sqlite: "yyyy-MM-dd HH:mm:ss.SSSS". This example uses the current date ([NSDate date]).

NSDateFormatter *dateFormatter = [[[NSDateFormatter alloc] init] autorelease];  
[dateFormatter setDateFormat:@"yyyy-MM-dd"];
NSString *dateString = [dateFormatter stringFromDate:[NSDate date]];
NSDate *startOfDay = [dateFormatter dateFromString:dateString];  
// add a full day and subtract 1 second to get the end of day timestamp
NSDate *endOfDay = [startOfDay addTimeInterval:(60*60*24)-1];
[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss.SSSS"];

You can then use startOfDay and endOfDay for your query values.

Answer 3

SQLite does string comparison, not date comparison.

So, one way to solve your problem is:

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

Answer 4

Check out this question.

http://stackoverflow.com/questions/1711504/how-get-datetime-column-in-sqlite-objecite-c/1711591#1711591