Persisting Dates to SQLite3 in an iPhone Application

Question

I am developing an iPhone application that persists data to a SQLite3 database.

For each row I persist I wish to include a 'created date' and a 'last modified date'

My question is what is the recommend approach for storing this information in a table?

The properties are represented as NSDate in my application but I am unsure how to represent this information in my table.

It appears that SQLite3 provides a DATETIME type but does not have a native understanding of how to parse this information.

Any help would be much appreciated.

Thanks in advance.

Answer 1

According to the docs there's no DATETIME data type! I use a real data type and convert an NSDate to a real using the timeIntervalSinceReferenceDate method.

Answer 2

I typically use a double, something like:

sqlite3_bind_double(statement, index, [dateObject timeIntervalSince1970]);

where dateObject is an NSDate*. Then, when getting the data out of the DB, use

[NSDate dateWithTimeIntervalSince1970:doubleValueFromDatabase];

Answer 3

Store as a typical C-time (e.g. the time_t/int value returned by time()) value in an UNSIGNED INT column. Convert like drewh said with NSDate:

- timeIntervalSince1970
- dateWithTimeIntervalSince1970:(double)value

Except store as an integer, unless you need sub-second granularity (which, generally, for created-on/last-modified, is overkill). Unsigned ints are a lot smaller and easier to process than doubles. While this may not matter in most desktop and web applications anymore, it certainly helps on the iPhone. Every little bit helps.

One side benefit of C-times that most people don't realize is that they're timezone-agnostic. If you ever need to support multiple timezones, this comes in handy.

Answer 4

I convert to/from ISO8601 strings: http://en.wikipedia.org/wiki/ISO_8601

Far more readible/hackable than time intervals.

Answer 5

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