Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Here's my method</p> <p>I converted everything to seconds to make it a lot easier.</p> <p>Here's the rate table indexed by seconds. Theres only 3 time slots for monday</p> <pre><code>// 0-28800 (12am-8am) = 1.5 // 28800-61200 (8am-5pm) = 1 // 61200-86399 (5pm-11:50pm) = 1.5 $rate_table = array( 'monday' =&gt; array ( '28800' =&gt; 1.5, '61200' =&gt; 1, '86399' =&gt; 1.5 ) ); </code></pre> <p>It uses this function to convert hh:mm:ss to seconds</p> <pre><code>function time2seconds( $time ){ list($h,$m,$s) = explode(':', $time); return ((int)$h*3600)+((int)$m*60)+(int)$s; } </code></pre> <p>This is the function that returns a rate table</p> <pre><code>function get_rates( $start, $end, $rate_table ) { $day = strtolower( date( 'l', strtotime( $start ) ) ); // these should probably be pulled out and the function // should accept integers and not time strings $start_time = time2seconds( end( explode( 'T', $start ) ) ); $end_time = time2seconds( end( explode( 'T', $end ) ) ); $current_time = $start_time; foreach( $rate_table[$day] as $seconds =&gt; $multiplier ) { // loop until we get to the first slot if ( $start_time &lt; $seconds ) { //$rate[ $seconds ] = ( $seconds &lt; $end_time ? $seconds : $end_time ) - $current_time; $rate[] = array ( 'start' =&gt; $current_time, 'stop' =&gt; $seconds &lt; $end_time ? $seconds : $end_time, 'duration' =&gt; ( $seconds &lt; $end_time ? $seconds : $end_time ) - $current_time, 'multiplier' =&gt; $multiplier ); $current_time=$seconds; // quit the loop if the next time block is after clock out time if ( $current_time &gt; $end_time ) break; } } return $rate; } </code></pre> <p>Here's how you use it</p> <pre><code>$start = '2010-05-03T07:00:00'; $end = '2010-05-03T21:00:00'; print_r( get_rates( $start, $end, $rate_table ) ); </code></pre> <p>returns </p> <pre><code>Array ( [0] =&gt; Array ( [start] =&gt; 25200 [stop] =&gt; 28800 [duration] =&gt; 3600 [multiplier] =&gt; 1.5 ) [1] =&gt; Array ( [start] =&gt; 28800 [stop] =&gt; 61200 [duration] =&gt; 32400 [multiplier] =&gt; 1 ) [2] =&gt; Array ( [start] =&gt; 61200 [stop] =&gt; 75600 [duration] =&gt; 14400 [multiplier] =&gt; 1.5 ) ) </code></pre> <p>Basically the code loops over the rate table and finds how many seconds from the given time slot belong to each rate.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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