What is the best way to determine if a timestamp falls within seasonal business hours?

Question

I want a SQL query to check if a given timestamp falls within the open hours of a business. These open hours change depending on the time of year (seasonal hours) and the business is closed for certain holidays. I have a list of these dates, although they are subject to change.

What I want is a good structure for storing this information in the database and a function that looks like this:

CREATE OR REPLACE FUNCTION is_business_open(event TIMESTAMP) RETURNS BOOLEAN AS $_$
...
$_$

I'll be writing this in plpgsql on PostgreSQL 8.2.x, if that makes a difference.

Answer 1

I'd suggest putting the data (date, open time, close time) into a table and getting your query to select from there.

Answer 2

I would suggest date ranges you are open, along with times.

date_start, date_end, time_open, time_close

A query might look like so:

select time_open, time_close from hours_table where date_start < @DateInput and @DateInput < date_end

You so only put your open times. Anything else is "closed"