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.

Wendolynwendt

Answer 1

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

Daukas

I am trying to access using FMDatabase, but I am entering records using a GUI, each time I do not get any records. please check my question and suggest: #8773101 – Westhead

Answer 2

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.

Wove

Answer 3

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

Far more readible/hackable than time intervals.

Irena

Same here. Sure it's a little more space-heavy, but it's way nicer to read in a debugging session. – Smattering

How do you NSPredicate date range searches on these? – Arietta

Answer 4

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.

Glidewell

Answer 5

How get a datetime column in SQLite with Objective C

Rozele