I have a custom recurring reminder (recurring every 2 weeks) named "Recycling is every other Monday". And I wrote a script to catch change events and record the next due date of selected reminders.
(Note, I chose to do this using Apple's sqlite3 database because the size of my DB is so large, it takes upwards of 30 second to run via applescript and takes a fraction of a second using sqlite3).
When I query the database to get all incomplete reminders and all reminders modified since a specific date, I ran into a use case where if I had accidentally "uncompleted" (and recompleted) a very old instance of the recurrence and also accidentally completed and "uncompleted" the current instance, I cannot seem to figure out the logic to figure out the next due date (which is called the "remind me date" in applescript and the reminders app).
Here is my query and the output, looking at all incomplete reminders and any reminder modified in the past 14 days. (Note, if I take out ALL the WHERE clauses, this output comprises the last few rows of all output).
% sqlite3 "file:/Users/robleach/Library/Reminders/Container_v1/Stores/Data-A6A78E21-BA53-4867-B651-08569C902142.sqlite?mode=ro" -cmd ".mode tabs" "SELECT (978307200 + TASK.ZLASTMODIFIEDDATE) as lmdEpochSecs, TASK.ZPRIORITY AS priority, replace(replace(replace(TASK.ZTITLE1, X'0A', '\n'), X'0D', '\r'), X'09', '\t') AS title, LIST.ZNAME1 AS list, replace(replace(replace(TASK.ZNOTES, X'0A', '\n'), X'0D', '\r'), X'09', '\t') AS notes, TASK.ZCOMPLETED as completed, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZCOMPLETIONDATE),'unixepoch') as completionDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZCREATIONDATE),'unixepoch') as creationDate, TASK.ZDISPLAYDATEISALLDAY as isAllday, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZDISPLAYDATEDATE),'unixepoch') as displayDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZLASTMODIFIEDDATE),'unixepoch') as modificationDate, TASK.ZFLAGGED as flagged, TASK.ZCKIDENTIFIER as reminderID, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZACKNOWLEDGEDDATE),'unixepoch') as ackDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZENDDATE),'unixepoch') as endDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZDUEDATE),'unixepoch') as dueDate, strftime('%Y-%m-%dT%H:%M:%S',(978307200 + TASK.ZSTARTDATE),'unixepoch') as startDate FROM ZREMCDOBJECT TASK LEFT JOIN ZREMCDOBJECT LIST on TASK.ZLIST = LIST.Z_PK WHERE LIST.Z_ENT = 21 AND LIST.ZMARKEDFORDELETION = 0 AND TASK.ZMARKEDFORDELETION = 0 AND (TASK.ZCOMPLETED = 0 or modificationDate > '2021-08-03T09:25:08') ORDER BY CASE WHEN TASK.ZDISPLAYDATEDATE IS NULL THEN 1 ELSE 0 END, TASK.ZDISPLAYDATEDATE, TASK.ZPRIORITY;" | grep "Recycling is " | sort -n | perl -e 'while(<>){chomp;@x=split(/\t/);$x[0]=localtime($x[0]);print(join("\t",@x),"\n")}'
lmd priority title list notes completed completionDate creationDate isAllday displayDate modificationDate flagged reminderID acknowledgedDate endDate dueDate startDate
Sun Aug 8 21:57:44 2021 0 Recycling is every other Monday ToDo Home Recurring 1 2021-08-09T01:57:44 2017-09-18T16:59:00 0 2021-08-08T22:00:00 2021-08-09T01:57:44 0 AA6C3A21-45F8-4A21-8A29-E20F00C6EFE3 2021-08-08T22:00:00 2021-08-09T04:00:00
Sun Aug 8 21:58:33 2021 0 Recycling is every other Monday ToDo Home Recurring 1 2021-08-09T01:58:33 2017-09-18T16:59:00 0 2021-08-22T22:00:00 2021-08-09T01:58:33 0 E324BFD0-4EC8-41D3-8EF2-0BEA96EC05F0 2021-08-22T22:00:00 2021-08-23T04:00:00
Sun Aug 8 21:58:33 2021 0 Recycling is every other Monday ToDo Home Recurring 0 2017-09-18T16:59:00 0 2021-09-05T22:00:00 2021-08-09T01:58:33 097F85B66-E979-5226-9E87-794C0901AEB2 2021-09-05T22:00:00 2021-09-06T04:00:00
Sun Aug 15 09:19:03 2021 0 Recycling is every other Monday ToDo Home Recurring 1 2021-08-15T13:19:03 2017-09-18T16:59:00 0 2017-07-17T00:00:00 2021-08-15T13:19:03 0 EAA3CD20-D4A7-59F6-B3BF-DD7A32175810 2017-07-17T00:00:00 2017-07-17T04:00:00
Sun Aug 15 12:53:31 2021 0 Recycling is every other Monday ToDo Home Recurring 1 2021-08-15T16:53:31 2017-09-18T16:59:00 0 2121-07-15T22:00:00 2021-08-15T16:53:31 0 0CBB413B-7BD2-4D4E-80EF-612BF08E2DAD 2021-08-08T22:00:00 2021-08-09T04:00:00
I added headers manually for convenience. The output is sorted by lmd (last modified date), the most recently modified at the bottom.
The actual remind me/due date of the only incomplete instance of the reminder in the reminders app is 2021-08-22. That date does occur among the rows returned (2nd row). But my script is yielding 2121-07-15, which is the due date of the row with the most recent lmd.
My script normally records the due date (or display date - always appear to be the same) of the last modified entry as the next due date, and this is usually correct, but in this instance, it's wrong. And I cannot seem to discern the logic of how Apple determines what the remind me date actually is. The row containing the correct due date has a completion status of "completed", so if I took that row, the completed status would be wrong. The only row with an incomplete status has a due date that is 2 weeks after the correct due date.
I output I want to generate for the reminder would be:
Sun Aug 15 12:53:31 2021 0 Recycling is every other Monday ToDo Home Recurring 0 2017-09-18T16:59:00 0 2021-08-22T22:00:00 2021-08-09T01:58:33 0 E324BFD0-4EC8-41D3-8EF2-0BEA96EC05F0 2021-08-22T22:00:00 2021-08-23T04:00:00
however, the only data I'm really interested in is the completed status (being 0) and the due date (/remind me date) reflecting what the Reminders app shows (2021-08-22).
So how do I compile the correct combined current entry from this output? I don't get it.
UPDATE: I was looking at the output this morning again to try and work out the logic, so I ran the command again and saw a new entry that didn't make sense. The latest (new) version of the reminder had a new due date of 9/5/2021. I checked my phone because I didn't remember completing the latest reminder and the phone still shows the due date as 8/22/2021. That got me thinking, so I checked the reminders app on the computer on which I was running the sqlite3 query and sure enough, the remind me date showed 9/5/2021. It disagreed with my phone on the same iCloud account! So I think that this appears to possibly be an iCloud synch bug. I wish I'd checked it when it said the due date was 7/15, but I bet that's what it said. My guess is that my accidentally changing the completion status of 2 instances of the recurrence lead to reveal a synch bug. So I disabled reminders synch on the Mac mini and reenabled it. Reminders are syncing from scratch. I'll report back once I've confirmed my suspicion. I bet they'll agree once the synch is finished.
Turns out the latest remind-me-date(/due date) does appear to be in the most recently modified reminder entry in the sqlite database.
The problem appears to have been an iCloud Reminders synch issue(/bug).
I had been consulting the remind-me-date in the reminders app on my iPhone, which showed a date of 8/22/2021, but when I opened the Reminders app on the computer on which I was running the sqlite command, the remind-me-date did not agree with my iPhone's copy of the recurring reminder.
Both devices were using the same iCloud account. I resolved the discrepancy by turning off reminders synch and turning it back on. The synch took awhile, but once it was finished, the remind-me-date agreed with the one on my iPhone, and the sqlite command showed the same due date in the most recent version of that reminder.
I think that the reminder modifications that lead to the synch issue were:
...though I'm not certain of the order in which those happened.
I suspect that the iCloud Reminders synch may have incorrectly condensed those multiple edits into a single event, though...
The bad copy of the reminder did seemingly updated on its own sometime this morning (which I know because I have fswatch set up to watch the database file and text me when it detects a change to a reminder I'm tracking for automations), resulting in a second incorrect due date (9/5/2021) that still disagreed with my phone's due date of 8/22/2021.