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

  2. Wenbert says:

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>