How do I find the next saturday in MySQL
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)
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>