I have needed to extract information from a MySQL database on a regular basis and present the information in the form of ‘business hours'. One of the biggest issues I have had is that whilst the business days and hours are static and can be filtered (see my article: MySQL limiting selection to business hours) The public holidays themselves change and as such I can't simply remove a specific day.

In order to get around this, the easiest way I found was to create another table in MySQL to store the public holidays. In my schema, my table contained holidayID, HolidayName, HolidayState and HolidayDate. This allowed me to specify which state the holiday applied to (although you could easily add more information).

With this information in MySQL, I could then run a SELECT query to get my data, and add a WHERE clause as per the below which specifies only show information where the date is not in the entire list of Holidays.

If I wanted to narrow it down I could change the Select Date(HolidayDate) from Holidays WHERE HolidayState = “VIC”, an example of my more broad code is presented below;


FROM raw_data
Date(entry_date) NOT IN (Select Date(HolidayDate) from Holidays)

Prev MySQL limiting selection to business hours
Next MySQL: Changing Date Format

Comments are closed.