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).
Nice. Thanks.
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);
Woops! Thanks Lance. I have also added “FROM table”