Database Administrators Asked by Who Cares on November 14, 2021
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.
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.
Answered by Jingjing Liang on November 14, 2021
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
Answered by yazan on November 14, 2021
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
Answered by VenomFangs on November 14, 2021
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
Let's test this formula out
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>
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>
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>
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>
Answered by RolandoMySQLDBA on November 14, 2021
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>
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)
Answered by oNare on November 14, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP