需要获得图书馆第二天的开放日期和时间

yeotifhr 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(279)

我需要在mysql中根据下表获得库的第二天时间和日期，而不需要使用过程

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');

正如你所看到的，我有一天的时间库，所以输出预期如下
（今天是2020年6月25日星期一）

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

以及（如果今天是2020年6月26日星期二，星期三是假日，那么请考虑星期四的日期时间）

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

到目前为止，我已经试过这个案子，这给了我下一天的时间，但不知道如何做，如果有超过一天的差距

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

sql mysql

来源：https://stackoverflow.com/questions/62572936/need-to-get-next-day-open-date-and-time-for-library

1条答案

按热度按时间

sxpgvts31#

我可以建议下一个解决方案：
添加列 day_of_week 对于简化计算的星期几：

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');

接下来我们可以使用下一种方法：

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;

这种方法并不理想，可以改进

赞(0）回复(0）举报 2021-07-24

我来回答

需要获得图书馆第二天的开放日期和时间

1条答案

相关问题

热门标签

最新问答