How does the iPhone SDK Core Data system store date types to sqlite?

Question

I used core data to do this:

NSManagedObjectContext *m = [self managedObjectContext];
Foo *f = (Foo *)[NSEntityDescription insertNewObjectForEntityForName:@"Foo" 
                                                        inManagedObjectContext:m];
f.created_at = [NSDate date];
[m insertObject:f];

NSError *error;
[m save:&error];

Where the created_at field is defined as type "Date" in the xcdatamodel.

When I export the sql from the sqlite database it created, created_at is defined as type "timestamp" and the values look like:

290902422.72624

Nine digits before the . and then some fraction.

What is this format? It's not epoch time and it's not julianday format.

Epoch would be:

1269280338.81213

julianday would be:

2455278.236746875 (notice only 7 digits before the . not 9 like I have)

How can I convert a number like 290902422.72624 to epoch time? Thanks!

Answer 1

First, note that the Core Data documentation says you should never touch the SQL or values it generates on your own - doing so has the potential to invalidate your model if you make changes to it, and it's difficult to parse in the first place.

That said, what you may be seeing is dates relative to January 1, 2001 in GMT. The documentation for NSDate specifies that the single primitive method, timeIntervalSinceReferenceDate, uses that time for its reference. Core Data, in turn, uses the NSDateAttributeType to store date types, which is defined to be an NSDate object.

Running your value through a calculator produces:

290902422.72624 / 60 / 60 / 24 / 365.25 = 9.21814...

which is about the number of years that's elapsed since that reference date.

If you really need to parse that value back into an epoch time you can use the method initWithTimeIntervalSinceReferenceDate: with your SQLite-stored number to get an NSDate, then call timeIntervalSince1970 to get epoch seconds back (in an NSTimeInterval struct).

Answer 2

I have run into a related issue of trying to compare Core Data stored date to MySQL stored date in a remote API. My solution was to use SQLite's date functions to convert the time:

SELECT datetime('2001-01-01','+290902422.72624 second');

Returns:

2010-03-21 22:13:42

SQLite assumes the date is in local time and converts it to UTC/GMT. If you want it to remain in local time, use the local time modifier:

SELECT datetime('2001-01-01','+296662599 second','localtime');

Returns:

2010-03-21 17:13:42

This shows the -0500 offset for my local TZ.

Building on that you can use SQLite's strftime function with the format '%s' to get epoch time back:

SELECT strftime('%s',datetime('2001-01-01','+290902422.72624 second'));

Returns:

1269209622

I hope this helps.