A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

# MySQL: Doing calculations on dates excluding weekends

I recently had to calculate the amount of work done by an employee between a specific date range. My problem was that the calculation must exclude the weekends. I asked around (both Google and #mysql in IRC) and found out that MySQL has a DAYOFWEEK() function.

DAYOFWEEK() takes a date and then returns 1 for Sundays and 7 for Saturdays. So for example, if I feed it using the date tomorrow which is 2009-08-14, I get 7.

```mysql> SELECT DAYOFWEEK( '2009-08-15' ); +----------------------------+ | DAYOFWEEK( '2009-08-15' ) | +----------------------------+ | 7 | +----------------------------+ 1 row in set (0.00 sec)```

If I feed it August 30, 2009 — it would return “1″ because that day is a Sunday.

```mysql> SELECT DAYOFWEEK( '2009-08-30' ); +----------------------------+ | DAYOFWEEK( '2009-08-30' ) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec)```

With these examples on mind, we can come up with queries such as these:

```SELECT sum(orders) AS total_orders FROM orders_table WHERE (date_created BETWEEN '2009-08-01' AND '2009-08-15') AND DAYOFWEEK(date_created) NOT IN (1,7);```

The query would fetch the sum of orders from August 1 to August 15 without counting the orders created on the weekends (August 1,2,8,9 and 15 — all of which are weekends).

This entry was posted in General and tagged , , , , . Bookmark the permalink.

### 3 Responses to MySQL: Doing calculations on dates excluding weekends

1. Gerard says:

Nice. Thanks.

2. Lance says:

Shouldn’t that be:
``` SELECT sum(orders) AS total_orders WHERE (date_created BETWEEN '2009-08-01' AND '2009-08-15') AND dayofweek(date_created) NOT IN (1,7); ```

3. Wenbert says:

Woops! Thanks Lance. I have also added “FROM table”