adding and subtracting timestamps

matthewst · #1 (**permalink**) 05-01-07, 09:12

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?

r937 · #2 (**permalink**) 05-01-07, 10:18

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

matthewst · #3 (**permalink**) 05-01-07, 11:19

Thanks!
give me some time to let that soak in

I'll post back when I run into trouble

matthewst · #4 (**permalink**) 05-01-07, 12:12

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.

r937 · #5 (**permalink**) 05-01-07, 12:37

Quote:

Originally Posted by matthewst

... for anyone still willing to help me.

my best wishes for speedy resolution of your problem

dbmab · #6 (**permalink**) 05-01-07, 12:58

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.

matthewst · #7 (**permalink**) 05-01-07, 14:41

thank you for you time

Internet Services

Web Development

Digital Marketing

Consumer Tech