问题相关的sqlite从数据库IOS获取日期之间的日期(issue related sqlite fetching dates between dates from database IOS)

只是很少的时刻之前，它工作完美，突然不知道发生了什么，并且已经改变了什么，但它不再工作了，试了这么多但是无法弄清楚我错过了什么帮助就像天堂对我来说下面是我的代码

这是我的日期在我的sqlite数据库01/20/1978中的方式

01/20/2013 02/28/1979 MM/dd/yyyy NSLog(@"Fetching holidays from the database between %@ and %@...", fromDate, toDate); // fromdate and todate both are nsdate this is what they prints // Fetching holidays from the database between 2013-06-29 18:30:00 +0000 and 2013-08-03 18:29:59 +0000... NSDateFormatter *fmt = [[[NSDateFormatter alloc] init] autorelease]; NSString *query4 = [[NSString alloc] initWithFormat:@"select Birthdates,Names,Profilepic,Email,Phonenumber from PersonNamesAndBirthDates where Birthdates between ? and ?"]; sqlite3_stmt *stmt4; int x4 = sqlite3_prepare_v2(database1,[query4 UTF8String],-1, &stmt4, nil); if (x4 == SQLITE_OK) [fmt setDateFormat:@"MM/dd/yyyy"]; sqlite3_bind_text(stmt4, 1, [[fmt stringFromDate:fromDate] UTF8String], -1, SQLITE_STATIC); sqlite3_bind_text(stmt4, 2, [[fmt stringFromDate:toDate] UTF8String], -1, SQLITE_STATIC); //**IT WAS GOING IN WHILE BUT NOW ITS NOT GOING IN SO IT JUST JUMP FROM HERE** while(sqlite3_step(stmt4) == SQLITE_ROW) { NSString *name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt4, 0)]; NSString *proPic = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt4, 1)]; NSString *email = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt4, 2)]; NSString *pNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt4, 3)]; NSString *bDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt4, 4)]; [holidays addObject:[Holiday holidayNamed:name email:email profilePic:proPic phoneNumber:pNumber date:[fmt dateFromString:bDate]]]; NSLog(@"%@",holidays); } sqlite3_finalize(stmt4); NSLog(@"%@",holidays); sqlite3_close(database1); [delegate loadedDataSource:self];

}

just few Moments before it was working perfectly and suddenly dont know what happen and havnt changed anything but its not working anymore tried so much but cant figure out what i am missing any help be like heaven for me below is my code

this is how my dates are in my sqlite datbase 01/20/1978

}

最满意答案

格式化程序与您在数据库中使用的内容相匹配，但问题是该格式不会按时间顺序对日期进行排序。 SQLite没有正确的日期数据类型，只是将日期存储为字符串（或数字，但不是真正的“日期”数据类型）。更糟糕的是，因为您当前的字符串值被格式化为MM/dd/yyyy ，因此它将按字母顺序排序，即按月，按天，然后按年，而不是按时间顺序排序。

解决方案是：

浏览数据库并将格式为MM/dd/yyyy当前存储为字符串的日期值替换为yyyy-MM-dd格式的字符串值。

同样，在上面的代码示例中，替换：

[fmt setDateFormat:@"MM/dd/yyyy"];

同

[fmt setDateFormat:@"yyyy-MM-dd"];

您还可以使用日期和时间函数中枚举的格式之一。但是选择一种格式，当你按字母顺序排序时，它也会按时间顺序对其进行有效排序。

虽然您确实应该在数据库中修复数据，但您可以定义一个自定义SQLite函数，该函数可以在运行时从MM/dd/yyyy转换为yyyy-MM-dd 。所以，在@implementation之前，声明这个新函数：

void convertFromMMDDYYYY(sqlite3_context *context, int argc, sqlite3_value **argv);

然后，在@implementation的@end之后，实现这个函数：

