Need to get next day open date and time for library

Question

i need to get the next day time and date for library based on the following table in MySQL without using procedures

CREATE TABLE `library_timing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(10),
  `day` varchar(10) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
   PRIMARY KEY (`id`)
 );

Insert into library_timing values(1,1,"Monday",'09:00:00','18:00:00');
Insert into library_timing values(2,1,"Tuesday",'09:00:00','18:00:00');
Insert into library_timing values(3,1,"Thrusday",'09:00:00','18:00:00');
Insert into library_timing values(4,1,"Friday",'09:00:00','18:00:00');

As u can see I have timings day for library so the output expected is as follows

(Consider Today is MONDAY 25 June 2020)

ID.      Next opening Time
1        26-06-2020 09:00:00

And (Consider if Today is TUESDAY 26 JUNE 2020 and WEDNESDAY IS HOLIDAY so DATE TIME FOR THURSDAY)

ID.      Next opening Time
1        28-06-2020 09:00:00

i have tried this case so far which gives me next days time but not sure how to do if there is gap of more than 1 day

case 
    when
      (curtime() < start_time or curtime() > end_time) or
      (start_time = "00:00:00" and end_time = "00:00:00") or
      (start_time is null and end_time is null)
    then                                                           
      UNIX_TIMESTAMP((
         select TIMESTAMP(curdate()+1,(
           select start_time 
           from library_timing a 
           where a.store_id = s.id and day = DAYNAME(CURDATE() + 1)
         ))
      ))
    else 0 end nextopeningtime

user_3399356 · Answer

I can advice next solution:

add column day_of_week for number day of week for simplify calcualtion:

       CREATE TABLE `library_timing` (
         `id` INT(11) NOT NULL AUTO_INCREMENT,
         `store_id` INT(10),
         `day_of_week` INT(10),
         `day` VARCHAR(10) DEFAULT NULL,
         `start_time` TIME DEFAULT NULL,
         `end_time` TIME DEFAULT NULL,
          PRIMARY KEY (`id`)
        );
       
       INSERT INTO library_timing VALUES(1,1,2,"Monday",'09:00:00','18:00:00');
       INSERT INTO library_timing VALUES(2,1,3,"Tuesday",'09:00:00','18:00:00');
       INSERT INTO library_timing VALUES(3,1,4,"Thrusday",'09:00:00','18:00:00');
       INSERT INTO library_timing VALUES(4,1,5,"Friday",'09:00:00','18:00:00');

next we can use next approach:


SELECT * FROM (
    -- check currently open
    SELECT 'Open' , `lt`.* 
    FROM  `library_timing` `lt`
    WHERE
      `lt`.`day_of_week` = DAYOFWEEK('2020-06-29') AND -- '2020-06-29' will be changed to CURRDATE() 
      CURTIME() BETWEEN `start_time` AND `end_time`
    UNION
    -- check open next day
    SELECT * FROM (
        SELECT 'Next' , `lt`.* 
        FROM  `library_timing` `lt` 
        WHERE `lt`.`day_of_week` > DAYOFWEEK('2020-06-29') -- '2020-06-29' will be changed to CURRDATE()
        LIMIT 1
    ) nextday
    UNION
    -- check first working day
    SELECT 'Next' , `lt`.* 
    FROM  `library_timing` `lt` 
    WHERE `lt`.`day_of_week` = 2
) opentime LIMIT 1;

This approach not ideal and can be improved

Need to get next day open date and time for library

1 Answers