Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#11541 closed Bug Report - General (fixed)

EPG data end time fix ignores time zones

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 (last modified by Raymond Wagner)

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

Attachments (3)

programdata_midnight_diff.cpp (1.8 KB) - added by m.a.zulliger@… 6 years ago.
Fix missing midnight program endtime compleation
0003-simplify-handling-of-missing-endtime-at-end-of-file-.patch (3.6 KB) - added by Karl Egly 5 years ago.
untested patch to simplify the endtime fixup instead of keeping it complicated
0001-simplify-handling-of-missing-endtime-at-end-of-file-.patch (4.4 KB) - added by Karl Egly 5 years ago.
tested patch

Download all attachments as: .zip

Change History (16)

comment:1 Changed 6 years ago by m.a.zulliger@…

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

comment:2 Changed 6 years ago by Raymond Wagner

Description: modified (diff)
Type: Patch - Bug FixBug Report - General

comment:3 Changed 6 years ago by 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.

Changed 6 years ago by m.a.zulliger@…

Fix missing midnight program endtime compleation

comment:4 Changed 6 years ago by 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.

comment:5 Changed 6 years ago by stuartm

Milestone: unknown0.27
Priority: minorcritical

comment:6 Changed 6 years ago by m.a.zulliger@…

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

Changed 5 years ago by Karl Egly

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

comment:7 Changed 5 years ago by 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.

comment:8 Changed 5 years ago by 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)

Changed 5 years ago by Karl Egly

tested patch

comment:9 Changed 5 years ago by 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.

comment:10 Changed 5 years ago by Karl Dietz <dekarl@…>

Resolution: fixed
Status: newclosed

In 619b00848a0f272cf4e0529be4fedf48d9c414e9/mythtv:

simplify handling of missing endtime at end of file in mythfilldatabase

Instead of making up endtime of "next midnight" and "next 06:00 am" we
just let the database fill in its default of 0000-00-00 00:00 and replace
it later with whatever the next starttime is on that channel.

This has seen very light testing, so I'm commiting it to master only. If
I get positive reports from users I'm happing to merge it into fixes.

Fixes #11541

comment:11 Changed 5 years ago by 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.

comment:12 Changed 5 years ago by 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.

comment:13 Changed 5 years ago by Karl Dietz <dekarl@…>

In 231972ca6da5432d05d513fd2a351aa0202470ee/mythtv:

simplify handling of missing endtime at end of file in mythfilldatabase

Instead of making up endtime of "next midnight" and "next 06:00 am" we
just let the database fill in its default of 0000-00-00 00:00 and replace
it later with whatever the next starttime is on that channel.

This has seen very light testing, so I'm commiting it to master only. If
I get positive reports from users I'm happing to merge it into fixes.

Fixes #11541

Reported as working on fixes/0.27 in https://code.mythtv.org/trac/ticket/11541#comment:12
(cherry picked from commit 619b00848a0f272cf4e0529be4fedf48d9c414e9)

Note: See TracTickets for help on using tickets.