Computation of days to next free slot is incorrect on index page #173

New Issue

davmo · 2023-10-06T17:37:18Z

davmo commented

2023-10-06 17:37:18 +00:00

Currently (2023-10-06) the next free slot is 3966 on 2023-10-16. That's 10 days in the future.

if you record your show now it could be released in 8 days.

This computation is made by using module Date::Calc on data from a database query. The query is query_next_available_episode in templates/queries-index-{mysql,sqlite}.tpl.html.

This returns the show number (correct) and the date of the show (incorrect). When run today it returns the date for slot 3966 as 2023-10-14, so the delta date calculation returns 8, not 10.

The date/show finding query needs to be rewritten.

Currently (2023-10-06) the next free slot is 3966 on 2023-10-16. That's 10 days in the future. The [index page](https://hackerpublicradio.org/index.html) states: > if you record your show now it could be released in 8 days. This computation is made by using module `Date::Calc` on data from a database query. The query is `query_next_available_episode` in `templates/queries-index-{mysql,sqlite}.tpl.html`. This returns the show number (correct) and the date of the show (incorrect). When run today it returns the date for slot 3966 as 2023-10-14, so the delta date calculation returns 8, not 10. The date/show finding query needs to be rewritten.

davmo commented

2023-10-07 18:50:58 +00:00

The original query was as follows (slightly edited for testing):

WITH next_id AS (
    SELECT id, id + 1 AS id_next, date as last_date
    FROM eps
    WHERE eps.date > date('now')
)
SELECT
    MIN(next_id.id_next) AS next_id,
    strftime('%Y', DATE (MIN (last_date), '+1 Days')) AS last_year,
    strftime('%d', DATE (MIN (last_date), '+1 Days')) AS last_day,
    strftime('%m', DATE (MIN (last_date), '+1 Days')) AS last_month
FROM next_id
LEFT JOIN eps ON next_id.id_next = eps.id
WHERE eps.id IS NULL;

For the current situation on 2023-10-07 it returns:

┌─────────┬───────────┬──────────┬────────────┐
│ next_id │ last_year │ last_day │ last_month │
├─────────┼───────────┼──────────┼────────────┤
│ 3966    │ 2023      │ 14       │ 10         │
└─────────┴───────────┴──────────┴────────────┘

Show 3966 is actually scheduled for 2023-10-16.

The original query was as follows (slightly edited for testing): ``` WITH next_id AS ( SELECT id, id + 1 AS id_next, date as last_date FROM eps WHERE eps.date > date('now') ) SELECT MIN(next_id.id_next) AS next_id, strftime('%Y', DATE (MIN (last_date), '+1 Days')) AS last_year, strftime('%d', DATE (MIN (last_date), '+1 Days')) AS last_day, strftime('%m', DATE (MIN (last_date), '+1 Days')) AS last_month FROM next_id LEFT JOIN eps ON next_id.id_next = eps.id WHERE eps.id IS NULL; ``` For the current situation on 2023-10-07 it returns: ``` ┌─────────┬───────────┬──────────┬────────────┐ │ next_id │ last_year │ last_day │ last_month │ ├─────────┼───────────┼──────────┼────────────┤ │ 3966 │ 2023 │ 14 │ 10 │ └─────────┴───────────┴──────────┴────────────┘ ``` Show 3966 is actually scheduled for 2023-10-16.

davmo commented

2023-10-07 19:03:41 +00:00

The first draft of a replacement query is this:

WITH next_id AS (
    SELECT id, id + 1 AS id_next, date as last_date
    FROM eps
    WHERE eps.date > date('now')
),
free_slot AS (
    SELECT
        last_date AS prev_date,
        CASE
            WHEN strftime('%w',last_date) = '5'
                THEN '+3 days'
            ELSE '+1 days'
        END AS date_offset,
        next_id.id_next AS empty_slot
    FROM next_id
    LEFT JOIN eps ON next_id.id_next = eps.id
    WHERE eps.id IS NULL
    LIMIT 1
)
SELECT
    empty_slot AS next_id,
    strftime('%Y', DATE (prev_date, date_offset)) AS last_year,
    strftime('%d', DATE (prev_date, date_offset)) AS last_day,
    strftime('%m', DATE (prev_date, date_offset)) AS last_month
FROM free_slot;

When run on the database it returns:

┌─────────┬───────────┬──────────┬────────────┐
│ next_id │ last_year │ last_day │ last_month │
├─────────┼───────────┼──────────┼────────────┤
│ 3966    │ 2023      │ 16       │ 10         │
└─────────┴───────────┴──────────┴────────────┘

This is the correct date.

This replacement is somewhat more complex than the original with an extra CTE, but that simplifies the main query and computes the date offset. If the date before the next empty slot is a Friday (day 5 in the range used), the offset is 3 days to cater for the weekend, otherwise it's 1 day.

This hasn't been heavily tested yet, so will not be proposed as a fix until some testing has been done.

The first draft of a replacement query is this: ``` WITH next_id AS ( SELECT id, id + 1 AS id_next, date as last_date FROM eps WHERE eps.date > date('now') ), free_slot AS ( SELECT last_date AS prev_date, CASE WHEN strftime('%w',last_date) = '5' THEN '+3 days' ELSE '+1 days' END AS date_offset, next_id.id_next AS empty_slot FROM next_id LEFT JOIN eps ON next_id.id_next = eps.id WHERE eps.id IS NULL LIMIT 1 ) SELECT empty_slot AS next_id, strftime('%Y', DATE (prev_date, date_offset)) AS last_year, strftime('%d', DATE (prev_date, date_offset)) AS last_day, strftime('%m', DATE (prev_date, date_offset)) AS last_month FROM free_slot; ``` When run on the database it returns: ``` ┌─────────┬───────────┬──────────┬────────────┐ │ next_id │ last_year │ last_day │ last_month │ ├─────────┼───────────┼──────────┼────────────┤ │ 3966 │ 2023 │ 16 │ 10 │ └─────────┴───────────┴──────────┴────────────┘ ``` This is the correct date. This replacement is somewhat more complex than the original with an extra CTE, but that simplifies the main query and computes the date offset. If the date before the next empty slot is a Friday (day 5 in the range used), the offset is 3 days to cater for the weekend, otherwise it's 1 day. This hasn't been heavily tested yet, so will not be proposed as a fix until some testing has been done.

davmo commented

2023-10-10 20:13:15 +00:00

Tested this and the MySQL variant, and the change seems to solve the problem.

The last_year, last_month and last_month fields are used in content-index.tpl.html to compute the number of days until the next empty slot using Date::Calc.

The changes to the query permits it to compute this difference itself, so the computation in the TT² template is not needed.

Released the two files with the database queries as well as a new version of content-index.tpl.html without the computation. The changes were tested on copies of the live database and all worked as desired.

The changes were merged into the repo.

Closing the issue.

Tested this and the MySQL variant, and the change seems to solve the problem. The `last_year`, `last_month` and `last_month` fields are used in `content-index.tpl.html` to compute the number of days until the next empty slot using `Date::Calc`. The changes to the query permits it to compute this difference itself, so the computation in the TT² template is not needed. Released the two files with the database queries as well as a new version of `content-index.tpl.html` without the computation. The changes were tested on copies of the live database and all worked as desired. The changes were merged into the repo. Closing the issue.

davmo closed this issue

2023-10-10 20:13:15 +00:00

Sign in to join this conversation.