fmdb插入日期给出“无效日期”

I'm trying to use fmdb in my latest ios-project and it worked great so far. But now i'm trying to do inserts with an NSDate as a parameter and it ends up being stored as an invalid date in my sqlite database. I have tried multiple solutions, but nothing seems to work. What am i doing wrong?

我正在尝试在我最新的ios项目中使用fmdb,到目前为止它运行良好。但是现在我正在尝试使用NSDate作为参数进行插入,并最终在我的sqlite数据库中存储为无效日期。我尝试了多种解决方案,但似乎没有任何效果。我究竟做错了什么?

[database executeUpdate:@"CREATE TABLE IF NOT EXISTS inspection (ID INTEGER PRIMARY KEY, result TEXT, date_of_inspection DATE, restaurant_id INTEGER"];

NSDate *today=[NSDate date];
[database executeUpdate:@"insert into inspection(id,result,date_of_inspection,restaurant_id) values(?,?,?,?)",[inspection valueForKey:@"id"], result, today, [restaurant valueForKey:@"id"],nil];

The database is populated and everything works fine except for the *#%! date... :) What is wrong?

数据库已填充,一切正常,但*#%除外!约会...... :)有什么不对?

2 个解决方案

#1

SQLite doesn't have a date format, so that FMDB expects the date fields to be stored as floats/real numbers:

SQLite没有日期格式,因此FMDB希望将日期字段存储为浮点数/实数:

else if ([obj isKindOfClass:[NSDate class]]) {
    sqlite3_bind_double(pStmt, idx, [obj timeIntervalSince1970]);
}

So try making your table with that format.

所以尝试用这种格式制作你的表格。

#2

Try this:

[database executeUpdate:@"CREATE TABLE IF NOT EXISTS inspection(ID INTEGER PRIMARY KEY, result TEXT, date_of_inspection REAL, restaurant_id INTEGER"];

NSDate *today=[NSDate date];
[database executeUpdate:@"insert into inspection(id,result,date_of_inspection,restaurant_id) values(?,?,?,?)",[inspection valueForKey:@"id"], result, [today timeIntervalSince1970], [restaurant valueForKey:@"id"],nil];

SQLite does not support the DATE data type (http://www.sqlite.org/datatype3.html). You must store it as a REAL data type. timeIntervalSince1970 returns the data type NSTimeInterval, which is a double ( https://developer.apple.com/library/mac/documentation/cocoa/reference/foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html).

SQLite不支持DATE数据类型(http://www.sqlite.org/datatype3.html)。您必须将其存储为REAL数据类型。 timeIntervalSince1970返回数据类型NSTimeInterval,这是一个双精度数据(https://developer.apple.com/library/mac/documentation/cocoa/reference/foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html)。

#1