Opened 11 years ago

Last modified 10 years ago

#11541 closed Bug Report - General

EPG data end time fix ignores time zones — at Initial Version

Reported by: m.a.zulliger@… Owned by: stuartm
Priority: critical Milestone: 0.27
Component: MythTV - Mythfilldatabase Version: 0.26-fixes
Severity: medium Keywords: Query convert to UTC
Cc: Ticket locked: no

Description

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 | +--------+---------------------+---------------------+------------------+

Change History (0)

Note: See TracTickets for help on using tickets.