iPhone SQLite3 Datetime Question

What is the best format to store a date in, for easy formatting later using the Objective-C dateformatter? The date will start as a unix timestamp, then become whatever f开发者_JAVA技巧ormat is most easily worked with, stored in an SQLite3 db, then retrieved and manipulated with a dateformatter. Can I just use a timestamp? Or is it better to convert it into, say, YYYY/mm/dd, or something along those lines.

I think you'll want to store it in format YYYYMMDDHHMMSS for easy sortability.

Are you writing sqlite code directly? Why not use a managed data framework such as Core Data (underlying data store is sqlite), would make your life much easier. This little example uses NSSortDescriptor to sort by FileDate column. AssetItem is an NSManagedObject subclass

NSFetchRequest *nsrequest = nil;
NSEntityDescription *entity = nil;

NSError *error; 

// check to see if the scene exists (could have been downloaded previously)
nsrequest = [[NSFetchRequest alloc] init];
entity = [NSEntityDescription entityForName:@"AssetItem" inManagedObjectContext:managedObjectContext];          
[nsrequest setSortDescriptors:[NSArray arrayWithObject:[[[NSSortDescriptor alloc] initWithKey:@"FileDate" ascending:NO] autorelease]]];     
[nsrequest setEntity:entity];

NSArray *objs = [[managedObjectContext executeFetchRequest:nsrequest error:&error] retain];

for (AssetItem *item in objs) {
    NSLog(@"file date is: %@", [item FileDate]);
}
[objs release];
[nsrequest release]

;

You can do much modeling using the built-in Xcode data modeler tools.