SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    calculating time

    i have a db record that may contain multiple start and end time.
    how would i calculate total time? i've never worked with time in mysql or php so i wouldn't know where to start.

    for instance
    a task may be assigned to 2 people so it would mean each person has a start and end time.
    1:00am-2:00am = 1 hour
    3:30pm-5:00pm = 1:30
    so total time would be 2:30

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How are the start and end times stored? Dates? Timestamps? Integer values in seconds? Strings?

  3. #3
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have the field types as time.
    also, how would i get the field to accept time in 12 hour format?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That sounds like a display issue, which is the job of your application. The database is concerned solely with the storage of information, and the most efficient way to store a time is as a single (24 hour) value, not a value plus an AM/PM marker.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SEC_TO_TIME( TIME_TO_SEC(end) - TIME_TO_SEC(start) ) AS elapsed_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i've gotten to the point where it loops through and displays all start and finish times for the record. i can't seem to add up the total time.
    Code:
    $rowCount=1;
    $totaltime=0;
    $Qry='SELECT wid, mid, assignto, start, finish, timediff(finish,start) as elapsetime from workassign WHERE mid = "' . $_GET['id'].'"';
    //echo $Qry;
    
    $sql = mysql_query($Qry)or die (mysql_error());
    
    // the form is now dynamic and length depends on how many rows have billid in todo.
    // Fetch the array of records and Loop through them
    while($results = mysql_fetch_assoc($sql))  
    {
    echo '<table>
         <tr>';
    
           <input type="hidden" name="wid[]" value="'.$results['wid'].'"/>
          <td><SELECT name="assignto[]">';
           // the following could be put in an array before the while loop to save DB calls
            $sQuery = mysql_query("SELECT sid, serviceby FROM service");
           while ($sRow = mysql_fetch_row($sQuery)) {
          if( $results['assignto'] == $sRow[0] ) {
              print ("<option selected value='$sRow[0]'>$sRow[1]</OPTION>");
         } else {
              print ("<option value='$sRow[0]'>$sRow[1]</OPTION>");
         } // end of conditions
         } // end of while loop                                                            
                 echo '</SELECT>';
    			echo '<br />
    		     <nobr>Start: <input type="text" name="start[]" id="start" value="'.$results['start'].'" onchange="return valforms(this.form,this)" editcheck="type=^([01]\d|20|21|22|23):[0-5]\d$=24 hour time ie. 17:30" /> Finish: <input type="text" name="finish[]" id="start" value="'.$results['finish'].'" onchange="return valforms(this.form,this)" editcheck="type=^([01]\d|20|21|22|23):[0-5]\d$=24 hour time ie. 17:30" />  </nobr>
    			</td></tr></table>		
    
    ';
    
    $totaltime=$results['elapsetime'];
    $gtotal=$gtotal + $totaltime;
     // Increment the row count
    $rowCount++;
    }
    if i echo $totaltime it will give me this:
    00:30:00
    01:00:00
    so $gtotal =01:30:00, but it's not it's giving me 01:00:00

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You cannot do arithmetic in php on strings, unless the strings are of a specific format. Well, you can, but you won't get the right result. For example, a pure number like "1234", you can safely do arithmetic on. But something like "01:00:00" php will simply not understand. php tries its best to guess what you mean, but it just uses the numbers on the left until it encounters a charcter it can't understand in a numeric context. So, for example, "01:00:00" gets converted to 01, because it chokes on the colon character.

    You should just tell mysql to give you seconds instead, as you can easily work with these whole numbers in php.

    Code:
    TIME_TO_SEC(end) - TIME_TO_SEC(start) AS elapsed_time
    You can then sum these values for each row in php, as you attempted.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •