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 .
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:
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
)