Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: adding and subtracting timestamps

    I'm using mysql 5.0.24 and phpmyadmin 2.8.1

    example database:
    major_task---minor_task---userid---time--------action
    -----------------------------------------------------
    task1---------------------123------1234567891---200 (start1)
    task2---------------------456------1234567899---200 (start1)
    task1---------------------123------1234567999---210 (pause)
    -------------task3--------789------1234569999---300 (start2)
    task1---------------------123------1234599988---301 (resume)
    task1---------------------123------1234599999---999 (end)

    I need a way to total the time for task1. The difficult part is that most of the time user1 will begin task1 and user2 will begin task2 before the first user ends his task. The good news is each record has an action associated with it. I need mysql to isolate a given taskid associated with a start action then find the next record with the same taskid associated with a stop action. Do the math and store the taskid (task1, task2, etc) and the total time in another table. Is this possible with mysql?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is very possible

    "isolate a given taskid associated with a start action ..."
    Code:
      FROM tasks as start
     WHERE start.action = 200
    " ... then find ... record with the same taskid associated with a stop action"
    Code:
      FROM tasks as start
    INNER
      JOIN tasks as stop
        ON stop.taskid = start.taskid
       AND stop.action = 999
     WHERE start.action = 200
    " ... the next record "
    Code:
      FROM tasks as start
    INNER
      JOIN tasks as stop
        ON stop.taskid = start.taskid
       AND stop.action = 999
       AND stop.time =
           ( select min(time)
               from tasks
              where taskid = start.taskid
                and action = 999 )
     WHERE start.action = 200
    "Do the math and store the taskid (task1, task2, etc) ..."
    Code:
    SELECT start.major_task
         , start.minor_task
         , stop.time - start.time as total_time
      FROM tasks as start
    INNER
      JOIN tasks as stop
        ON stop.taskid = start.taskid
       AND stop.action = 999
       AND stop.time =
           ( select min(time)
               from tasks
              where taskid = start.taskid
                and action = 999 )
     WHERE start.action = 200
    " ... in another table"
    Code:
    CREATE TABLE another
    SELECT start.major_task
         , start.minor_task
         , stop.time - start.time as total_time
      FROM tasks as start
    INNER
      JOIN tasks as stop
        ON stop.taskid = start.taskid
       AND stop.action = 999
       AND stop.time =
           ( select min(time)
               from tasks
              where taskid = start.taskid
                and action = 999 )
     WHERE start.action = 200
    yes, possible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    4

    Thanks!
    give me some time to let that soak in

    I'll post back when I run into trouble

  4. #4
    Join Date
    May 2007
    Posts
    4
    Ok I've been messing with this thing for over an hour. I can't figure out what I'm doing or doing wrong.

    The full story. I work for a company that sell ad space on restraunt tabletops. We need a way of tracking how long it takes to put together a given ad or table. Some restraunts are more paticular than others and the longer we take the less me make (time is money). The task_id's are actually ad and table id's.

    I'm not looking for someone to write the code for me but this should make things easier for anyone still willing to help me.

    table_ad_time1.php
    Code:
    <?php
    	include('include/user_check.php');
    	include('include/db_con.php');
    	$id = $_SESSION['track_id'];
    ?>
    
    <html>
    <BODY BGCOLOR=#FFFFFF leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
    		<div align="center">
    			<TABLE WIDTH=758 BORDER=0 CELLPADDING=0 CELLSPACING=0>
    				<? include('include/top.php'); ?>
    				<TR height="516">
    				  <TD valign="top" height="516">
    						<div align="center">
    
    <?php
    
    ///////////// added a table id for convenience
    $table_id=100581;
    /////////////
    $query="SELECT rest_name FROM abc_tables WHERE table_id=$table_id";
    $result=mysql_query($query);
    while ($row = mysql_fetch_assoc($result))
    {
    $rest_name = $row['rest_name'];
    }
    ?>
     
    <?php
    echo "<center>$rest_name<br><br></center>";
    echo "<center>Table ID &nbsp;#$table_id<br><br></center>";
    echo "<center><table border = '1' cellspaceing = '2' cellpadding = '4' width = '100%' bgcolor = '#999999'>";
    echo "<tr><td width = '40%'><font color='#ffffff'>Company Name</font></td><td width = '20%'><font color='#ffffff'>Employee Name</font></td><td width = '20%'><font color='#ffffff'>Time</font></td><td width = '25%'><font color='#ffffff'>Action</font></td></tr>";
    echo "</table><br></center>";
    
    $query4="SELECT * FROM job_log WHERE $table_id=table_id";
    
    $result4=mysql_query($query4);
    while ($row4 = mysql_fetch_assoc($result4))
    
    {
    $time_table4 = $row4['time'];
    $employee_name5 = $row4['employee_id'];
    $action6 = $row4['action'];
    $showtime2 = date('m/d/y-h:i:s',$time_table4);
    	if ($showtime2=="12/31/69-06:00:00")
    	$showtime2 = "No Entry";
    	else
    	$showtime2 = date('m/d/y-h:i:s',$time_table4);
    
    	    $query5 = "SELECT * FROM employees WHERE employee_id = '$employee_name5'";
            $result5 = mysql_query($query5);
            while($row5 = mysql_fetch_assoc($result5)) 
            {
    				$employee_name5 = $row5['fname']." ".$row5['lname'];
    				if ($employee_name5=="")
    				$employee_name5 = "No Entry";
    				else
    				$employee_name5 = $row5['fname']." ".$row5['lname'];
                    
            }
    		
    		$query6 = "SELECT * FROM job_actions WHERE action_id = '$action6'";
            $result6 = mysql_query($query6);
            while($row6 = mysql_fetch_assoc($result6)) 
            {
                     $action6 = $row6['action_name'];
    				 	if ($action6=="")
    					$action6 = "No Entry";
    					else
    					$action6 = $row6['action_name'];
            }
    	
    echo "<center><table border = '0' cellspaceing = '0' cellpadding = '1' width = '100%' bgcolor = '#ffffff'>";
    echo "<tr><td align = 'center' abbr = abbr_text width = '40%'>$table_id</td><td align = 'center' width = '20%'>$employee_name5</td><td align = 'center' width = '20%'>$showtime2</td><td align = center width = '25%'>$action6</td></tr>";
    echo "</table></center>";
    }
    
    $query="SELECT ad_order.company company, job_log.employee_id employee_id, job_log.time time, job_log.table_id table_id, job_log.action action FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE ad_order.cust_id=$table_id ORDER BY company,time";
    
    $result=mysql_query($query);
    while ($row = mysql_fetch_assoc($result))
    
    {
    $company = $row['company'];
    $time = $row['time'];
    $employee_name2 = $row['employee_id'];
    $action3 = $row['action'];
    $showtime = date('m/d/y-h:i:s',$time);
    	if ($showtime=="12/31/69-06:00:00")
    	$showtime = "No Entry";
    	else
    	$showtime = date('m/d/y-h:i:s',$time);
    	
    	
    //////////////////////////////////////////////////////////////////	
    //	
    //	this is where i tried your code r937
    //
    //	
    //	
    //	
    //	
    /////////////////////////////////////////////////////////////////	
    	
    	
    	
    	    $query2 = "SELECT * FROM employees WHERE employee_id = '$employee_name2'";
            $result2 = mysql_query($query2);
            while($row2 = mysql_fetch_assoc($result2)) 
            {
    				$employee_name2 = $row2['fname']." ".$row2['lname'];
    				if ($employee_name2=="")
    				$employee_name2 = "No Entry";
    				else
    				$employee_name2 = $row2['fname']." ".$row2['lname'];
                    
            }
    		
    		$query3 = "SELECT * FROM job_actions WHERE action_id = '$action3'";
            $result3 = mysql_query($query3);
            while($row3 = mysql_fetch_assoc($result3)) 
            {
                     $action3 = $row3['action_name'];
    				 	if ($action3=="")
    					$action3 = "No Entry";
    					else
    					$action3 = $row3['action_name'];
            }
    	
    echo "<center><table border = '0' cellspaceing = '0' cellpadding = '1' width = '100%' bgcolor = '#ffffff'>";
    echo "<tr><td align = 'center' abbr = abbr_text width = '40%'>$company</td><td align = 'center' width = '20%'>$employee_name2</td><td align = 'center' width = '20%'>$showtime</td><td align = center width = '25%'>$action3</td></tr><td align = 'center' abbr = abbr_text width = '40%'>$table_time4</td>";
    echo "</table></center>";
    }
    echo "<br><br><br>";
    mysql_close();
    ?>
    
    </TD>
    </TR>
    <TR>
    <TD COLSPAN=3><IMG SRC="images/inside_08.gif" WIDTH=758 HEIGHT=3></TD>
    </TR>
    </TABLE>
    </body>
    </html>
    I don't need the total_table_time table to be populated but i thought it would be eaiser to let sql do the work then have php do it each time a page is called for.
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by matthewst
    ... for anyone still willing to help me.
    my best wishes for speedy resolution of your problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Creating this type of application/report is what programming (sql/php...) is all about. It is something you want/need to do to accomplish your goal.

    Posting a statement of what you are trying to accomplish or posting code without stating specifically what problems, symptoms, or errors you are getting is not within the scope of what a programming forum can help you with.

    Programming forums are only able to help by providing direction, answering specific questions, or helping with errors... If you need more help with something than what can be provided in a forum, you should consider hiring someone to do the work.

  7. #7
    Join Date
    May 2007
    Posts
    4
    thank you for you time

Posting Permissions

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