Missing days in UPnP listing for Recordings/Date

[Valentin.Vidic@…]

After upgrading to 0.28.1 some days in the UPnP listing for Recordings/Date? were missing - sometimes Today or Yesterday was not displayed and possibly other dates too.

For example in the following query no recordings are reported for 2017-10-08 and 2017-10-06 is duplicated:

MariaDB [mythconverg]> SELECT SQL_CALC_FOUND_ROWS r.starttime, COUNT(r.recordedid)
FROM recorded r LEFT JOIN recgroups g ON g.recgroup=r.recgroup 
WHERE g.password='' AND g.recgroup != 'LiveTV' AND g.recgroup != 'Deleted' 
GROUP BY DATE(CONVERT_TZ(r.starttime, 'UTC', 'SYSTEM'))
ORDER BY r.starttime DESC
LIMIT 0,10;
+---------------------+---------------------+
| starttime           | COUNT(r.recordedid) |
+---------------------+---------------------+
| 2017-10-09 12:25:00 |                   1 |
| 2017-10-07 22:01:00 |                   8 |
| 2017-10-06 23:35:00 |                   5 |
| 2017-10-06 21:26:00 |                   1 |
| 2017-10-05 18:49:00 |                   4 |
| 2017-10-04 19:55:00 |                   3 |
| 2017-10-03 18:19:00 |                   6 |
| 2017-10-02 19:30:00 |                   6 |
| 2017-10-01 08:25:00 |                   6 |
| 2017-09-30 08:20:00 |                   3 |
+---------------------+---------------------+
10 rows in set (0.00 sec)

The problem seems to be in the timezone conversion not used consistently in the query. The following updated query gives the correct results:

MariaDB [mythconverg]> SELECT SQL_CALC_FOUND_ROWS
CONVERT_TZ(r.starttime, 'UTC', 'SYSTEM') AS starttime_local, COUNT(r.recordedid)
FROM recorded r LEFT JOIN recgroups g ON g.recgroup=r.recgroup
WHERE g.password='' AND g.recgroup != 'LiveTV' AND g.recgroup != 'Deleted' 
GROUP BY DATE(starttime_local)
ORDER BY starttime_local DESC
LIMIT 0,10;
+---------------------+---------------------+
| starttime_local     | COUNT(r.recordedid) |
+---------------------+---------------------+
| 2017-10-09 14:25:00 |                   1 |
| 2017-10-08 00:01:00 |                   8 |
| 2017-10-07 01:35:00 |                   5 |
| 2017-10-06 23:26:00 |                   1 |
| 2017-10-05 20:49:00 |                   4 |
| 2017-10-04 21:55:00 |                   3 |
| 2017-10-03 20:19:00 |                   6 |
| 2017-10-02 21:30:00 |                   6 |
| 2017-10-01 10:25:00 |                   6 |
| 2017-09-30 10:20:00 |                   3 |
+---------------------+---------------------+
10 rows in set (0.00 sec)

Pull request on github fixes the issue by using the starttime in local timezone for all parts of the query:

​https://github.com/MythTV/mythtv/pull/157

[Stuart Auchterlonie]

Closing any remaining tickets for 0.28, if the issue persists, feel free to reopen and align to v29 or master