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

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 frac开发者_JS百科tion.

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!

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

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.

Just add 978307200 to the number in core data and you'll get normal timestamp

NSString *coreDataTimestamp =@"464615485.832736"; //string of timestamp in coredata
NSTimeInterval timestamp = [coreDataTimestamp doubleValue] + 978307200;

For those interested in converting dates stored in Core Data to Excel, I came up with this excel forumla, I think its right, it might be off by an hour with the GMT stuff if someone else could confirm that would be cool

=(A1/86400) +35430.042

Here's a website that lets you paste those date numbers and shows you the actual date: http://blog.paddlefish.net/?page_id=90

Or, here's how to read those sqlite dates with ruby:

ruby -e "require 'time'; puts Time.parse('2001-01-01 00:00:00 -0000') + 123"

Replace 123 with the value in the date column.

If you're trying to get the data directly from the SQLite command line, you would do

select datetime('2001-01-01', dateColumn || 'seconds') from table; or select datetime('2001-01-01', dateColumn || 'seconds', 'localtime') from table;