CODEWITHSUNDEEP
mysqlsql
Joel
Joel

Reputation: 59

mysql list data excluding weekends

I have table with id (PK,AI), name and date, when users register I set date = CURDATE().

I have a PHP page where I have to list the names where CURDATE() >= (date + 3 days) excluding weekends (Sat and Sunday) means I list names of users who completed the registration before 3 days but I don't count weekends.

which means if someone register on Monday, he should listed on the page on Thursday and if he registered on Friday, the page list him only on Wednesday (excluded weekends)

Is there any method in MySQL to accomplish this ?

Upvotes: 0

Views: 594

Answers (2)

flaschenpost
flaschenpost

Reputation: 2235

Easy in MySQL.

SELECT CURRENT_DATE + interval (3 + 2*(weekday(CURRENt_DATE) > 1)) day;

Thanks to @JamesBlond for the table, saves me to explain. ;-)

EDIT: I was confused by the (index-killing) way to request data: normally you should try taking the date from the table unmodified and to fiddle with your CURRENT_DATE data.

As long as I understand you right, it should be the following, so you also could simply use a CASE WHEN clasue, that is more readable and easier to adopt.

thedate - interval ( 3 + 2*(weekday(thedate) < 3) + (weekday(thedate) > 4) * (weekday(thedate)-4)) DAY AS 3_days_before

http://www.sqlfiddle.com/#!2/49731/9/0

THEDATE     WEEKDAY(THEDATE)    THEDATE_FORMATTED   3_DAYS_BEFORE   FORMATTED_3_DAYS_BEFORE
April, 27 2014 00:00:00+0000    6   Sunday  April, 22 2014 00:00:00+0000    Tuesday
April, 28 2014 00:00:00+0000    0   Monday  April, 23 2014 00:00:00+0000    Wednesday
April, 29 2014 00:00:00+0000    1   Tuesday     April, 24 2014 00:00:00+0000    Thursday
April, 30 2014 00:00:00+0000    2   Wednesday   April, 25 2014 00:00:00+0000    Friday
May, 01 2014 00:00:00+0000  3   Thursday    April, 28 2014 00:00:00+0000    Monday
May, 02 2014 00:00:00+0000  4   Friday  April, 29 2014 00:00:00+0000    Tuesday
May, 03 2014 00:00:00+0000  5   Saturday    April, 29 2014 00:00:00+0000    Tuesday
May, 04 2014 00:00:00+0000  6   Sunday  April, 29 2014 00:00:00+0000    Tuesday
May, 05 2014 00:00:00+0000  0   Monday  April, 30 2014 00:00:00+0000    Wednesday
May, 06 2014 00:00:00+0000  1   Tuesday     May, 01 2014 00:00:00+0000  Thursday
May, 07 2014 00:00:00+0000  2   Wednesday   May, 02 2014 00:00:00+0000  Friday

Upvotes: 0

Chief Wiggum
Chief Wiggum

Reputation: 2934

Instead of doing a complex mysql query, could you not just pass in a different date to check against, depending on the weekday from your php script?

So, instead of comparing in your sql query your date+3days <= today, pass in a variable for the date offset or just calculate the date in php and pass the date in.

day of the week | offset
1 | 5
2 | 5
3 | 5
4 | 3
5 | 3
6 | 4
7 | 5

Upvotes: 0

Related Questions