Extracting Podcast Addict listening history from the SQLite database

Featured image for sharing metadata for article

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.

Written by Jamie Tanna's profile image Jamie Tanna on , and last updated on .

Content for this article is shared under the terms of the Creative Commons Attribution Non Commercial Share Alike 4.0 International, and code is shared under the Apache License 2.0.

#blogumentation #podcast-addict #sqlite.

Also on:

This post was filed under articles.

Interactions with this post

Interactions with this post

Below you can find the interactions that this page has had using WebMention.

Have you written a response to this post? Let me know the URL:

Do you not have a website set up with WebMention capabilities? You can use Comment Parade.