List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:March 22 2005 4:12pm
Subject:Re: SELECT rows from the previous business day
View as plain text  
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!
>

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