Hi Kor
Apple's Core Data uses a reference date of 1 January 2001, GMT - 31 years ahead of Unix Epoch time. Times are stored in the database as a decimal value of the elapsed seconds since that reference date. You'll just need to translate that +31 years from UET.
There is only 1 many-to-many relationship in the database: between ZCUSTOMROASTERSETTING and ZROAST - handled by the Z_13ROASTS join table.
Beyond that, I've modeled several join tables to facilitate the weight or volume of a few key one-to-many relationships. They are:
ZROAST -> ZROASTEDITEM -> ZBEAN (or ZBLEND)
ZCUPPING -> ZCUPPEDITEM -> ZROAST (or ZBLEND)
ZBLEND -> ZBLENDEDITEM -> ZBEAN (or ZROAST)
Due to its portability, SQLite lacks much of the functionality of other SQL engines, so... your code will be cleaner, but using raw SQLite to select date, market name and duration of every roast that was started on Feb 27th would be
SELECT datetime(r.ZDATE, 'unixepoch', '31 YEARS', 'localtime') as 'Date',
b.ZMARKETNAME as 'Market Name',
(cast(cast(r.ZDURATION/60.0 as int) as string) || ':' || cast(ZDURATION%60 as string)) as 'Duration'
from ((ZROAST r join ZROASTEDITEM i on i.ZROAST = r.Z_PK) join ZBEAN b on i.ZBEAN = b.Z_PK)
where ZDATE >= (strftime('%s','2014-02-27 00:00:00 -05:00') - strftime('%s','2001-01-01 00:00:00'))
AND ZDATE <= (strftime('%s','2014-02-27 23:59:59 -05:00') - strftime('%s','2001-01-01 00:00:00'));
ORDER BY ZDATE
Note the locale offsets - I'm in -5
Using SQLite in Apple's Core Data, I work in predicates, which are akin to SQL, but much cleaner than this. You'd be well off defining a method to take a date and return the seconds offset from 1/1/1. Doing that, the code becomes much cleaner.
Hope this helps - let me know.
