SQL query issue for a tv show table-CodePudding

I have a table structure:

CREATE TABLE `tv_series_intervals` (
          `id_tv_series` int(11) NOT NULL,   
          `week_day` smallint(1) NOT NULL DEFAULT 1,
          `show_time` TIMESTAMP NOT NULL)

week day in a format 1,2,3,4,5,6,7

show_time it's a converted time to timestamp, for example, 11-45 will convert to 1970-01-01 11:45:00

How can I get next id_tv_series depending on today's datetime?

Does anyone have an idea by what query can I do that?

One example: TV show (with 1 ID) shows on Wednesday at 00:00 and today is Tuesday 23:59, it should return ID 1

CodePudding user response：

can you change structure to

CREATE TABLE tv_series_intervals ( id_tv_series int(11) NOT NULL,
week_day smallint(1) NOT NULL DEFAULT 1, show_time TIME NOT NULL)

or perhaps you can include the function to get time sorted by

select *,from_unixtime(show_time, '%h:%i:%s') time from tv_series_interval order by time

CodePudding user response：

You can use this query to achieve your goal.

Assume today is day 1 and the time is 1970-01-01 12:45:00.

(SELECT * from db WHERE show_time > '1970-01-01 12:45:00' AND week_day = 1
ORDER BY show_time LIMIT 1)

UNION ALL
(SELECT * from db WHERE week_day > 1 AND NOT EXISTS
(SELECT * from db WHERE show_time > '1970-01-01 12:45:00' and week_day = 1 ORDER BY show_time LIMIT 1)
ORDER BY week_day , show_time LIMIT 1)

UNION ALL

(SELECT * from db WHERE week_day < 1 and NOT EXISTS
(SELECT * from db WHERE show_time > '1970-01-01 12:45:00' AND week_day = 1 ORDER BY show_time LIMIT 1) AND NOT EXISTS
(SELECT * from db WHERE week_day > 1)
ORDER BY week_day , show_time LIMIT 1)

We check if we have the next id today if there wasn't any id today we check for the nearest id on other days.

In the third select we check if today is 7 and we have to roll back to 1 and check for the nearest id on day 1.

You can use float type instead of timestamp for column show_time that makes sense as well.

If I was you and could modify the backend. I store the next timestamp totally in the database it could reduce the complexity. (e.g. next tv show will be on 18-02-2022 12:10:00)