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.