Convert MythTV (database, protocol, etc.) to use UTC times

[Nick Morrott <knowledgejunkie (at) gmail (dot) com>]

<rant>First let me add that danielk has advised on IRC that this bug is not likely to be resolved due to a lack of developer time, and the ticket will be closed due to this. However, this is a real issue which could affect any user of MythTV if their country employs daylight savings times during the year. My feeling is that a valid bug should be listed on trac irrespective of whether current capacity dictates that it will not be resolved in a timely fashion. That said:</rant>

MythTV does not currently store programme timings with a UTC offset to indicate the 'absolute' time a programme is broadcast. This requirement is a fundamental requirement during DST changeovers to be able to determine exactly when a programme should be scheduled for recording.

I use the XMLTV tv_grab_uk_rt grabber, which like most XMLTV grabbers, outputs programme data including UTC offsets. Data for tonight's UK DST changeover (2008-10-26 0100 +0000) for The Hallmark Channel includes:

<programme start="20081026000000 +0100" stop="20081026010000 +0100" channel="hallmarkchannelint.com">
  <title>Hustle</title>
  <desc lang="en">Drama serial about a team of con artists. Albert is attacked in Las Vegas so the team plot revenge in the form o
  <credits>
    <actor>Adrian Lester</actor>
    <actor>Marc Warren</actor>
    <actor>Robert Glenister</actor>
    <actor>Jaime Murray</actor>
    <actor>Robert Vaughn</actor>
  </credits>
  <category lang="en">Drama</category>
</programme>
<programme start="20081026010000 +0100" stop="20081026010000 +0000" channel="hallmarkchannelint.com">
  <title>Law &amp; Order: Criminal Intent</title>
  <sub-title>Stress Position</sub-title>
  <desc lang="en">Crime drama series that looks inside the minds of criminals. Goren and Eames investigate when a prison guard is
  <credits>
    <actor>Vincent D'Onofrio</actor>
    <actor>Kathryn Erbe</actor>
    <actor>Courtney B Vance</actor>
    <actor>Jamey Sheridan</actor>
  </credits>
  <category lang="en">Drama</category>
  <subtitles type="teletext" />
</programme>
<programme start="20081026010000 +0000" stop="20081026020000 +0000" channel="hallmarkchannelint.com">
  <title>Law &amp; Order: Special Victims Unit</title>
  <sub-title>Privilege</sub-title>
  <desc lang="en">Drama series about an elite force based in New York which investigates sexually orientated crimes, while dealing
  <credits>
    <actor>Christopher Meloni</actor>
    <actor>Mariska Hargitay</actor>
    <actor>Dann Florek</actor>
    <actor>Richard Belzer</actor>
    <actor>Ice-T</actor>
    <actor>Stephanie March</actor>
  </credits>
  <category lang="en">Drama</category>
</programme>

One will note that the programme "LAO:CI Stress Position" has a start time given in BST, and an end time given in GMT. Importantly, the three programmes follow one after the other, with no timing gaps when UTC offsets are taken into account.

When the XMLTV data is imported into MythTV via mythfilldatabase, these programme entries end up as:

INSERT INTO `program` (`chanid`, `starttime`, `endtime`, `title`, `subtitle`, `description`, `category`, `category_type`, `airdate`, `stars`, `previouslyshown`, `title_pronounce`, `stereo`, `subtitled`, `hdtv`, `closecaptioned`, `partnumber`, `parttotal`, `seriesid`, `originalairdate`, `showtype`, `colorcode`, `syndicatedepisodenumber`, `programid`, `manualid`, `generic`, `listingsource`, `first`, `last`, `audioprop`, `subtitletypes`, `videoprop`) VALUES 
(2190, '2008-10-26 00:00:00', '2008-10-26 01:00:00', 'Hustle', '', 'Drama serial about a team of con artists. Albert is attacked in Las Vegas so the team plot revenge in the form of their most sophisticated con yet. Will they succeed in targeting a $5million fruit machine jackpot without attracting unwanted attention?', 'Drama', '', 2008, 0, 0, '', 0, 0, 0, 0, 0, 0, '83667749', '0000-00-00', '', '', '', '', 0, 0, 0, 1, 1, '', '', ''),
(2190, '2008-10-26 01:00:00', '2008-10-26 02:00:00', 'Law & Order: Criminal Intent : Law & Order: Special Victims Unit', 'Privilege', 'Crime drama series that looks inside the minds of criminals. Goren and Eames investigate when a prison guard is murdered. : Drama series about an elite force based in New York which investigates sexually orientated crimes, while dealing with the effects that their investigations have on their personal lives. A student appears to have committed suicide, but the circumstances seem suspicious.', 'Drama', '', 2008, 0, 0, '', 0, 0, 0, 0, 0, 0, '80615876', '0000-00-00', '', '', '', '', 0, 0, 0, 1, 1, '', '', ''),

One will see that the two Law and Order episodes are concatenated into a single entry in mythconverg.program - causing any regular recording schedules to fail if they are based on these titles.

This issue rears its ugly head for me twice a year (my first mention of this issue on the users list was in 2005 - ​http://www.gossamer-threads.com/lists/mythtv/users/157557).

I updated the tv_grab_uk_rt grabber to output correct programme timings during these changeover periods, but we need to have them handled correctly in MythTV. This will not be a trivial task, but it needs to be done, and should not be swept under the carpet.

[Dibblah]

Isaac has indicated on IRC that he has no problems in-principal with storing times in UTC in the database. However, this change is obviously not trivial. If someone proposes a patch...

[anaerin@…]

In theory, the MySQL part of this is reasonably simple:

UPDATE recordings SET 
   starttime = CONVERT_TZ(starttime,'SYSTEM','UTC'), 
   endtime = CONVERT_TZ(endtime,'SYSTEM','UTC'), 
   progstart = IF(ISNULL(progstart),NULL,CONVERT_TZ(progstart,'SYSTEM','UTC')), 
   progend = IF(ISNULL(progend),NULL,CONVERT_TZ(progend,'SYSTEM','UTC'))

The difficult part will be combing through the code to check each datetime check, to see if it needs to be updated or changed. However, once this is done, it will be a huge boon.

[danielk]

feature request without patch

[beirdo]

I may look into this at some point in the future... Assigned it to myself for easier locating later.

[mythtv@…]

I think the root cause here are bugs that I submitted a patch for in ticket #9676. I think the shows being combined may have been a consequence of MythSecsTo() doing an incorrect calculation.

Based on my experimentation, the QtDateTime class does the right thing regarding daylight savings even using localtime, so long as you're consistent about using local time (and a search through the code didn't turn up any inconsistencies.) Qt consistently bases the offset from UTC on the daylight savings offset of the time in question.

There were some bugs regarding time interval calculation across a DST change, and EITHelper was using an offset from UTC in its calculations based on the time the EITHelper object was constructed, rather than the time value it is operating on. With those fixed, I'm not sure that a big changeover to UTC can be justified.