A quick try:
In most cases you need results from yesterday, but for Sunday and Monday we
must correct extra for the weekend.
DAYOFWEEK() returns 1 for Sunday, 2 for Monday, etc., so we can use this to
make a correction value:
SET @COR = (DAYOFWEEK(NOW()) < 3) * DAYOFWEEK(NOW());
SELECT * FROM table WHERE `datetime` > CURDATE() - INTERVAL (1 + @COR) DAY
AND `datetime` < CURDATE() - INTERVAL @COR DAY;
This construction is index-friendly, because MySQL will reduce the
calculation to a constant before running the query. The result is a simple
col > constant expression which can make use of indexes if they are present.
Expressions such as TO_DAYS(`datetime`) (as suggested by you) will result in
a full table scan and slow down the query significantly.
If you consider Saturday as a business day you only need to make a
correction for Monday:
SET @COR = (DAYOFWEEK(NOW()) = 2);
Regards, Jigal.
----- Original Message -----
From: "Stembridge, Michael" <MStembridge@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 22, 2005 4:30 PM
Subject: SELECT rows from the previous business day
> 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
>
>
>
> !DSPAM:42403a4317126025714369!
>
