Throwback in dates without Weekends

问题:

I often use this query1 to go back 6 days on a variable date:

query1 = SELECT
    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
    FROM `HELPER_SEQ`
    AS S WHERE S.`I` <= 6;

With the help of this auxiliary table with a sequence inserted there.

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES (1),(2),(3),(4),(5),(6);

Question:

How can I avoid output weekends days using query1? In a such way that the output go back in 6 useful days, in this example starting on '2018-11-21' the output would be ->

| 2018-11-21 | 
| 2018-11-20 |
| 2018-11-19 | 
| 2018-11-16 | 
| 2018-11-15 | 
| 2018-11-14 |

回答1:

We can utilize DayName() function to get the name of the weekday corresponding to a Date. We will utilize this function's result to restrict weekends by NOT IN ('Saturday', 'Sunday').

Also, we will need to increase the number generator range upto 10. Because there is a possibility that we can come across 2 weekends (total 4 days) on either side of 5 weekdays.

So, we need 2 (first pair of weekend days) + 5 (weekdays) + 2 (second pair of weekend days) + 1 (6th weekday) = 10 dates to consider. An example of this edge case would be when an input date is Sunday.

We will need to use LIMIT 6 to restrict the result upto 6 days only, in the non-edge cases.

Schema (MySQL v5.7)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Query

SELECT
    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
FROM `HELPER_SEQ` AS S 
WHERE S.`I` <= 10 
  AND DAYNAME(DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')
ORDER BY VAR DESC
LIMIT 6;

Result

| VAR        |
| ---------- |
| 2018-11-21 |
| 2018-11-20 |
| 2018-11-19 |
| 2018-11-16 |
| 2018-11-15 |
| 2018-11-14 |

View on DB Fiddle

---

Edge Case Demo - Input date: 25 Nov 2018 (Sunday)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);
INSERT INTO `HELPER_SEQ` (`I`) VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Query #2

SELECT
    DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')
    AS VAR
FROM `HELPER_SEQ` AS S 
WHERE S.`I` <= 10 
  AND DAYNAME(DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')
ORDER BY VAR DESC
LIMIT 6;

Result

| VAR        |
| ---------- |
| 2018-11-23 |
| 2018-11-22 |
| 2018-11-21 |
| 2018-11-20 |
| 2018-11-19 |
| 2018-11-16 |

View on DB Fiddle