Media History

List All Media Playback Sessions

This query retrieves all records of media playback, including the media URL, start time, and duration.

SELECT 
    playback.url AS 'Media URL',
    datetime(playback.last_play_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS 'Last Play Time',
    playback.watch_time_s AS 'Duration (Seconds)',
    origin.origin AS 'Origin URL'
FROM playback
JOIN origin ON playback.origin_id = origin.id
ORDER BY playback.last_play_time DESC;

Find Media Playback from Specific Origin

If you're interested in media playback from a specific website, this query filters sessions by origin.

SELECT 
    playback.url AS 'Media URL',
    datetime(playback.last_play_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS 'Last Play Time',
    playback.watch_time_s AS 'Duration (Seconds)'
FROM playback
JOIN origin ON playback.origin_id = origin.id
WHERE origin.origin LIKE '%example.com%'
ORDER BY playback.last_play_time DESC;

Replace %example.com% with the desired website origin.

Aggregate Watch Time by Origin

To understand which websites users spend the most time watching media on, use this query to sum watch times grouped by origin.

SELECT 
    origin.origin AS 'Origin URL',
    SUM(playback.watch_time_s) / 60 AS 'Total Watch Time (Minutes)'
FROM playback
JOIN origin ON playback.origin_id = origin.id
GROUP BY origin.origin
ORDER BY SUM(playback.watch_time_s) DESC;

Identify Most Frequently Played Media

This query helps identify the most frequently accessed media files, which can be indicative of user interest or important content.

SELECT 
    playback.url AS 'Media URL',
    COUNT(playback.url) AS 'Play Count',
    SUM(playback.watch_time_s) / 60 AS 'Total Watch Time (Minutes)'
FROM playback
GROUP BY playback.url
ORDER BY COUNT(playback.url) DESC, SUM(playback.watch_time_s) DESC;

Media Playback Within a Specific Timeframe

To filter media playback sessions within a specific timeframe, modify this query with your desired dates.

SELECT 
    playback.url AS 'Media URL',
    datetime(playback.last_play_time / 1000000 - 11644473600, 'unixepoch', 'localtime') AS 'Last Play Time',
    playback.watch_time_s AS 'Duration (Seconds)'
FROM playback
WHERE playback.last_play_time BETWEEN strftime('%s','2024-01-01')*1000000+11644473600000000
                                AND strftime('%s','2024-01-31')*1000000+11644473600000000
ORDER BY playback.last_play_time DESC;

Adjust the dates in the BETWEEN clause ('2024-01-01' and '2024-01-31') to the desired timeframe.