"Stembridge, Michael" <MStembridge@stripped> wrote on 03/22/2005 10:30:50
> A table exists with id and datetime columns; I need to SELECT records
> the previous business day. I began with this:
> SELECT id FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(datetime) <=1
> But if NOW() is a Monday, it pulls records from Sunday (there are none
> records are inserted M-F only). I thought of using something like
> my WHERE clause:
> "AND DAYOFWEEK(datetime) != 6"
> Though this doesn't seem like an operable solution (If I'm not mistaken
> would return 0 rows if no records were inserted on a Sunday).
> Does MySQL include a specifier for business day?
> Thank you,
Do a little pre-processing to get your "target" date. I am assuming a 5
day workweek (M-F) and no holidays.
SET @TodaysDate = CURDATE();
SET @LastBusDayStart = IF(DAYOFWEEK(@TodaysDate)=1
, @TodaysDate - INTERVAL 2 DAY
, @TodaysDate - INTERVAL 3 DAY
, @TodaysDate - INTERVAL 1 DAY
SET @LastBusDayEnd = @TodaysDate + INTERVAL 1 DAY;
Then your new, very fast (because you can use an index) query looks like:
WHERE datetime_field >= @LastBusDayStart and
datetime_field < @LastBusDayEnd;
The 40 or 50 milliseconds it takes to compute the starting and ending
dates in the SET clauses saves you <insert "huge quantity" noun here> of
time because you move the calculation OUT of your query. The query only
needs to evaluate against constant values and will not need to compute a
date difference for each row as it would have had to do using your
IF you are on a version of MySQL pre-3.23 then these calculations get a
little more hairy but they are still possible.
Unimin Corporation - Spruce Pine