Issues converting DATETIME using SQLITE3 and Objec

问题:

I have a sqlite3 Database with a DATETIME column containing values formatted like this:
2013-01-09 04:00:00

I'm trying to figure out how to determine if times are AM or PM?

Here's how I read and convert the DATETIME objects from my Sqlite3 DB using Objective-C:

NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"yyyy-MM-dd hh:mm:ss"];
char *DateTimeColumn = (char *)sqlite3_column_text(statement, 13);
NSDate *myDate =[dateFormat dateFromString:[NSString stringWithUTF8String:DateTimeColumn]];

This works really well except it doesn't figure out whether this is 4:00AM or 4:00PM.

I saw a posting suggesting adding an a at the end of the formatter string like so:
"yyyy-MM-dd hh:mm:ss a"
... to figure out AM/PM - but that caused crashes. (I of course also added AM and PM to the actual values in my DATETIME column like so:
2013-01-09 04:00:00 AM
but its still crashed.

Not sure if I'm doing something wrong - or if this just can't be done.

Is the only solution to have the entries in the DB itself all be in the 24hr. format (meaning: 4:00PM should be entered as 16:00) - and then use IF statements to subtract 12 from values > 12 so as to determine a time's AM or PM'ness?
I would have thought there'd be some automated function for this... Any ideas?

=================================

EDIT/UPDATE:
I'm suddenly getting a '(null)' after converting the string into an NSDate. Here's the current version of the code:

NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"yyyy-MM-dd hh:mm:ss"];
char *DateTimeColumn = (char *)sqlite3_column_text(statement, 13);
NSString *DateTimeString = [NSString stringWithUTF8String:DateTimeColumn];
NSLog(@"DateTimeString = %@", DateTimeString);
   // This NSLog gives me a good string: "DateTimeString = 2013-01-09 16:00:00"
// But when I create an NSDate object from this string:
NSDate *myDate =[dateFormat dateFromString:DateTimeString];
// ... and log it out: 
NSLog(@"the REAL DATETIME object = %@", [myDate description]);
// the console shows: "the REAL DATETIME object = (null)"
// and when I add this NSDate object to my Array of NSDates, I crash:
[tempNSDateObjectsArray addObject:myDate];

Console shows: 
*** Terminating app due to uncaught exception 'NSInvalidArgumentException',     
reason: '*** -[__NSArrayM insertObject:atIndex:]: object cannot be nil'
*** First throw call stack:

回答1:

HH, not hh, is for the 24-hour date format:

[dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

Parsing "2013-01-09 16:00:00" with hh as hour-format fails and therefore returns nil.

Note that NSDateFormatter interprets the date string according to the local timezone, unless you set an explicit time zone. You might want to add the time zone information to the strings, or use different format (e.g. UNIX time, seconds since 1.1.1970) to store the date.

---

ADDED: The SQLite datetime() function returns a string "YYYY-MM-DD HH:MM:SS" which is the date and time in UTC/GMT. To convert such a string to a NSDate, use a NSDateFormatter and set the time zone to GMT:

NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
[dateFormat setTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];

NSString *dateTimeString = @"2013-01-09 16:00:00";
NSLog(@"DateTimeString = %@", dateTimeString);

NSDate *myDate =[dateFormat dateFromString:dateTimeString];
NSLog(@"myDate:          %@", myDate);

Output:

dateTimeString = 2013-01-09 16:00:00
myDate:        = 2013-01-09 16:00:00 +0000

To present a date to the user, convert NSDate to NSString, using stringFromDate, but do not set a time zone (so that the local time zone is used):

NSDateFormatter *dateFormat2 = [[NSDateFormatter alloc] init];
[dateFormat2 setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

NSString *localDateString = [dateFormat2 stringFromDate:myDate];
NSLog(@"localDateString: %@", localDateString);

Output:

localDateString: 2013-01-09 17:00:00

The time is now shown as 17:00:00, because my local time zone is "GMT+01".

回答2:

The 24-hour format is the only format valid in SQLite date fields.

Please note that the strings in the database are not intended to be directly displayed to the user; modifying them for the user's time zone and formatting preferences is the application's job.