sqlite datetime data type with iphone NSdate?

Question

Is there a way in sqlite to automatically save a timestamp in the table whenever a record has been created that I can then grab and save as an NSDate in my application?

Or should I instead create the NSdate object with the current time in my iphone app, and then insert it into the database. If so? What column should the datatype be for the time? a DATETIME?

Solution 1

What I do is use sqlite's current_timestamp (which looks something like this: 2009-06-16 12:11:24). To do this just set the row type of your qslite table to

Data type: "DATETIME"
Allow null: NO
Default value: CURRENT_TIMESTAMP

Then use an SQL query like this:

@"INSERT INTO 'scores' ('one', 'two', 'three') VALUES ('%d', '%d', '%d')"

ignoring the date, so that it will automatically get the current time value.

Then to convert this to an NSDate you can use an NSDateFormatter like this:

NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
[formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; //this is the sqlite's format
NSDate *date = [formatter dateFromString:score.datetime];

and now you have that date as an NSDate object. Just don't forget to release the NSDateFormatter we allocated :)

Solution 2

There is no TIMESTAMP datatype that you can use in SQLite, so you'll have to manually insert the time when you do your INSERT. I use the INTEGER datatype in the database, and convert my NSDate as such:

sqlite3_bind_double(insert_statement, 1, [myDate timeIntervalSince1970]);

And to get it back out of the DB:

myDate = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(select_statement, 1)];

Comments

[Thiru]

I am afraid, this is incorrect, there is Datetime datatype in SQLite.