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