/* convert from MM/dd/yyyy to yyyy-MM-dd */ void convertFromMMDDYYYY(sqlite3_context *context, int argc, sqlite3_value **argv) { assert(argc == 1); if (sqlite3_value_type(argv[0]) != SQLITE_TEXT) { sqlite3_result_null(context); return; } NSString *originalDateString = [NSString stringWithUTF8String:(const char *)sqlite3_value_text(argv[0])]; static NSDateFormatter *mmddyyyyFormat = nil; static NSDateFormatter *yyyymmddFormat = nil; // date formatters are a little computationally intensive to create, so let's only do it once static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ mmddyyyyFormat = [[NSDateFormatter alloc] init]; mmddyyyyFormat.dateFormat = @"MM/dd/yyyy"; yyyymmddFormat = [[NSDateFormatter alloc] init]; yyyymmddFormat.dateFormat = @"yyyy-MM-dd"; }); NSDate *date = [mmddyyyyFormat dateFromString:originalDateString]; if (date == nil) { sqlite3_result_null(context); return; } NSString *resultingDateString = [yyyymmddFormat stringFromDate:date]; char *buf = sqlite3_malloc(sizeof(char) * [resultingDateString length] + 1); strcpy(buf, [resultingDateString UTF8String]); sqlite3_result_text(context, buf, -1, sqlite3_free); }

现在，当您的应用程序打开数据库时，定义一个SQLite函数YYYYMMDD ，它将调用上述例程：

if (sqlite3_create_function_v2(database1, "YYYYMMDD", 1, SQLITE_ANY, NULL, &convertFromMMDDYYYY, NULL, NULL, NULL) != SQLITE_OK) NSLog(@"%s: sqlite3_create_function_v2 error: %s", __FUNCTION__, sqlite3_errmsg(database1));

然后，您可以更改SQL以利用这个新的SQLite函数：

NSString *query4 = @"select Birthdates,Names,Profilepic,Email,Phonenumber from PersonNamesAndBirthDates where YYYYMMDD(Birthdates) between ? and ?";

当然，由于这是在寻找yyyy-MM-dd格式，因此您应该将此格式用于此特定SQL语句中为WHERE子句绑定的值：

sqlite3_stmt *stmt4; int x4 = sqlite3_prepare_v2(database1,[query4 UTF8String],-1, &stmt4, nil); if (x4 == SQLITE_OK) { [fmt setDateFormat:@"yyyy-MM-dd"]; // ... }

同样，最好修复数据，但您可以使用此自定义函数方法。这是一种非常低效的方式，但应该有效。

The formatter matches what you've used in the database, but the problem is that that format will not sort dates chronologically. SQLite does not have a proper date data type and just instead stores the dates as strings (or also numbers, but not as true "date" data type). Worse, because your current string values are formatted as MM/dd/yyyy, it will therefore sort them alphabetically, i.e., by month, then day, then year, not chronologically.

The solution is to:

Go through your database and replace your date values, currently stored as strings in the format of MM/dd/yyyy, with string values in the format of yyyy-MM-dd.

Likewise, in your above code sample, replace:

[fmt setDateFormat:@"MM/dd/yyyy"];

with

[fmt setDateFormat:@"yyyy-MM-dd"];

You can also use one of the formats enumerated in Date And Time Functions. But pick a format such that when you sort it alphabetically, it also effectively sorts it chronologically as well.

While you really should fix your data in your database, you could define a custom SQLite function that can convert from MM/dd/yyyy to yyyy-MM-dd on the fly. So, before your @implementation, declare this new function:

void convertFromMMDDYYYY(sqlite3_context *context, int argc, sqlite3_value **argv);

Then, after the @end of your @implementation, implement this function:

Now, when your app opens the database, define a SQLite function, YYYYMMDD, which will call the above routine:

Then you can change your SQL to avail yourself of this new SQLite function:

NSString *query4 = @"select Birthdates,Names,Profilepic,Email,Phonenumber from PersonNamesAndBirthDates where YYYYMMDD(Birthdates) between ? and ?";

And, of course, since this is looking for yyyy-MM-dd format, you should use that format for the values you bind for the WHERE clause in this particular SQL statement:

sqlite3_stmt *stmt4; int x4 = sqlite3_prepare_v2(database1,[query4 UTF8String],-1, &stmt4, nil); if (x4 == SQLITE_OK) { [fmt setDateFormat:@"yyyy-MM-dd"]; // ... }

Again, it is better to fix the data, but you could use this custom function approach. It is a really inefficient way of doing it, but should work.

更多推荐