[mythtv-users] TV schedules that are a minute off

[Paul Thompson]

Paul Thompson wrote:
> My setup (Debian Myth packages) has cron running sometime early each 
> morning to run mythfilldatabase. I could see very easily altering that 
> script to run the mysql to round all program start and end settings to 
> five minute intervals. In fact, I think I will probably do that. :)
> 
> If you like, I'll post the code when I am done.

Here is the resulting code.

First, the Debian MythTV packages have a cronfile in /etc/cron.daily 
called mythtv-backend. That file is run once a day, sometime in the 
early morning. That file on my systems, after some modifications, looks 
like this:

     #!/bin/sh

     LOGPATH=/var/log/mythtv/cron
     DATESTAMP=$(date +%Y%m%d)
     su mythtv -c "mythfilldatabase \
        &> $LOGPATH/mythfilldatabase-$DATESTAMP.log"
     su mythtv -c "mysql -vvu mythtv --password=?????? mythconverg \
        < /home/mythtv/fix_bad_times.sql \
        &> $LOGPATH/fix_bad_times-$DATESTAMP.log"

Yes, I actually have my mythtv database password in there. Perhaps not 
so secure, but not an issue behind my firewall. And if someone breaks in 
and breaks my mythtv box - oh well. :)

The other important part is /home/mythtv/fix_bad_times.sql. I really 
should have named that better, and put it in a more intelligent place. 
But I didn't feel like doing any more then a basic hack:

     UPDATE program
        SET starttime = ROUND(ROUND(starttime * 2, -3) / 2)
     WHERE (MINUTE(starttime) % 5) != 0;

     UPDATE programrating
        SET starttime = ROUND(ROUND(starttime * 2, -3) / 2)
     WHERE (MINUTE(starttime) % 5) != 0;

     UPDATE program
        SET starttime = starttime,
            endtime = ROUND(ROUND(endtime * 2, -3) / 2)
     WHERE (MINUTE(endtime) % 5) != 0;

     COMMIT;

This is working great for me. It rounds all start and end times to the 
closest 5 minute interval. If anyone has a better solution, or better 
sql, toss it out here please! :)

-- 
Paul Joseph Thompson
captbunzo at squirrelmail.org
AIM/Yahoo/MSN IM: Captain Bunzo
ICQ Number: 38801719