从MySQL查询中排除特定假期

Question

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.

Answer 1

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.