如何在 MySQL 中找到下一個星期六

我有觸發器和一個儲存過程（所以當觸發器執行時 SP 執行）。我需要一個函式來找到下一個星期六放入 SP。

所以可以說，今天是星期三（2015-7-22）。如果我的觸發器今天執行，其中的 SP 必須找到下週六（2015-7-25）。

另外，即使是星期六，但時間早於晚上 9.30，它也必須找到目前日期。晚上 9 點 30 分之後，必須在下週六返回。

我想把我的整個觸發器和 sp 放在這裡，但我不想到這裡擁擠。我只是需要想法，謝謝。

編輯：

感謝 oNaye，我編寫了以下程式碼：

CREATE DEFINER=`root`@`localhost` PROCEDURE `newGuess`(
IN `muserID` INT, 
IN `numm1` INT, 
IN `numm2` INT, 
IN `numm3` INT, 
IN `numm4` INT, 
IN `numm5` INT, 
IN `numm6` INT)
   begin
set @today = (select weekday(curdate())+1); /*monday is the first day in here*/
if @today<6 or @today=7 then /*it is NOT saturday*/
   set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
end if;
if @today = 6 then /* it is saturday */
   set @current_time = (select curtime());
   set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
   if @current_time < CAST('21:30:00' AS time) then
       set @nextSaturday = curdate();
   end if;
   if @current_time >=CAST('21:30:00' AS time) then
       set @nextSaturday = curdate()+INTERVAL 1 WEEK;
   end if;
end if;
insert into guessesTBL (userID,num1,num2,num3,num4,num5,num6,current__datetime,draw_date) values (muserID,numm1,numm2,numm3,numm4,numm5,numm6,NOW(),@nextSaturday);

end

它現在起作用了。解決了。

您可以使用WEEKDAY和DATE_ADD函式來計算下一個工作日的收入。

在這裡你必須做的：

SELECT 
 DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,
                            (6-WEEKDAY(NOW())),
                            (5-WEEKDAY(NOW()))) DAY);

查詢含義：

假設我星期六的工作日是 ，您將在參數和一個條件之間DATE_ADD添加一個間隔，您將必須評估是否大於或等於，您必須用（星期日）休息，如果小於，您必須休息與 的工作日。NOW()5NOW()5****6NOW()5****5NOW()

測試：

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-07-22 07:51:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY);
+------------------------------------------------------------------------------------------+
| DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY) |
+------------------------------------------------------------------------------------------+
| 2015-07-25 07:51:34                                                                      |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

編輯：

如果實際星期六是晚上 9:30，您需要計算下一個星期六：

新語法：

SET @ACTUAL_DATE=NOW();
SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
                                           7,
                                           IF(WEEKDAY(@ACTUAL_DATE)>=5,
                                               (6-WEEKDAY(@ACTUAL_DATE)),
                                               (5-WEEKDAY(@ACTUAL_DATE)))) 
                                       DAY);

今天測試：

mysql> SET @ACTUAL_DATE=NOW();
Query OK, 0 rows affected (0.01 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
   -> 7,
   -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
   -> (6-WEEKDAY(@ACTUAL_DATE)),
   -> (5-WEEKDAY(@ACTUAL_DATE)))) 
   -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-07-25 21:30:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

但如果我們在July 25th at 21:30:01（星期六）：

mysql> SET @ACTUAL_DATE='2015-07-25 21:30:01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
   -> 7,
   -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
   -> (6-WEEKDAY(@ACTUAL_DATE)),
   -> (5-WEEKDAY(@ACTUAL_DATE)))) 
   -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-08-01 21:30:01 |
+---------------------+
1 row in set (0.00 sec)

引用自：https://dba.stackexchange.com/questions/107744