SQLite數(shù)據(jù)庫支持的類型
| storage classes | description |
|---|---|
| NULL | The value is a NULL value |
| INTEGER | The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
| REAL | The value is a floating point value, stored as an 8-byte IEEE floating point number. |
| TEXT | The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). |
| BLOB | The value is a blob of data, stored exactly as it was input. |
從表中可以看出SQLite本身是不支持date類型的抵拘，在使用FMDB操作數(shù)據(jù)庫時，如果需要存儲NSDate類型數(shù)據(jù)時些阅，可能我們會考慮按TEXTl類型存儲试浙，在執(zhí)行插入時先將date轉成時間戳或者字符串然后再執(zhí)行插入操作，事實上不用這么復雜物舒，F(xiàn)MDB已經(jīng)幫我們做了相應的轉換處理。
詳見FMDataBase.m
...
else if ([obj isKindOfClass:[NSDate class]]) {
if (self.hasDateFormatter)
sqlite3_bind_text(pStmt, idx, [[self stringFromDate:obj] UTF8String], -1, SQLITE_STATIC);
else
sqlite3_bind_double(pStmt, idx, [obj timeIntervalSince1970]);
}
...
首先我們設計表結構的時候需要把待存儲的時間字段指定為TEXT類型，
比如：
NSString *createtbSql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' (\
'%@' INTEGER PRIMARY KEY AUTOINCREMENT, \
'%@' TEXT NOT NULL, \
'%@' TEXT NOT NULL, \
'%@' BLOB, \
'%@' TEXT, \
'%@' TEXT)",
tbname ,
@"ID" ,
@"userid",
@"content",
@"links",
@"create_at",
@"last_modified"];
BOOL res = [self.database executeUpdate: createtbSql];
插入數(shù)據(jù)時闻牡，我們直接傳NSDate對象。
success = [self.database executeUpdate:@"INSERT INTO topic_drafts (pin, content, links, create_at, last_modified) VALUES (?, ?, ?, ?, ?)",
model.pin,
model.content,
linksdata,
[NSDate date],
[NSDate date]];
if(!success){
DebugLog(@"INSERT failed, %@", [self.database lastError]);
}
讀取時使用庫方法dateForColumn:即可獲取存儲的NSDate對象
NSDate * create_at = [rs dateForColumn:@"create_at"];
默認情況下會將NSDate對象轉成時間戳存儲在表中绳矩，如下圖第二行所示
datetime.jpg
當然我們也可以將存儲的數(shù)據(jù)指定為格式化的時間字符串
+ (void)initialize
{
if (self == [DBPersistentManager class]) {
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
__dbDateFormatter = [[NSDateFormatter alloc] init];
[__dbDateFormatter setLocale:[NSLocale currentLocale]];
[__dbDateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
});
}
}
- (instancetype)init
{
self = [super init];
if (self) {
mdatabase = [FMDatabase databaseWithPath:[self dbPath]];
if(mdatabase){
[mdatabase setDateFormat:__dbDateFormatter];
}
}
return self;
}
這樣存儲在數(shù)據(jù)表里的數(shù)據(jù)顯示就如上圖第一行所示罩润。
