SQLite and NSDate time zones

[Scenario]

I'm having trouble understanding the way NSDate handles (and requires) time zones.

I have integrated a large sqlite3 database with my iPhone app. I store my dates in the standard SQL format "2009-03-24 18:44:13". SQL doesn't seem to care about time zones and that's fine with me.

When I load the textual representation of a date/time into an NSDate object, I use a line of code like this:

date = [NSDate dateWithString:[NSString stringWithFormat:@"%@ -0600", [NSString stringWithUTF8String:(char *)sqlite3_column_text(hydrate_statement, 1)]]];

This creates an NSDate object represented as "2009-03-24 18:44:13 -0600". Then when I write my dates back to SQL as text, I simply trim off the " -0600".

The obvious problem is, the "-0600" offset works for my location but probably not my user's. And worse, when I load a bunch of dates one at a time into a UIDatePicker in my app, they can be off by an hour depending on whether the system thinks Daylight Savings Time is in effect on that particular date. To explain this another way, last month I had to hard code "-0700" instead of "-0600".

I've tried using the following line of code to effectively turn off time zone support:

[NSTimeZone setDefaultTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];

That seems to work for dates I load from my database but any dates I create via [NSDate date] or edit via UIDatePicker get screwed up by 6 hours.

Can anyone give me some advice?

Please note that I don't want to store my dates as doubles. I want them to be human-readable when browsing the database in something like the Terminal app. I just want all times to be interpreted as "the local time in the user's time zone" across all records.

Do I need to determine the time zone offset for each particular date before I create an NSDate from loaded text? If so that seems like a major pain but I suspect [NSTimeZone secondsFromGMTForDate] might be designed for this problem.

Thanks in advance,
Todd

[Scenario]

I think I answered my own question here. This appears to work:

+ (NSString *)offsetForDate:(NSDate *)date {
	NSInteger seconds = [[NSTimeZone defaultTimeZone] secondsFromGMTForDate:date];
	seconds /= 36;
	NSNumberFormatter *formatter = [[NSNumberFormatter alloc] init];
	formatter.formatWidth = 5;
	formatter.paddingCharacter = @"0";
	formatter.paddingPosition = NSNumberFormatterPadAfterPrefix;
	NSString *offset = [formatter stringFromNumber:[NSNumber numberWithInteger:seconds]];
	return offset;
}

+ (NSDate *)dateWithSQLString:(NSString *)dateAndTime {
	if (!dateAndTime) return nil;
	NSDate *date;
	if (dateAndTime.length == 10)
		date = [NSDate dateWithString:[NSString stringWithFormat:@"%@ 00:00:00 +0000", dateAndTime]];
	else if (dateAndTime.length == 19)
		date = [NSDate dateWithString:[NSString stringWithFormat:@"%@ +0000", dateAndTime]];
	else
		return nil;
	NSString *offset = [self offsetForDate:date];
	date = nil;
	if (dateAndTime.length == 10)
		date = [NSDate dateWithString:[NSString stringWithFormat:@"%@ 00:00:00 %@", dateAndTime, offset]];
	else if (dateAndTime.length == 19)
		date = [NSDate dateWithString:[NSString stringWithFormat:@"%@ %@", dateAndTime, offset]];
	return date;
}

For the Mountain Time Zone (where Daylight Savings Time is observed):

NSDate *date = [self dateWithSQLString:@"2009-01-01 19:00:00"];

This properly returns a date of: "2009-01-01 19:00:00 -0700"

NSDate *date = [self dateWithSQLString:@"2009-03-24 19:00:00"];

This properly returns a date of: "2009-03-24 19:00:00 -0600"

And finally, here's the call I now make from several of my SQL database objects:

self.date = [Database dateWithSQLString:[NSString stringWithUTF8String:(char *)sqlite3_column_text(init_statement, 0)]];

So I think this will solve the problem in UIPicker and elsewhere, across a whole range of dates within the user's time zone. I hope someone finds this useful.

[Scenario]

Here's a more elegant solution without all the parsing and with only a single method call:

+ (NSDate *)dateWithSQLString:(NSString *)string {
	NSDate *date = [NSDate dateWithNaturalLanguageString:string];
	if (!date) return nil;
	NSCalendar *calendar = [[[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar] autorelease];
	[calendar setTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];
	NSDateComponents *components = [calendar components:(
		NSYearCalendarUnit |
		NSMonthCalendarUnit |
		NSDayCalendarUnit |
		NSHourCalendarUnit |
		NSMinuteCalendarUnit |
		NSSecondCalendarUnit)
		fromDate:date];
	[calendar setTimeZone:[NSTimeZone defaultTimeZone]];
	date = [calendar dateFromComponents:components];
	return date;
}