How do I get a date or datetime out of an SQLite3 row?

Question

There is no function as sqlite3_column_date which I need:

int i = 0;
for (NSString *key in [self valuesWithTypes]) {
  id value;
  if ([[[self valuesWithTypes] objectForKey:key] isKindOfClass:[NSString class]]) {
    value = [[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, i)] autorelease];
  }
  else if ([[[self valuesWithTypes] objectForKey:key] isKindOfClass:[NSNumber class]]) {
    value = [[NSNumber numberWithDouble:sqlite3_column_double(statement, i)] autorelease];
  }
  else if ([[[self valuesWithTypes] objectForKey:key] isKindOfClass:[NSDate class]]) {
//  value = // WTF
  }
  else if ([[[self valuesWithTypes] objectForKey:key] isKindOfClass:[NSData class]]) {
    value = [[NSData dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement, i)] autorelease];
  }
  [rowDictionary setValue:value forKey:key];
  i++;
}

SQLite3 tables can have a date/datetime typed column. But how, in the name of the holy Lord, can I get a date column out of a row?

Thanks

Answer 1

According to SQLite documentation there is no date/datetime column.

In that page three options are suggested:

• TEXT
• REAL
• INTEGER

TEXT may be the prettier option if you need to look at the data directly.
I've never used REAL
I like INTEGER and UNIX timestamps :-)

32-bits UNIX timestamps have a problem looming very near (no issue with 64-bits timestamps) and they don't reflect wall-clock time accurately. If a second too much or too little every now and then isn't a problem for you, I'd suggest this option.