[mythtv-users] 15 Years of MythTV Goodness

[John Marshall]

On 11/22/2021 1:44 PM, Dan Wilga wrote:
> I think you do need MythWeb. It took me a while to find them, as it was. 
> For anyone else who can't figure it out, go to the URI mythweb/stats.

Well, I didn't install MythWeb but I did find the code.  These queries 
should replicate the stats in case anyone else without MythWeb is 
curious.  I did add the future=0 because I was getting future dates.

TitleCount: 1678
ShowCount: 22850
First Recording:  2007-11-05
Latest:  2021-11-22
Running: 14y 21d 17h 0m
Recording: 2y 156d 20h 41m
%: 17.282

-----------
SELECT COUNT(*) as TitleCount FROM (SELECT DISTINCT title FROM 
oldrecorded where future=0) x;

SELECT COUNT(title) as ShowCount FROM oldrecorded where future=0;

SELECT Min(starttime) as FirstRecording, MAX(starttime) as 
LatestRecording FROM oldrecorded where future=0;

SELECT (RunningMinutes DIV 525600) as Years, ((RunningMinutes MOD 
525600) DIV 1440) as Days, ((RunningMinutes MOD 1440) DIV 60) as Hours, 
(RunningMinutes MOD 60) AS RunningMinutes FROM (SELECT 
timestampdiff(minute, FirstRecording, LatestRecording) as RunningMinutes 
FROM (SELECT Min(starttime) as FirstRecording, MAX(starttime) as 
LatestRecording FROM oldrecorded where future=0) x) minutes;

SELECT (TotalMinutes DIV 525600) as Years, ((TotalMinutes MOD 525600) 
DIV 1440) as Days, ((TotalMinutes MOD 1440) DIV 60) as Hours, 
(TotalMinutes MOD 60) AS Minutes FROM (SELECT SUM(timestampdiff(minute, 
starttime, endtime)) as TotalMinutes FROM oldrecorded where future=0 AND 
endtime > starttime) minutes;

SELECT title, COUNT(programid) AS recorded, MAX(starttime) AS 
last_recorded FROM oldrecorded where future=0 GROUP BY title ORDER BY 
recorded DESC, last_recorded, title LIMIT 20;

SELECT COUNT(oldrecorded.chanid) as recorded, channel.callsign, 
channel.name, channel.channum, MAX(oldrecorded.starttime) AS 
last_recorded FROM oldrecorded LEFT JOIN channel ON channel.chanid = 
oldrecorded.chanid WHERE future=0 AND channel.channum IS NOT NULL GROUP 
BY channel.callsign, channel.name, channel.channum ORDER BY recorded 
DESC, last_recorded, name LIMIT 20;

WITH
   running AS (SELECT timestampdiff(minute, FirstRecording, 
LatestRecording) as RunningMinutes FROM (SELECT Min(starttime) as 
FirstRecording, MAX(starttime) as LatestRecording FROM oldrecorded where 
future=0) x),
   recording AS (SELECT SUM(timestampdiff(minute, starttime, endtime)) 
as TotalMinutes FROM oldrecorded where future=0 AND endtime > starttime)
SELECT CONCAT(recording.TotalMinutes / running.RunningMinutes * 100.0, 
'%') as PercentRecording
FROM recording, running;