CODEWITHSUNDEEP
phpmysqldate
G. Muqtada
G. Muqtada

Reputation: 162

Count days within a month from date range

I have three date ranges in mysql table as follow

I want to count only days that occur in Month of October, for example from first range (2013-09-29 to 2013-10-02) I should get difference of two days (1st and 2nd October) , and it should ignore days from September month, Finally i want to count total days in a given month from above date ranges.

Can it be done from direct mysql query. or any short PHP logic.

here is my table structure id,employee_id,leave_start,leave_to

I am looking for a method some thing like

function countLeaves($employee_id,$month) {

// code

return $numberOfLeaves }

Upvotes: 1

Views: 1423

Answers (3)

Dmitriy Mozgovoy
Dmitriy Mozgovoy

Reputation: 46

$month = strtotime(date('Y-m-01', time()));
$daysCount = (int)(time() - $month) / (24 * 3600);

and if you need to check from any date to any date:

/**
* @var $dateFrom string Date to count from, 
* you can use date('Y-m-01') as start of current month
* @var $dateTo string End of period date, example: '2013-09-05'
**/
function getDaysCount($dateFrom, $dateTo)
{
    return (int)(strtotime($dateTo) - strtotime($dateFrom)) / (24 * 3600);
}

Upvotes: 0

Punithavel
Punithavel

Reputation: 31

function getAllDates($fromDate, $toDate,$rejectmonth) { if(!$fromDate || !$toDate ) {return false;}

    $dateMonthYearArr = array();
    $fromDateTS = strtotime($fromDate);
    $toDateTS = strtotime($toDate);
    for ($currentDateTS = $fromDateTS; $currentDateTS <= $toDateTS; $currentDateTS += (60 * 60 * 24))
    {

        if($rejectmonth == date("m",$currentDateTS))
            continue;
        $currentDateStr = date("Y-m-d",$currentDateTS);
        $dateMonthYearArr[] = $currentDateStr;
    }
    return $dateMonthYearArr;
}

use this function will return the array dates which are in the date ranges.

getAllDates('2013-09-10','2013-19-10',10);

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You need to calculate the first day of the month 2013-10-01 and the last day of the month 2013-10-31 and then you could use a query like this:

SELECT
  DATEDIFF(
    LEAST(d_end, '2013-10-31'),
    GREATEST(d_start, '2013-10-01'))+1 days
FROM
  ranges
WHERE
  d_start<='2013-10-31' AND d_end>='2013-10-01'

Please see fiddle here.

Upvotes: 2

Related Questions