Altering Anki's revlog table, or how to recover your streak

anki

Anki users are protective of their streak - the number of consecutive days they’ve done their reviews. Right now, for example, my streak is 621 days. So if you miss a day for whatever reason, not only do you have to deal with double the number of reviews, but you also deal with the emotional toll of having lost your streak.

You can lose your streak for one of several reasons. You could have simply been lazy. You may have forgotten that you didn’t do your Anki. Or travel across timezones put you in a situation where Anki’s clock and your clock differ. Others have described a procedure for resetting the computer’s clock as a way of recovering a lost streak. It apparently works though I haven’t tried it. Instead I’ll focus on a technique that involves working directly with the Anki database.

First, I must warn you: if you don’t know anything about sqlite, SQL or the like don’t attempt this. It’s very easy to do something that will wreck your collection’s database. You’ve been warned.

Locating the database

First of all, make any modifications to the Anki db with care; make backups, etc. Make sure Anki is closed.

On macOS the sqlite database file is at ~/Library/Application Support/Anki2/your_collection_name/collection.anki2. On other platforms, the path is something else, I’m sure the manual says something about this. Or you can just search for collection.anki2 and navigate there. That file is the Anki database.

Moving a review

Let’s say the goal is to move the latest review back to a different day. This query will find the latest review (here I’ve restricted to a particular deck named Словарный запас…)

SELECT * FROM revlog r 
INNER JOIN cards c ON c.id = r.cid 
INNER JOIN decks d ON c.did = d.id
WHERE d.name LIKE '%Словарный запас%'
   AND c.queue = 2
ORDER BY r.id DESC
LIMIT 1

This query returns a single row. In that row, focusing on the id column, that will give us the timestamp in Unix epoch milliseconds. In my case it is 1656057796342. That translates to Fri Jun 24 2022 08:03:16 GMT+0000, which checks out.

Now we are going to need to move that row to an id with a different timestamp. But what should that timestamp be? Well, if we want to move it to yesterday, then we can subtract 86400 seconds (the number of seconds in one day) from the id above. Remember that the id field is in epoch milliseconds so we have to divide by 1000 first → 1656057796. Now subtract 86400 → 1655971396, and multiply by 1000 → 1655971396000 which translates to Thu Jun 23 2022 08:03:16 GMT+0000. Bingo! One day earlier that our existing row.

Now we just need to move the row to our new computed id:

UPDATE revlog 
SET id = 1655971396000
WHERE id = 1656057796342

As long as our new id is unique, this query should succeed.

Again, if you’re not comfortable working with sqlite and SQL queries - this is not a good idea. At the very minimum backup your database in case of error.

References