iphone sqlite NSDate bug

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:NSYearCa开发者_高级运维lendarUnit 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?

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.

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.

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.

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.