How to calculate streak of days in core data?

Question

I need to count last streak of days, but can't figure out how to do this. For example I got core data like this:

|id| isPresent(Bool)| date(NSDate)|
|==|================|=============|
| 1|               0| 2016-02-11  |
| 2|               1| 2016-02-11  |
| 3|               1| 2016-02-12  |
| 4|               0| 2016-02-14  |
| 5|               1| 2016-02-15  |
| 6|               1| 2016-02-16  |
| 7|               1| 2016-02-16  |

I try to check last not presented(isPresent = 0) date till today and get 2016-02-14 - so I can count days, it is easy.

But if I mark 2016-02-14 as isPresented = 1(like in table lower) I will get last not presented 2016-02-11 - but it is not correct there are no data for 2016-02-13, so isPresented for this date should be 0 and streak should count from this date

|id| isPresent(Bool)| date(NSDate)|
|==|================|=============|
| 1|               0| 2016-02-11  |
| 2|               1| 2016-02-11  |
| 3|               1| 2016-02-12  |
| 4|               1| 2016-02-14  |
| 5|               1| 2016-02-15  |
| 6|               1| 2016-02-16  |
| 7|               1| 2016-02-16  |

I searched for different algoritm for streaks or sql reuests for missing dates(sql server displaying missing dates) but cant figure out how to use it in core data.

I thinking about another data that will keep streak and updates every time when user opened app, but got same problem what if user didn't open app.

Output: I need to found just counts of days in streak or date when it break, so for

For first table: streak = 2 or breakDate = 2016-02-14 - I try this, but my solution wrong because second table

For second table: streak = 3 or breakDate = 2016-02-13 - can't figure out how to get missing date

Important Update: There will be cloud sync data, so I don't see any solution inside app, really need to find missing date or isPresented = 0 in coredata

p.s. I'm using swift if you can help me via swift it would be great, but I also understand Obj-C. And sorry for my bad English

Answer 1

From your question, I guess you have a item entity with a NSDate object. Here is some code you can use to do it.

let userDefaults = NSUserDefaults.standardUserDefaults()
var moc: NSManagedObjectContext!

var lastStreakEndDate: NSDate!
var streakTotal: Int!



override func viewDidLoad() {
    super.viewDidLoad()


    // checks for object if nil creates one (used for first run)
    if userDefaults.objectForKey("lastStreakEndDate") == nil {
        userDefaults.setObject(NSDate(), forKey: "lastStreakEndDate")
    }

    lastStreakEndDate = userDefaults.objectForKey("lastStreakEndDate") as! NSDate

    streakTotal = calculateStreak(lastStreakEndDate)
}


// fetches dates since last streak
func fetchLatestDates(moc: NSManagedObjectContext, lastDate: NSDate) -> [NSDate] {
    var dates = [NSDate]()

    let fetchRequest = NSFetchRequest(entityName: "YourEntity")
    let datePredicate = NSPredicate(format: "date < %@", lastDate)

    fetchRequest.predicate = datePredicate

    do {
        let result = try moc.executeFetchRequest(fetchRequest)
        let allDates = result as! [NSDate]
        if allDates.count > 0 {
            for date in allDates {
                dates.append(date)
            }
        }
    } catch {
        fatalError()
    }
    return dates
}


// set date time to the end of the day so the user has 24hrs to add to the streak
func changeDateTime(userDate: NSDate) -> NSDate {
    let dateComponents = NSDateComponents()
    let currentCalendar = NSCalendar.currentCalendar()
    let year = Int(currentCalendar.component(NSCalendarUnit.Year, fromDate: userDate))
    let month = Int(currentCalendar.component(NSCalendarUnit.Month, fromDate: userDate))
    let day = Int(currentCalendar.component(NSCalendarUnit.Day, fromDate: userDate))

    dateComponents.year = year
    dateComponents.month = month
    dateComponents.day = day
    dateComponents.hour = 23
    dateComponents.minute = 59
    dateComponents.second = 59

    guard let returnDate = currentCalendar.dateFromComponents(dateComponents) else {
        return userDate
    }
    return returnDate
}


// adds a day to the date
func addDay(today: NSDate) -> NSDate {
    let tomorrow = NSCalendar.currentCalendar().dateByAddingUnit(.Day, value: 1, toDate: today, options: NSCalendarOptions(rawValue: 0))

    return tomorrow!
}

// this method returns the total of the streak and sets the ending date of the last streak
func calculateStreak(lastDate: NSDate) -> Int {
    let dateList = fetchLatestDates(moc, lastDate: lastDate)
    let compareDate = changeDateTime(lastDate)
    var streakDateList = [NSDate]()
    var tomorrow = addDay(compareDate)

    for date in dateList {
        changeDateTime(date)
        if date == tomorrow {
           streakDateList.append(date)
        }
        tomorrow = addDay(tomorrow)
    }

    userDefaults.setObject(streakDateList.last, forKey: "lastStreakEndDate")
    return streakDateList.count
}

I put the call in the viewDidLoad, but you can add it to a button if you like.

Answer 2

ALL SQL Solution

Please note that this assumes that "Today" counts as a day in the streak for the calculation. The SQL returns both the streak in terms of days, and the date just prior to when the streak started.

with max_zero_dt (zero_dt) as
(
  select max(dt)
    from ( 
         select max(isPresent) as isPresent, dt from check_tab group by dt
         union select 0, min(dt) from check_tab
         )
   where isPresent = 0
),
days_to_check (isPresent, dt) as
(
  select isPresent, dt
    from check_tab ct
    join max_zero_dt on ( ct.dt >= zero_dt )
),
missing_days (isPresent, dt) as
(
  select 0, date(dt, '-1 day') from days_to_check
  UNION
  select 0, date('now')
),
all_days_dups (isPresent, dt) as
(
  select isPresent, dt from days_to_check
  union
  select isPresent, dt from missing_days
),
all_days (isPresent, dt) as
(
  select max(isPresent) as isPresent, dt
  from all_days_dups
  group by dt
)
select cast(min(julianday('now') - julianday(dt)) as int) as day_streak
     , max(dt) as dt
  from all_days
 where isPresent = 0

Here is a sqlfiddle for the first scenario: http://sqlfiddle.com/#!7/0f781/2

Here is a sqlfiddle for the second scenario: http://sqlfiddle.com/#!7/155bb/2

NOTE ABOUT THE FIDDLES: They change the dates to be relative to "Today" so that it tests the streak accurately.

Here is how it works:

• SUMMARY: We are going to "fill in" the missing days with zeros, and then do the simple check to see when the max 0 date is. But, we must take into account if there are no rows for today, and if there are no rows with zeros.
• max_zero_dt: contains a single row that contains the latest explicit 0 date, or the earliest date minus 1 day. This is to reduce the number of rows for later queries.
• days_to_check: This is the reduced # of rows to check based on when the latest 0 is.
• missing_days: We need to fill-in the missing days, so we get a list of all the days minus 1 day. We also add today in case there are no rows for it.
• all_days_dups: We simply combine the days_to_check and the missing_days.
• all_days: Here we get the 'max' isPresent for each day, so that now we have the true list of days to search through, knowing that there will not be any gaps with a 0 for isPresent.
• Final Query: This is the simple calculation providing the streak and start date.

ASSUMPTIONS:

• TABLE NAME IS: check_tab
• The Current Date must be in the table with a 1. Otherwise, the streak is 0. The query can be modified if this is not the case.
• If a single day has both a 0 and a 1 for isPresent, the 1 takes precedence, and the streak can continue to prior days.
• That Core Data uses SQLite, and the above SQL which works in SQLite will also work with Core Data's database.