I’ve recently installed MythTV on my linux box. (This was a painful experience, but I notice that there’s a Google Summer of Code project for automating a lot of the installation, so things might improve soon.)

Since the TV listings are automatically downloaded into MySQL, I thought I’d write a little script to show what was on ‘now and next’.

The SQL

I’ve saved the following as nowandnext.mysql in my home directory.

-- Now
select 'ON NOW' as '';
select name as Station,
       concat(time_format(starttime, '%h:%i'),'-',time_format(endtime, '%h:%i')) as Time,
       title as Programme
from program, channel
where starttime < NOW() and
      endtime > NOW() and
      program.chanid = channel.chanid;

-- Next
select 'ON NEXT' as '';
select name as Station,
       concat(time_format(starttime, '%h:%i'),'-',time_format(endtime, '%h:%i')) as Time,
       title as Programme
from program, channel
where starttime > NOW() and
      starttime < (NOW() + INTERVAL 1 HOUR) and
      program.chanid = channel.chanid;

Running it

I’ve added the following line to my .bashrc file:

alias nowandnext='mysql -u mythtv --password=addpasswordhere -D mythconverg < ~/nowandnext.mysql'

The result

The result is something like this:

ON NOW
Station Time    Programme
BBC1 East       02:20-03:20     Sign Zone:The Line of Beauty
BBC2 East       03:00-04:00     Adult Learners Week: Books
Channel 4       02:25-04:25     Big Brother Live
Five    12:25-04:05     Major League Baseball Live
ITV1 Anglia     01:25-04:10     ITV Play: Quizmania

ON NEXT
Station Time    Programme
BBC1 East       03:20-03:50     Sign Zone:The Armstrongs
BBC1 East       03:50-04:20     Sign Zone:A Seaside Parish
BBC2 East       04:00-05:00     Adult Learners Week: Diversity
Five    04:05-05:35     UEFA Under-21s Championship

There’s some tidying up to do:

  • it would be nice if the tabs lined up properly
  • the ‘next’ section is a bit of a fudge, ‘cos it’s getting late.