Info regarding CoreData build SQLite tables for dates

CoreData SQLite date queries

In trying to debug and NSPredicate I typically find myself delving into the sqlite commandline interface to run the queries manually to see what happens.  I just now ran into doing some queries dealing with timestamps which led to alot of frustration, so I hope this helps someone.

1. NSDate uses 01/01/2001 00:00:00 GMT as it's reference date, this is very important to know.  This date converts to 978307200 as a unix timestamp, which is a number you will be using later.

2. CoreData, stores dates as timestamps from the above reference date.

3. SQLite has a couple of date functions that work off of the unix epoch, which is 1/1/1970 00:00:00 GMT.  Using these functions you can effectively query your CoreData store.

4. The strftime('%s', 'now') function will return to you the current time as a timetamp from the unix epoch, unfortunately this is returned as a string so you'll need to cast it to use in comparison query.

e.g.
select * from ZENROLLMENT where (ZSTARTDATE + 978307200) <= (strftime('%s', 'now') + 0) and (ZENDDATE + 978307200) >= (strftime('%s', 'now') + 0);