Last n weekdays in sql where clausel

Question

We are using MySQL as our database to store messages with timestamps. Is it possible to create a query that returns messages of the last n weekdays?

I.e. if n is 4 and today is Tuesday, I want messages from this weeks Monday, last weeks Friday, last weeks Thursday and last weeks Wednesday .

score 2 · Accepted Answer · answered Jul 27 '12 at 17:37

If you want to do this directly with mysql it would be a little complicated. As Vatev recommended you should calculate date star date in advance, but if you really want to do this, you'll probably need following functions:

ADD_DATE, with INTERVAL -N WEEKS
FLOOR, in C int/int would do just fine
MOD, a % b :)
WEEKDAY

First of all you need should count how many weeks you should go back, that's easy... For you one week = 5 days, that means

weeks = FLOOR(days / 5)

We've taken care of weeks, so we'll now have to work with the rest:

rest = days MOD 5

Now we have two cases, weekend has occurred or no, for the case that there wasn't weekend days are good. We have to add 2 days to skip it. The weekend occurred if (WEEKDAY(now) - rest) < 0

rest = IF( (WEEKDAY(now) - rest) < 0, rest + 2, rest)

And now we can build it to one par (let's assume you have {days} and {rest} pre-calculated):

WHERE date >= ADD_DATE(
    ADD_DATE (
        {now},
        INTERVAL -IF( (WEEKDAY({now}) - {rest}) < 0, {rest} + 2, {rest}) DAYS, 
    ),
    INTERVAL -FLOOR({days} / 5) WEEKS
)

thank you for the detailed explanation. As Vatev pointed out, I think I will calculate the starting day in my application to put some stress from the db. — Thomas, Jul 27 '12 at 18:10
Stress on the DB from this operation will be negligible, but there will probably be a lot less stress for you if you do it in a more sane language :P — Vatev, Jul 27 '12 at 18:20
@Thomas I wrote it so I'll be reusable for any other language, especially PHP accepts (in [`strtotime`](http://php.net/manual/en/function.strtotime.php) ) intervals to and the logic is the same. You can even do `timestamp() - weeks*7days*24hour*3600seconds` to save some little resources. — Vyktor, Jul 27 '12 at 18:26

score 1 · Answer 2 · answered Jul 27 '12 at 17:21

The best i can come up with is calculating the start date ({start_date} in the query) in the language of your choice and then running something like this:

SELECT some_things
FROM your_table
WHERE 
    WEEKDAY(time_column) < 5
    AND time_column >= {start_date}
ORDER BY time_column DESC

You can also make a stored function to calculate 'the date x week days ago' and use it for {start_date}.

score 0 · Answer 3 · answered Jul 27 '12 at 17:19

0

Have you tried something like this?

SELECT columns 
FROM table
WHERE datediff(column_with_timestamp,NOW()) > n

Where N is as you defined above, the number of days you're looking for.

answered Jul 27 '12 at 17:19

Valdogg21

1,151
4
14
24

Week days, not including weekends. – Vyktor Jul 27 '12 at 17:20

score 0 · Answer 4 · answered Jul 27 '12 at 17:25

0

COL >= date_sub( NOW(), interval 1 week) and
weekday( COL ) < 5

date_sub is to seek rows created last week
weekday is to exclude sunday or saturday

answered Jul 27 '12 at 17:25

ajreal

46,720
11
89
119

Last n weekdays in sql where clausel

4 Answers4