EPG data end time fix ignores time zones

[m.a.zulliger@…]

XMLTV grabbers such as tv_grab_ch_search can not provide endtime for programs. For programs spanning midnight, the endtime is incorrectly reconstructed: none of these programs ends before 00:00:00 UTC, some later. For the CET time zone with daylight savings time (my time zone), this translates into 2am local time (CET-UTC = +2hrs). It appears that the starttime of the first program starting after 00:00:00 UTC is chosen rather than the first starting after 00:00:00 local time. From the mysql DB:

mysql> SELECT chanid, starttime, endtime, title FROM program WHERE starttime BETWEEN CONVERT_TZ('2013-05-17 22:10:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2013-05-18 23:59:59', 'SYSTEM', 'UTC') AND chanid = '10056' ORDER BY starttime LIMIT 6;

+--------+---------------------+---------------------+------------------------+
| chanid | starttime           | endtime             | title                  |
+--------+---------------------+---------------------+------------------------+
|  10056 | 2013-05-17 20:10:00 | 2013-05-18 00:00:00 | Rambo III              |
|  10056 | 2013-05-17 22:05:00 | 2013-05-17 23:00:00 | The Walking Dead       |
|  10056 | 2013-05-17 23:00:00 | 2013-05-18 00:00:00 | Sofortpartner          |
|  10056 | 2013-05-18 00:00:00 | 2013-05-18 01:00:00 | Sexy Live-Strip Girls  |
|  10056 | 2013-05-18 01:00:00 | 2013-05-18 02:00:00 | Shop24Direct           |
|  10056 | 2013-05-18 02:00:00 | 2013-05-18 03:40:00 | Bauer, ledig, sucht... |
+--------+---------------------+---------------------+------------------------+

(I apologize, I do not do the channel programming.)

I suspect mythtv/libs/libmythtv/programdata.cpp ProgramData::fix_end_times is to blame, starting line 1172:

        querystr = QString("SELECT chanid, starttime, endtime FROM program "
                           "WHERE starttime BETWEEN '%1 00:00:00'"
                           "AND '%2 23:59:59' AND chanid = '%3' "
                           "ORDER BY starttime LIMIT 1;")
                           .arg(endtime.left(10))
                           .arg(endtime.left(10))
                           .arg(chanid);

This seems to be where the following program is queried. Emulating this in mysql for the given case:

mysql> SELECT chanid, starttime, endtime, title FROM program WHERE starttime BETWEEN '2013-05-18 00:00:00' AND '2013-05-18 23:59:59' AND chanid = '10056' ORDER BY starttime LIMIT 1
    -> ;
+--------+---------------------+---------------------+-----------------------+
| chanid | starttime           | endtime             | title                 |
+--------+---------------------+---------------------+-----------------------+
|  10056 | 2013-05-18 00:00:00 | 2013-05-18 01:00:00 | Sexy Live-Strip Girls |
+--------+---------------------+---------------------+-----------------------+

Should the times not be converted to UTC?

        querystr = QString("SELECT chanid, starttime, endtime FROM program "
                           "WHERE starttime BETWEEN CONVERT_TZ('%1 00:00:00', 'SYSTEM', 'UTC')"
                           "AND CONVERT_TZ('%2 23:59:59', 'SYSTEM', 'UTC') AND chanid = '%3' "
                           "ORDER BY starttime LIMIT 1;")
                           .arg(endtime.left(10))
                           .arg(endtime.left(10))
                           .arg(chanid);

Emulating this in mysql for the given case:

mysql> SELECT chanid, starttime, endtime, title FROM program WHERE starttime BETWEEN CONVERT_TZ('2013-05-18 00:00:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2013-05-18 23:59:59', 'SYSTEM', 'UTC') AND chanid = '10056' ORDER BY starttime LIMIT 1;
+--------+---------------------+---------------------+------------------+
| chanid | starttime           | endtime             | title            |
+--------+---------------------+---------------------+------------------+
|  10056 | 2013-05-17 22:05:00 | 2013-05-17 23:00:00 | The Walking Dead |
+--------+---------------------+---------------------+------------------+

