Search code examples
mysqlsql-timestamp

5 Business Days of Previous Week in Mysql

With this query: 

date(timestamp_column) >= SUBDATE(curdate(), WEEKDAY(curdate())) -
interval 1 week AND date(timestamp_column) < SUBDATE(curdate(), WEEKDAY(curdate()))

I get the 7 days from the previous week. The one that I am looking is 5 business days of previous week in Mysql (Mo-fr). I would be more than greatful if anyone can help. Thanks

Edit thanks : 

date(timestamp_column) > DATE_FORMAT(DATE_SUB(DATE_SUB(current_date, INTERVAL DAYOFWEEK(CURRENT_DATE)+5 day), INTERVAL 1 day), '%Y%m%d') 

and

date(timestamp_column) < DATE_FORMAT(DATE_SUB(current_date, INTERVAL DAYOFWEEK(CURRENT_DATE)-1 day), '%Y%m%d')does the trick, if anyone wants the code.
Solution

  • Adding AND DAYOFWEEK(timestamp_column) BETWEEN 2 AND 6 should help.

    DAYOFWEEK(date)

    Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

    source: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofweek