"Stembridge, Michael" <[EMAIL PROTECTED]> wrote on 03/22/2005 10:30:50
AM:
> A table exists with id and datetime columns; I need to SELECT records
from
> 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
since
> records are inserted M-F only). I thought of using something like
this in
> my WHERE clause:
>
>
>
> "AND DAYOFWEEK(datetime) != 6"
>
>
>
> Though this doesn't seem like an operable solution (If I'm not mistaken
this
> would return 0 rows if no records were inserted on a Sunday).
>
>
>
> Does MySQL include a specifier for business day?
>
>
>
> Thank you,
>
> Michael
>
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
, IF(DAYOFWEEK(@TodaysDate)=2
, @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:
SELECT id
FROM table
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
original query.
IF you are on a version of MySQL pre-3.23 then these calculations get a
little more hairy but they are still possible.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