[m.a.zulliger@…]

Sorry, first time bug-ticketing. I realize "new Patch - Bug Fix" was wrong. Not to mention the formatting.

[m.a.zulliger@…]

I have implemented the change as described and issue seems to be corrected in my case:

mysql> SELECT chanid, starttime, endtime, title FROM program WHERE starttime BETWEEN CONVERT_TZ('2013-05-17 22:10:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2013-05-18 23:59:59', 'SYSTEM', 'UTC') AND chanid = '10056' ORDER BY starttime LIMIT 6;
+--------+---------------------+---------------------+------------------------+
| chanid | starttime           | endtime             | title                  |
+--------+---------------------+---------------------+------------------------+
|  10056 | 2013-05-17 20:10:00 | 2013-05-17 22:05:00 | Rambo III              |
|  10056 | 2013-05-17 22:05:00 | 2013-05-17 23:00:00 | The Walking Dead       |
|  10056 | 2013-05-17 23:00:00 | 2013-05-17 22:05:00 | Sofortpartner          |
|  10056 | 2013-05-18 00:00:00 | 2013-05-18 01:00:00 | Sexy Live-Strip Girls  |
|  10056 | 2013-05-18 01:00:00 | 2013-05-18 02:00:00 | Shop24Direct           |
|  10056 | 2013-05-18 02:00:00 | 2013-05-18 03:40:00 | Bauer, ledig, sucht... |
+--------+---------------------+---------------------+------------------------+

I do not know which other xmltv grabbers do not provide the endtime (I use tv_grab_ch_search). I do not know if the fix proposed has any negative impacts on grabbers providing endtimes or for time zones running before UTC. Confirmation is needed, that this fix does not break things for others.

I have been struggling to get this into a patch without all the makefile changes... Sorry again.

[m.a.zulliger@…]

Fix missing midnight program endtime compleation

[m.a.zulliger@…]

Upon discovering further issues with the missing midnight program endtime completion:

• Wrong handling of programs correctly in the DB as ending at midnight UTC having their endtime corrupted
• mythfilldatabase for partial refreshes dropping programs starting between midnight UTC and midnight local time for the day immediately after the last day refreshed

I propose to change the time set as endtime to mark a missing endtime be midnight local time (expressed in UTC) rather than midnight UTC. The search window for the following program is then set to look for the first program starting after the program missing the endtime within the first 24hrs.

Please try the patch just posted.

[m.a.zulliger@…]

I am applying this patch to 0.27 successfully. This has been solving my problem for the last 7 months.

[Karl Egly]

untested patch to simplify the endtime fixup instead of keeping it complicated

[m.a.zulliger@…]

I applied the suggested 0003-simplify-handling-of-missing-endtime-at-end-of-file-.patch​ for testing and when running mythfilldatabase --refresh 0-8 get a large number of errors of the sort:

2014-04-06 20:15:14.216333 E  DB Error (program insert):
Query was:
REPLACE INTO program (  chanid,         title,          subtitle,        description,   category,       category_type,    starttime,      endtime,   closecaptioned, stereo,         hdtv,            subtitled,   subtitletypes,  audioprop,      videoprop,   partnumber,     parttotal,   syndicatedepisodenumber,   airdate,        originalairdate,listingsource,   seriesid,       programid,      previouslyshown,   stars,          showtype,       title_pronounce, colorcode ) VALUES( ?,        ?,         ?,       ?,  ?,      ?,        ?,     ?,  ?,            ?,        ?,           ?,  ?,      ?,     ?,  ?,    ?,  ?,  ?,       ?,   ?,  ?,      ?,     ?,  ?,         ?,      ?,      ?)
Bindings were:
:AIRDATE="2014", :AUDIOPROP=0, :CATEGORY="nachrichten", :CATTYPE="", :CC=false,
:CHANID=1013, :COLORCODE="",
:DESCRIPTION="Die Welt im Überblick! Die 'vox nachrichten' beleuchten die wichtigsten Themen des Tages, liefern Hintergründe und ordnen das Geschehen ausführlich ein. Verständlich, informativ und auch unterhaltsam. Dafür können die 'vox nachrichten' auf ein breites Korrespondentennetz im In- und Ausland zurückgreifen. Ob Steuerdebatten, Umweltthemen oder Promi-News - die 'vox nachrichten' zeigen alles, was man wissen muss.",
:ENDTIME=NULL, :HASSUBTITLES=false, :HDTV=false, :LSOURCE=4, :ORIGAIRDATE=NULL,
:PARTNUMBER=0, :PARTTOTAL=0, :PREVSHOWN=false, :PROGRAMID="",
:SERIESID="42851886", :SHOWTYPE="", :STARS="", :STARTTIME=2014-04-07T21:55:00Z,
:STEREO=false, :SUBTITLE="[vox nachrichten]", :SUBTYPES=0, :SYNDICATENO="",
:TITLE="Vox nachrichten", :TITLEPRON="", :VIDEOPROP=2
Driver error was [2/1048]:
QMYSQL3: Unable to execute statement
Database error was:
Column 'endtime' cannot be null

