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.

Answer 3

If you just want to get an NSDate from an SQLite datetime field, where dateTime is the field returned from the database:

NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSDate *date = [dateFormatter dateFromString:dateTime];

Answer 4

Please note that the category solution above has a problem in that it is subject to the locale settings on the user's device. For example, for midnight April 5th 2010 sqlLiteDateRepresentation above would return 2010/04/05 00:00:00 for most people's machines, however I have encountered a scenario where a user's locale settings caused the same function to produce "2010/04/05 12:00:00 a.m." which when used in my query does not return any rows. This seems to follow from the documentation of NSDateFormatter: "In general, you are encouraged to use format styles (see timeStyle, dateStyle, and NSDateFormatterStyle) rather than using custom format strings, since the format for a given style reflects a user’s preferences. Format styles also reflect the locale setting." Although I didn't see a good way to use the timeStyle/dateStyle to get the same format as yyyy/MM/dd HH:mm:ss that SQLite seems to need. I fear your best bet is likely a custom solution where you ensure that the time is definitely written in 24H format, don't allow locale settings to cause bugs.

Answer 5

Now Work!

NSString *dateValueS = [[NSString alloc] 
     initWithUTF8String:(char*) sqlite3_column_text(statement,2)];