5

I currently am saving dates in my iOS application in CoreData. In Core Data, my date attribute is of type "Date". In my NSManagedObjectSubclass file, the attribute is of type: NSDate (which is fine). The date in Core Data is being stored as: 2014/05/16 14:54:51 and when I check the actual values in my SQLite3 navigator, it is being stored in the form:

421959291.293972 for the date

How do I get the above value for the date, and is there a way to convert it back to the form: 2014/05/16 14:54:51? I'm asking this because I am trying to create a dataset via a CSV file, and want to make sure that all the values that I enter in the CSV file are converted correctly by Core Data/SQLite3 into the right format.

halfer
  • 19,824
  • 17
  • 99
  • 186
syedfa
  • 2,801
  • 1
  • 41
  • 74
  • 6
    I believe NSDate's are under-the-covers stored as an NSTimeInterval (a double) from a fixed reference point (e.g. see `-[NSDate timeIntervalSinceReferenceDate]`). So what you are seeing makes perfect sense from that point of view. If you NSLog() the dates after importing them, you'll see them in a more friendly format. Your problem is coming because you're viewing them directly in storage, and that is just the representation used to store the dates. – Rob Glassey May 26 '14 at 00:38
  • I'm creating test data for my application, which is why I would like to prepopulate my SQLite3 database via a csv file with the date in the right format. By exporting the data as a csv file and finding that I am being given a date in a decimal format (i.e. timestamp), I would like to know if there is a way for me to create dates in this format(decimal/timestamp), populate my database with this value, and have it appear in my application in the correct format (2014/05/16 14:54:51)? – syedfa May 26 '14 at 14:19
  • The confusion for anyone reading this is that you mention Core Data/SQLite3 - which is it? Are you dealing with a Core Data database? In that case trying to bypass it using SQLite directly is not a recommended strategy. You're better off reading this file and using the standard Core Data APIs to enter it. However, aside from this, I believe you're trying to use NSDate without being fully aware of how it should be used - I recommend watching the recent WWDC sessions on date/calendar calculations and reading the programming guide. This topic is more complex than many people give it credit. – Rob Glassey May 26 '14 at 15:25
  • Core Data is what I'm using as part of my application, however, I'm using a SQLite3 database explorer to view the data that is being stored in my application. This is how I am able to view the raw data. – syedfa May 26 '14 at 15:28
  • 1
    Duplicate of other stackoverflow questions http://stackoverflow.com/questions/10705062/behind-the-scenes-core-data-dates-stored-with-31-year-offset. – thom_ek May 28 '14 at 09:42
  • You can convert the decimal found in the sqlite to a real date time using http://www.epochconverter.com/coredata – J3RM Oct 27 '16 at 16:55

1 Answers1

14

if you are using sqlite3 as the command line tool, use the the datetime function to interpret the data, so for example

sqlite3> .schema
create table ZENTITY (... ZSTARTDATE TIMESTAMP, ...)
sqlite3> select datetime(zstartdate,'unixepoch','31 years') from ZENTITIY;

This should give the formatted timestamp stored in the entity table

The unix epoch/31 years arguments come from this stack overflow question. Behind The Scenes: Core Data dates stored with 31 year offset?

Community
  • 1
  • 1
Frank Cornelisen
  • 166
  • 1
  • 5
  • For others coming here looking for tips on interpreting dates stored in sqlite by iOS apps: some apps (stupidly) save timestamps as local time rather than UTC; in that case you need to add a `'localtime'` parameter to that `datetime` call, e.g. `datetime(foo, 'unixepoch', '31 years', 'localtime')` - particularly tricky for UK users (say) where localtime == UTC for half the year but not the other half! – gimboland Nov 14 '18 at 15:08