After flushing the mythconverg.program in mysql and re-running mythfilldatabase, the same errors appear.

[m.a.zulliger@…]

0003-simplify-handling-of-missing-endtime-at-end-of-file-.patch leaves a hole in the programming starting after midnight local time (-2 hrs UTC in my case) and skipping one show (this is on a flushed program table):

mysql> SELECT chanid, starttime, endtime, title FROM program WHERE starttime BETWEEN CONVERT_TZ('2014-04-07 22:10:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2014-04-08 23:59:59', 'SYSTEM', 'UTC') AND chanid = '1334' LIMIT 8;
+--------+---------------------+---------------------+---------------------------------------+
| chanid | starttime           | endtime             | title                                 |
+--------+---------------------+---------------------+---------------------------------------+
|   1334 | 2014-04-07 20:55:00 | 2014-04-07 21:40:00 | Intelligence - Ostaggi                |
|   1334 | 2014-04-07 21:40:00 | 2014-04-07 21:55:00 | TG 2                                  |
|   1334 | 2014-04-07 21:55:00 | 2014-04-07 21:59:00 | Oltre la notte                        |
|   1334 | 2014-04-07 23:20:00 | 2014-04-07 23:30:00 | Parlamento Telegiornale               |
|   1334 | 2014-04-07 23:30:00 | 2014-04-08 00:00:00 | Sorgente di vita                      |
|   1334 | 2014-04-08 00:00:00 | 2014-04-08 00:05:00 | Meteo 2                               |
|   1334 | 2014-04-08 00:05:00 | 2014-04-08 01:25:00 | FILM Colour Me Kubrick                |
|   1334 | 2014-04-08 01:25:00 | 2014-04-08 02:10:00 | Videocomic Passerella di comici in tv |
+--------+---------------------+---------------------+---------------------------------------+
8 rows in set (0.00 sec)

[Karl Egly]

tested patch

[Karl Egly]

Thanks for testing. Each SQL error, missing programme, and hole should be the last programme of each grabber run, due to the missing replacement of NULL with a "valid" time stamp. Find attached a fixed patch that avoids inserting NULL values and worked for me when manually grabbing two sets of days with proper fixup at the join point.

[Karl Dietz <dekarl@…>]

In 619b00848a0f272cf4e0529be4fedf48d9c414e9/mythtv:

Error: Processor CommitTicketReference failed

GIT backend not available

[m.a.zulliger@…]

I will test this as soon as I am near my mythtv box again (in about 10 days) and report back. I'll be very busy that following week but I'll do my best to test in depth and report what I tested and in detail any fails.

[m.a.zulliger@…]

Finally got around to testing this, sorry it took so long. Your patch works for me. Thank you. I hope this will make it into the fixes soon.

[Karl Dietz <dekarl@…>]

In 231972ca6da5432d05d513fd2a351aa0202470ee/mythtv:

Error: Processor CommitTicketReference failed

GIT backend not available