Extracting Podcast Addict listening history from the SQLite database

I listen to a lot of podcasts, especially recently where I'm finding it easier than listening to text-to-speech of my to-read list of articles, and I use the excellent Podcast Addict.

As an avid believer of the IndieWeb philosophy of "own your data", I've been tracking more of my listening history on my site, which has been manually added when I finish a podcast.

However, I wanted to get my historical data so I could backfill it, especially as I still had backups from previous phones' listening history.

I wasn't aware that Podcast Addict had this functionality until I'd seen this IndieWeb chat message. This got me looking, and I found that when enabling the history view, we could see the date a podcast was played.

I wondered if, because the date itself was shown, it's likely that the full datetimestamp is in there, too, and it turns out there is, but you need to dig around in the SQLite database.

To get this, you need to perform a backup, and inside that backup there will be a podcastAddict.db SQLite database.

As of the app v2022.2.5, we can run the following query:

select guid,url,name,playbackDate from episodes WHERE playbackDate > 0;

This fetches key metadata for each podcast that has been listened to before, which for instance looks like the following:

https://martymcgui.re/2018/10/03/141113/||This Week in the IndieWeb Audio Edition • September 22nd - 28th, 2018|1552589356698

Notice that the final field is the playbackDate which is a Unix epoch in milliseconds, so we can convert it to a human date by using date, once we've divided by 1000.

I've found it a little frustrating to see just how many podcasts don't have predictable, or even stable, permalinks for the url field, and understandably their guid isn't necessarily a URL.