iphone sqlite NSDate bug

Question

Hi, i use core data in my iphone app, but in some case i use sqlite to access to data and ia have a problem with NSDate.

NSDate *now = [NSDate date];
     NSCalendar *calender = [NSCalendar currentCalendar];;
     NSDateComponents *comp = [calender components:NSYearCalendarUnit fromDate:now];
     [comp setDay:1];
     [comp setMonth:1];
     NSDate *yearAgo = [calender dateFromComponents:comp];
     NSPredicate *predicate = [NSPredicate predicateWithFormat:@"date > %@",yearAgo];

This code works and select records from the start of year, but if i use sqlite raw query

NSDate *current=[NSDate date];
      NSDateComponents *comps = [gregorian components:(NSDayCalendarUnit | NSMonthCalendarUnit |NSYearCalendarUnit) fromDate:current];
      [comps setDay:1];
      [comps setMonth:1];

      NSDate *yearDate = [gregorian dateFromComponents:comps];

      [where appendFormat:@"zdate > '%@' ",yearDate];

I have a problem, wrong date determined by the records, Records that in core date has date like 2008-01-01 in sqlite have dates like 1977 year. How to fix it? May be I was wrong to use NSDate in the query?

Answer 1

I haven't checked this, but off the top of my head I'd guess that sqlite expects a different date format. Consider using an NSDateFormatter to convert it to the one sqlite uses.

Answer 2

Wrong usage of NSDate in predicate!

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"date > %@",yearAgo]; - worng

I must use this:

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"date > '%f'",[yearAgo timeIntervalSinceReferenceDate];

And all works.

Answer 3

In some cases you'll need to add 31 years to the dates in the SQLite DB.

From the book: IPhone Forensics: Recovering Evidence, Personal Data, and Corporate Assets By Jonathan Zdziarski On calendar events:

Unlike most timestamps used on the iPhone, which are standard Unix time-stamps, the timestamp used here is an RFC 822 timestamp representing the date offset to 1977. To convert this date, determine the actual RFC 822 time-stamp and add 31 years.

Answer 4

For doing any query or other SQLLite operation, you should always (always!!!) use bind variables. So the query would look like:

zdate > ?

Then you make a prepared statement, bind the date variable, and execute the statement to get your results.