Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>All you have to do is get the total time elapsed, then substract the non-working hours.</p> <p>You can use dateTime and <a href="http://us2.php.net/datePeriod" rel="nofollow">datePeriod</a> php objects for that (requires php 5.3)</p> <p>Here a small script to do what you want (but you will have probably to adapt for your needs)</p> <pre><code>&lt;?php ini_set('display_errors', 'on'); define('DAY_WORK', 32400); // 9 * 60 * 60 define('HOUR_START_DAY', '08:00:00'); define('HOUR_END_DAY', '17:00:00'); // get begin and end dates of the full period $date_begin = '2013-11-29 15:00:00'; $date_end = '2013-12-03 15:00:00'; // keep the initial dates for later use $d1 = new DateTime($date_begin); $d2 = new DateTime($date_end); // and get the datePeriod from the 1st to the last day $period_start = new DateTime($d1-&gt;format('Y-m-d 00:00:00')); $period_end = new DateTime($d2-&gt;format('Y-m-d 23:59:59')); $interval = new DateInterval('P1D'); //$interval = new DateInterval('weekdays'); // 1 day interval to get all days between the period $period = new DatePeriod($period_start, $interval, $period_end); $worked_time = 0; $nb = 0; // for every worked day, add the hours you want foreach($period as $date){ $week_day = $date-&gt;format('w'); // 0 (for Sunday) through 6 (for Saturday) if (!in_array($week_day,array(0, 6))) { // if this is the first day or the last dy, you have to count only the worked hours if ($date-&gt;format('Y-m-d') == $d1-&gt;format('Y-m-d')) { $end_of_day_format = $date-&gt;format('Y-m-d '.HOUR_END_DAY); $d1_format = $d1-&gt;format('Y-m-d H:i:s'); $end_of_day = new DateTime($end_of_day_format); $diff = $end_of_day-&gt;diff($d1)-&gt;format("%H:%I:%S"); $diff = split(':', $diff); $diff = $diff[0]*3600 + $diff[1]*60 + $diff[0]; $worked_time += $diff; } else if ($date-&gt;format('Y-m-d') == $d2-&gt;format('Y-m-d')) { $start_of_day = new DateTime($date-&gt;format('Y-m-d '.HOUR_START_DAY)); $d2_format = $d2-&gt;format('Y-m-d H:i:s'); $end_of_day = new DateTime($end_of_day_format); $diff = $start_of_day-&gt;diff($d2)-&gt;format('%H:%I:%S'); $diff = split(':', $diff); $diff = $diff[0]*3600 + $diff[1]*60 + $diff[0]; $worked_time += $diff; } else { // otherwise, just count the full day of work $worked_time += DAY_WORK; } } if ($nb&gt; 10) die("die ".$nb); } echo sprintf('Works from %s to %s, You worked %d hour(s)', $date_begin, $date_end, $worked_time/60/60); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPHP - Calculating working hours between two dates but exclude the time when request is on hold
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAsenar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for your help. So basically i will call this function twice to get the total working hours and then total hours on hold (after giving start and end date/time for the on hold time)...Minus it from total working hours and that should give me the actual time of the request, is that correct?
      singulars
      1. PO
      1. USTahir
    2. COSorry I'm not sure to understand what you mean by "on hold time", but this code is simple : for each days, it adds the worked hours except if this is the 1st or the last day. If you want calculate other time, you can change that code to add others variables inside the proper if/else part, I m not sure calling it twice can do something. But to be sure, you can try it, and if that doesn't work for you, add some debug strings like `echo "so far, the ".$date->format('Y-m-d').", worked_time=$worked_time<br/>";` to see how the time is calculated.
      singulars
      1. PO
      1. USAsenar
    3. CObascially on hold is the time in which the request had not been worked on as more information was required on that. So I need to minus the on hold time from the total time. so let's say if a request start and end time is 20 hours and there was a 10 hour period in between where that request was on hold...total time will become total time = total time (start to end) minus on hold time.
      singulars
      1. PO
      1. USTahir
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload