Read and Write NSDate to sqlite database through FMDatabase

Question

I have a doubt whether NSDate can be directly stored or read from the database using FMDatabase or not.

As per my research, FMDatabase reads date from record expecting that tuple to be a double value. FMDatabase converts that double value to date in this method:

- (NSDate*) dateForColumn:(NSString*)columnName; {
    int columnIdx = [self columnIndexForName:columnName];

    if (columnIdx == -1) {
        return nil;
    }

    return [NSDate dateWithTimeIntervalSince1970:[self doubleForColumn:columnName]];
}

The main problem with this approach is, it would be difficult for the person who is manually entering data in the database so that it would be understandable by FMDatabase in the code. So someone cannot simply enter the date in double value in GUI. One approach is to use one of these functions

date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

But the overhead here is again to run a query each time for entering a record using insert operation similar to this:

Compute the current date.
SELECT date('now');

Compute the last day of the current month.
SELECT date('now','start of month','+1 month','-1 day');

I would like the date to be stored in the database in following format:

TEXT as ISO8601 strings ("YYYY-MM-DD")

And store it as Date format in the database so that I can:

1. Leverage the date comparison facility in queries.
2. Make FMDatabase understandable to read and write this date format.

Is there any direct way where FMDatabase can interact with this kind of date format? Or is this a limitation of FMDatabase and I will have to extend the FMDatabase class to handle reading and writing back of NSDate to the sqlite date format?

Thanks

Edit: One of the author / moderator of FMDatabase has to say this about it: http://code.google.com/p/flycode/issues/detail?id=16#c1

Answer 1

Reading date from database which is entered in the format YYYY-MM-DD.

I have extended the FMResultSet class to include the following additional methods:

FMResultSet+Addtion.h file:

typedef enum
{
    eText,
    eReal,
    eInteger
}EDateAndTimeDataType;
// Reference: http://www.sqlite.org/datatype3.html

typedef enum
{
    eDateString    // YYYY-MM-DD format
    // Rest is un implemented
}EDateAndTimeFunctionType;
// Refernce: http://www.sqlite.org/lang_datefunc.html

@interface FMResultSet (Additions)

-(NSDate*)dateForColumn:(NSString *)columnName withDataType:(EDateAndTimeDataType)inDateDataType functionType:(EDateAndTimeFunctionType)inFunctionType;
-(NSDate*)dateForColumnIndex:(int)columnIdx withDataType:(EDateAndTimeDataType)inDateDataType functionType:(EDateAndTimeFunctionType)inFunctionType;

@end

FMResultSet+Addition.m file:

@implementation FMResultSet (Additions)

-(NSDate*)dateForColumn:(NSString *)columnName withDataType:(EDateAndTimeDataType)inDateDataType functionType:(EDateAndTimeFunctionType)inFunctionType
{
    return [self dateForColumnIndex:[self columnIndexForName:columnName]
                       withDataType:inDateDataType
                       functionType:inFunctionType];
}

-(NSDate*)dateForColumnIndex:(int)columnIdx withDataType:(EDateAndTimeDataType)inDateDataType functionType:(EDateAndTimeFunctionType)inFunctionType
{
    NSAssert (eText==inDateDataType, @"Only Text type is implemented for now");
    NSAssert (eDateString==inFunctionType, @"Only date() function type is implemented for now");

    NSDate *theDate = nil;
    NSString *dateString = [self stringForColumnIndex:columnIdx];
    if (nil!=dateString)
    {
        theDate = [Utility getDateFromString:dateString
                              withDateFormat:@"yyyy-MM-dd"];
        NSLog(@"Date from DB: %@", theDate);
    }
    return theDate;
}

@end

Utility:

+(NSDate *)getDateFromString:(NSString *)inString withDateFormat:(NSString*)inDateFormat
{
    NSDate *date =nil;
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    [dateFormatter setDateStyle:NSDateFormatterMediumStyle];
    [dateFormatter setDateFormat:inDateFormat];

    NSLocale *enUSPOSIXLocale = [[NSLocale alloc] initWithLocaleIdentifier:@"en_US_POSIX"];
    [dateFormatter setLocale:enUSPOSIXLocale];
    [dateFormatter setTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];
    [enUSPOSIXLocale release];

    date = [dateFormatter dateFromString:inString];
    [dateFormatter release];
    return date;
}

**Edit: Though this works for queries like "select * from table_name" it fails in queries comparing for dates. Need solutions for this.