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
I can advice next solution:
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