id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc	mlocked
11541	EPG data end time fix ignores time zones	m.a.zulliger@…	stuartm	"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 |
+--------+---------------------+---------------------+------------------+
"	Patch - Bug Fix	new	minor	unknown	MythTV - Mythfilldatabase	0.26-fixes	medium		Query convert to UTC		0