6

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP.

So lets say, it is Wednesday (2015-7-22) today. If my trigger runs today, the SP in it must find the next Saturday (2015-7-25).

Plus, even if it is Saturday, but the time is earlier of 9.30 pm, it must find the current day also. After 9.30 pm, it must return the next Saturday.

I would like to the put my whole trigger and sp on here but I don't want to get here crowded. I just need ideas, thanks.

EDIT:

Thanks to oNaye, I coded that:

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

It worked now. Solved.

5 Answers 5

10

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming.

Here what you have to do:

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

The query meaning:

With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you will have to evaluate if NOW() is greater than 5 or equal, you have to rest it with 6 (Sunday) and if NOW() is less than 5 you have to rest it 5 with the weekday of NOW().

Test:

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> 

EDIT:

If you need to calculate to get the next Saturday if the actual Saturday is at 9:30PM:

New syntax:

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

Today test:

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> 

But if we're on July 25th at 21:30:01 (Saturday):

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)
0
4

I have another variation of the formula guaranteed to work (unless there is a power failure)

SET @now = NOW();
SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY
    + INTERVAL 570 MINUTE
    + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200,
    IF(WEEKDAY(@now)=5,1,0),0) WEEK);

In case you are wondering, 09:30:00 AM is

  • 570 Minutes after midnight
  • 34200 Seconds after midnight

Let's test this formula out

RIGHT NOW

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

mysql> SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY
    ->     + INTERVAL 570 MINUTE
    ->     + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200,
    ->     IF(WEEKDAY(@now)=5,1,0),0) WEEK);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @now,@nextsat;
+---------------------+------------+
| @now                | @nextsat   |
+---------------------+------------+
| 2015-07-22 11:53:33 | 2015-07-25 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql>

SATURDAY 09:29:59 AM

mysql> SET @now = '2015-07-25 09:29:59';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY
    ->     + INTERVAL 570 MINUTE
    ->     + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200,
    ->     IF(WEEKDAY(@now)=5,1,0),0) WEEK);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @now,@nextsat;
+---------------------+------------+
| @now                | @nextsat   |
+---------------------+------------+
| 2015-07-25 09:29:59 | 2015-07-25 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql>

SATURDAY 09:30:00 AM

mysql> SET @now = '2015-07-25 09:30:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY
    ->     + INTERVAL 570 MINUTE
    ->     + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200,
    ->     IF(WEEKDAY(@now)=5,1,0),0) WEEK);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @now,@nextsat;
+---------------------+------------+
| @now                | @nextsat   |
+---------------------+------------+
| 2015-07-25 09:30:00 | 2015-07-25 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql>

SATURDAY 09:30:01 AM

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

mysql> SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY
    ->     + INTERVAL 570 MINUTE
    ->     + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200,
    ->     IF(WEEKDAY(@now)=5,1,0),0) WEEK);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @now,@nextsat;
+---------------------+------------+
| @now                | @nextsat   |
+---------------------+------------+
| 2015-07-25 09:30:01 | 2015-08-01 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

0
1

I posted a similar answer in https://stackoverflow.com/questions/43870329/what-is-the-mysql-alternative-to-oracles-next-day-function/51879395#51879395 which a more general solution to the question being asked here (minus the time portion). The solution below will help you get to any specified next day (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday or Saturday) similar to the NEXT_DAY() function in oracle.

The solution below will return the same date if you are looking for next day that matches today (e.g. if looking for next Saturday and today is Saturday, you'll get today's date returned). Your question here also specifies a special case for after 9:30AM. This is more specific which would require more magic, in which case I'd recommend using Rolando's answer or modifying this solution to account for the time requirement.

Short Version:

Utilizing DAYOFWEEK() you can leverage the number values to create a closed formula that tells you how many days to add. You can then utilize DATE_ADD to help you shift the date. There may be a cleaner way, however you should be able to make a function out of this.

The following is an Excel formula, which you use to verify all 49 use cases: =MOD(MOD(Next-Input, 7)+7,7)

Some examples of how the excel calculations can be translated into SQL:

SELECT (((1-DAYOFWEEK('2018-08-15')) % 7)+7) % 7 AS DaysToAddToGetNextSundayFromWednesday 
FROM dual; -- 4

SELECT DATE_ADD('2018-08-15', INTERVAL (((1-DAYOFWEEK('2018-08-15')) % 7)+7) % 7 DAY) AS NextSundayFromDate 
FROM dual; -- 2018-08-19   

If you plan to use the above often, you'll probably want to make a function or stored procedure.

Long Version:

I've run into this problem multiple times over the years. First time I created a giant case statement for all 49 cases. I found that this made the queries super big and not clean. I wanted a cleaner, simpler solution like a closed in formula. Below are details to to calculations I did to confirm the formula above works. I tested this with MySQL 5.7 if you are using MySQL 8.5 it looks like the function is built in ( Reference: http://www.sqlines.com/mysql/how-to/next_day ).

Note: Excel doesn't return negative results for modulus, where MySQL does. That is why we add 7 and do another modulus.

DAYOFWEEK()         
Sun     1       
Mon     2       
Tues    3       
Wed     4       
Thur    5       
Fri     6       
Sat     7       

Input   Next    Expected Days to Add    Formula Result
1       1       0                       0
1       2       1                       1
1       3       2                       2
1       4       3                       3
1       5       4                       4
1       6       5                       5
1       7       6                       6
2       1       6                       6
2       2       0                       0
2       3       1                       1
2       4       2                       2
2       5       3                       3
2       6       4                       4
2       7       5                       5
3       1       5                       5
3       2       6                       6
3       3       0                       0
3       4       1                       1
3       5       2                       2
3       6       3                       3
3       7       4                       4
4       1       4                       4
4       2       5                       5
4       3       6                       6
4       4       0                       0
4       5       1                       1
4       6       2                       2
4       7       3                       3
5       1       3                       3
5       2       4                       4
5       3       5                       5
5       4       6                       6
5       5       0                       0
5       6       1                       1
5       7       2                       2
6       1       2                       2
6       2       3                       3
6       3       4                       4
6       4       5                       5
6       5       6                       6
6       6       0                       0
6       7       1                       1
7       1       1                       1
7       2       2                       2
7       3       3                       3
7       4       4                       4
7       5       5                       5
7       6       6                       6
7       7       0                       0
1

To find the next date for given week day use this query. set the value of the week day you want in the THEDAY variable.

    SET @THEDAY = 1;
SELECT DATE_ADD(NOW(), INTERVAL if( @THEDAY > WEEKDAY(NOW()) , (@THEDAY - WEEKDAY(NOW())) ,  (7 - WEEKDAY(NOW())) + @THEDAY) DAY)

Note that the weekday index starts from 0 For Monday ...... 6 For Sunday

1

Assumption: Sunday~Saturday is the week structure we are looking to group

To return the closest next Saturday (the week_end):

Oracle: Next_day(now(), 'Sun')-1

Mysql: select DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())=6,6,(5-WEEKDAY(NOW()))) DAY) as week_end

Note: The most voted answer does not output the correct week_end date when the date you throw in is Sunday. When the date is Sunday, it should belong to the week that having the weekend as the next Saturday after the date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.