CODEWITHSUNDEEP
phpmysqldatetime
Jeremy
Jeremy

Reputation: 1161

Exclude specific holidays from MySQL query

I have this query that I'm using to pull records from my DB:

SELECT dateAdded, claimedDate, TIMESTAMPDIFF(SECOND, dateAdded, claimedDate) AS output FROM leads 
WHERE HOUR(dateAdded) >= '9' 
AND HOUR(dateAdded) < '18' 
AND DAYOFWEEK(dateAdded) != 7 
AND DAYOFWEEK(dateAdded) != 1

As it stands, I'm currently only selecting records with a timestamp that falls between 9:00am - 5:00pm, Monday through Friday. I also need to be able to exclude the following major holidays: New Year's Day, Memorial Day, Independence Day, Labor Day, Thanksgiving, and Christmas.

I need those holidays excluded for all time. For example, the query should exclude all records that occur on the fourth Thursday of Nov (Thanksgiving), regardless of the year.

This doesn't necessarily need a MySQL-only solution. If it's easier to do a hybrid PHP/MySQL solution that would also work great. Thanks for the help.

Upvotes: 0

Views: 2873

Answers (2)

DickoVan
DickoVan

Reputation: 89

You could create a function, is_holiday, and then add to your select statement. As so.

CREATE DEFINER=`root`@`localhost` FUNCTION `is_holiday`(nDate Date) RETURNS tinyint(1)
BEGIN
declare isholiday boolean;
set isholiday = false;

if month(nDate)=1 and day(nDate)=1 then set isholiday = true;
elseif month(nDate)=7 and day(nDate)=4 then set isholiday = true;
elseif month(nDate)=12 and day(nDate)=25 then set isholiday = true;
elseif month(nDate)=9 and day(nDate) between 1 and 7 and weekday(nDate) = 0 then set isholiday = true;
elseif month(nDate)=11 and day(nDate) between 22 and 28 and weekday(nDate) = 3 then set isholiday = true;
end if;

RETURN isholiday;
end

add to your code...

and not is_holiday(dateAdded)

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You need a table of dates that meet your "holiday" criteria, and then you JOIN to that table.

Here's an example

Otherwise, you'll need to simply exclude them...

... WHERE dateAdded NOT IN ('2012-07-04', ...)

Update

This solution uses a JOIN to omit holidays.

Upvotes: 4

Related Questions