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?

Answer 1

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)];

Answer 2

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 :)

Answer 3

I like to use the sqlite julianday() fuction, like:

insert into foo(currentDate) values (julianday('now'));

This would be inserting a floating point value into foo.currentDate.

You might also find some details of this question/answer useful.

http://stackoverflow.com/questions/1711504/how-get-datetime-column-in-sqlite-objecite-c/1711591#1711591