List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 22 2005 4:25pm
Subject:Re: SELECT rows from the previous business day
View as plain text  
"Stembridge, Michael" <MStembridge@stripped> 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


Thread
SELECT rows from the previous business dayMichael Stembridge22 Mar
  • Re: SELECT rows from the previous business dayJigal van Hemert22 Mar
  • Re: SELECT rows from the previous business daySGreen22 Mar
  • Re: SELECT rows from the previous business dayPeter Brawley22 Mar
    • Re: SELECT rows from the previous business daySGreen22 Mar