CODEWITHSUNDEEP
phpmysql
user2429578
user2429578

Reputation: 111

Counting number of working days and weekends using PHP/ MySQL

I am looking to calculate the number of weekdays worked and the number of weekend days worked from data i retrieve out of a MySQL database.

The time from the DB is formatted like this : 2013-07-01 07:00

This is what I have so far:

function isWeekend($date) {
    $check = date("w", strtotime($date));
        if ($check == 6 || $check == 0) {
            return 1;
        }
        else {
            return 0;
        }
}

$query = mysql_query ("SELECT date from jobcards");

while ($row = mysql_fetch_assoc($query)) {
  $date = $row['date'];
  $date_check= isWeekend($date);

  if ($date_check == 1) {
  ++$weekend;
  }
  else {
  ++$workday;
  }

}

I need to find a way to count the days using mysql instead, is there such a way or a more elegant way to improve the PHP code ?

Also if I have multiple records in the database with the same date range but a different time example: 2013-07-01 07:00 and 2013-07-01 07:30 it will be counted as two workdays, how would i prevent that ?

Thanks.

Upvotes: 3

Views: 2467

Answers (4)

Noam Rathaus
Noam Rathaus

Reputation: 5608

Have a look at the WEEKDAY function, and compare it to 6/5 for Sunday/Saturday (respectively)

Your SQL will look something like:

SELECT SUM(IF(WEEKDAY(date) >= 5, 1, 0) AS WeekendCount,
       SUM(IF(WEEKDAY(date) < 5, 1, 0) AS WeekdayCount
FROM jobcards

There is a similar answer here: MySQL Weekday/Weekend count - Part II

Fixed the ) of the IF being placed in the wrong place

Upvotes: 9

Justin Painter
Justin Painter

Reputation: 11

If you want to do this in SQL, you can solve the timestamp problem using the MySQL DATE() function, and the DAYOFWEEK() function to count your weekdays/weekends (Note that the day numbers for Sat/Sun are 7/1 in MySQL and not 6/0 as in PHP). So to count the distinct weekday entries it would look something like:

SELECT COUNT(*) FROM jobcards WHERE DAYOFWEEK(DATE(jobcards.date)) BETWEEN 2 AND 6;

Upvotes: 0

chetan
chetan

Reputation: 2896

SELECT date, 
sum(date) total, 
sum(if(date_format(date,'%w') between 1 and 5),1,0) weekday,
sum(date) - sum(if(date_format(date,'%w') between 1 and 5),1,0) weekend
from jobcards

Upvotes: 0

Jim
Jim

Reputation: 22656

SELECT SUM(IF(DAYOFWEEK(date) BETWEEN 2 AND 6,1,0) AS weekdays,
SUM(IF(DAYOFWEEK(date) NOT BETWEEN 2 AND 6,1,0) AS weekends,
FROM jobcards

Upvotes: 1

Related Questions