Print Y-m-d list of business dates between two dates from MySQL using PHP

Question

I have this MySQL table:

desc studentabsence;
+---------------------------+-------------+
| Field                     | Type        |
+---------------------------+-------------+
| student_id                | INT(11)     |
| student_absence_startdate | date        |
| student_absence_enddate   | date        |
+---------------------------+-------------+

Let's say that we have

student_absence_startdate = 2012-08-01
student_absence_enddate = 2012-08-08

Using PHP, I would like to echo all business days between that range (Mon-Fri).

From the above range I would like to print:

2012-08-01
2012-08-02
2012-08-03
2012-08-06
2012-08-07
2012-08-08

How and where should I start to achieve this?

`student_id | date` <- that does not look right (but might be unrelated to your problem) — hakre, Aug 10 '12 at 14:45
@hakra oops, simple mistake by me when copying the code to the question. I will edit this. — David, Aug 10 '12 at 14:46
convert both to timestamps (strtotime) and loop from start to end by increments of 24 * 3600, then use date('N', $loop_variable) to get the day of week and decide to print it or not. — yent, Aug 10 '12 at 14:47
Is that what you are looking for? [How to find day of week in php](http://stackoverflow.com/questions/712761/how-to-find-day-of-week-in-php) — hakre, Aug 10 '12 at 14:48

score 3 · Accepted Answer · answered Aug 10 '12 at 14:50

3

// Date strings from DB
$startDate = '2012-08-01';
$endDate = '2012-08-08';

// Convert to UNIX timestamps
$currentTime = strtotime($startDate);
$endTime = strtotime($endDate);

// Loop until we reach the last day
$result = array();
while ($currentTime <= $endTime) {
  if (date('N', $currentTime) < 6) {
    $result[] = date('Y-m-d', $currentTime);
  }
  $currentTime = strtotime('+1 day', $currentTime);
}

// Show the result
// You could loop the array to pretty-print it, or do it within the above loop
print_r($result);

See it working

answered Aug 10 '12 at 14:50

DaveRandom

87,921
11
154
174

@David Arguably Mathieu Imberts solution is better because `DateTime` is now considered the "right" way to do things. Mechanically they do basically the same thing though. – DaveRandom Aug 10 '12 at 15:01
[Relative formats support "weekdays"](http://stackoverflow.com/questions/6202576/get-all-work-days-in-a-week-for-a-given-date/6202709#6202709), so no need for `"N" < 6` – Gordon Oct 31 '12 at 09:12

Tchoupi · Answer 2 · 2012-08-10T14:55:24.047

2

This will print the range of dates:

$startDate = '2012-08-01';
$endDate = '2012-08-08';

$date = new DateTime($startDate);
while ($date->format('Y-m-d') != $endDate) {

    if ($date->format('N') > 5) {
        $date->modify('+1 day');
        continue;
    }

    echo $date->format('Y-m-d') . PHP_EOL;
    $date->modify('+1 day');
}
echo $endDate;

edited Aug 10 '12 at 14:55

answered Aug 10 '12 at 14:48

Tchoupi

14,560
5
37
71

@DaveRandom Everyday is a business day for me :D – Tchoupi Aug 10 '12 at 14:56
Thank's alot for taking your time to help! – David Aug 10 '12 at 15:01

Print Y-m-d list of business dates between two dates from MySQL using PHP

2 Answers2

Linked

Related