How get a datetime column in SQLite with Objective C

Question

How do you get a datetime column in SQLite with Objective C?

I have a table with 4 fields: pk, datetime, value1 and value2. pk (primary key), value1 and value2 are integers so I am using:

   int value1 = sqlite3_column_int(statement, 2);
   int value1 = sqlite3_column_int(statement, 3);

But what should I use for datetime?

Answer 1

In SQLite, there is no date/time column type per se, so one ends up representing dates either as Julian date values (real columns) or in strings (text columns). SQLite is also very particular in how dates are represented in strings, yyyy-MM-dd HH:mm:ss (only).

These are some methods that I wrote for working with SQLite dates from Objective-C. These methods are implemented in a category on NSDate.

Be sure to check out the functionality that SQLite offers for working with Julian dates. I have found these to be quite useful (http://www.sqlite.org/lang_datefunc.html). A function for deriving an NSDate's julianDay is included in the code example.

It looks like this subject was also covered here. Persisting Dates to SQLite3 in an iPhone Application

+ (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString;
{
    NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);

    return [[self sqlLiteDateFormatter] dateFromString: myString];
}

+ (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString timeZone: (NSString *) myTimeZone;
{
    NSString * dateWithTimezone = nil;
    NSDate * result = nil;

    NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);
    NSAssert3(myTimeZone, @"%s: %d; %s; Invalid argument. myTimeZone == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);

    dateWithTimezone = [[NSString alloc] initWithFormat: @"%@ %@", myString, myTimeZone];
    result = [[self sqlLiteDateFormatterWithTimezone] dateFromString: dateWithTimezone];
    [dateWithTimezone release];

    return result;
}

+ (NSString *) sqlLiteDateFormat;
{
    return @"yyyy-MM-dd HH:mm:ss";    
}

+ (NSString *) sqlLiteDateFormatWithTimeZone;
{
    static NSString * result = nil;

    if (!result) {
        result = [[NSString alloc] initWithFormat: @"%@ zzz", [self sqlLiteDateFormat]];
    }

    return result;    
}

+ (NSDateFormatter *) sqlLiteDateFormatter;
{
    static NSDateFormatter * _result = nil;

    if (!_result) {
        _result = [[NSDateFormatter alloc] init];
        [_result setDateFormat: [self sqlLiteDateFormat]];
    }

    return _result;
}

+ (NSDateFormatter *) sqlLiteDateFormatterWithTimezone;
{
    static NSDateFormatter * _result = nil;

    if (!_result) {
        _result = [[NSDateFormatter alloc] init];
        [_result setDateFormat: [self sqlLiteDateFormatWithTimeZone]];
    }

    return _result;
}


- (NSString *) sqlLiteDateRepresentation;
{
    NSString * result = nil;

    result = [[NSDate sqlLiteDateFormatter] stringFromDate: self];

    return result;
}

- (NSTimeInterval) unixTime;
{
    NSTimeInterval result = [self timeIntervalSince1970];

    return result;
}

#define SECONDS_PER_DAY 86400
#define JULIAN_DAY_OF_ZERO_UNIX_TIME 2440587.5
- (NSTimeInterval) julianDay;
{
    return [self unixTime]/SECONDS_PER_DAY + JULIAN_DAY_OF_ZERO_UNIX_TIME;
}

+ (NSDate *) dateWithJulianDay: (NSTimeInterval) myTimeInterval
{
    NSDate *result = [self dateWithTimeIntervalSince1970: (myTimeInterval - JULIAN_DAY_OF_ZERO_UNIX_TIME) * SECONDS_PER_DAY];

    return result;
}

Answer 2

If you can define the database, then ou could also use REAL (SQLite data type) as the type for the datetime, then load it with sqlite3_column_double(). This will return a variable of the type double.

Then you can use [NSDate dateWithTimeIntervalSince1970:double_value] to get an NSDate object.