Add timestamps to get total time

matthewst · April 30, 2007

I really have no idea how to even start to do this. I need to know how to add then display the the total amount of time for a given set of time stamps. For example my page looks like this:

task--------time

1-----------start at 8:00

1-----------end at 8:10

2-----------start at 8:12

3-----------start at 8:13

2-----------end at 8:14

3-----------end at 8:20

//notice there is no 8:11

I need to add then display the total amount of time it took for all tasks. Then

separate the individual tasks then figure and diaplay the total amount of time

needed for each individual task.

What I need is this:

Total Time: 19 min

task 1 10 min

task 2 2 min

task 3 7 min

--------------------------------

task--------time

1-----------start at 8:00

1-----------end at 8:10

2-----------start at 8:12

3-----------start at 8:13

2-----------end at 8:14

3-----------end at 8:2

//notice it didn't count 8:11

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$result4=mysql_query($query4);
while ($row4 = mysql_fetch_assoc($result4))

{
$action6 = $row4['action'];
$time_table4 = $row4['time'];
$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);


        $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 width = '20%'>$action6</td><td align = center width = '25%'>$showtime2</td></tr>";
echo "</table></center>";
}

suttercain · April 30, 2007

Where are the tasks in your code? For example, where is task 1 that equals 10 mins?

obsidian · April 30, 2007

I really have no idea how to even start to do this.

Well, we need a little more info before we can help you come to a viable solution. First, what format are your start and end times for each task stored in? Next, how do you group the tasks together? I would suggest you write two functions: one that will simply calculate the time involved for a single task, and the second that will allow you to loop over multiple tasks and keep a running total.

If your start and end times are stored as TIME or DATETIME types, you can actually let MySQL do the bulk of your calculations for you right within your queries.

So, as I've said, give us a little more background on how you're storing things, and I'm sure we can help you figure out something workable.

obsidian · April 30, 2007

After reading the post more thoroughly, it looks as though you are storing all your times as UNIX timestamps. As such, you can simply run some calculations on the difference between the two:

<?php
function getMinutes($ts1, $ts2) {
  $min  = 60;
  $diff = abs($ts1 - $ts2);
  return ceil($diff / $min);
}

$start = strtotime("10:15");
$end   = strtotime("11:03");
echo getMinutes($start, $end) . " minutes for this task";
?>

Hope that helps some.

matthewst · April 30, 2007

Sorry obsidian I should have been more clear.

There are more than two entries for the tasks in question

example:

task1---------start at 8:00

task1---------pause at 8:04

task1---------resume at 8:06

task1---------end at 8:10

//nothing at 8:05

task1 total time = 9 min

here is an example of my timestamps:

1170096118

obsidian · April 30, 2007

Sorry obsidian I should have been more clear.

There are more than two entries for the tasks in question

OK, so how do you know what the timestamps represent? Do you know that they always alternate starting and stopping, or do you have each timestamp flagged with what type it is?

matthewst · April 30, 2007

Each timestamp is associated with an action.

start = action_id 1

stop = action_id 2

pause = action_id 3

resume = action_id 4

and many others

here is a screen shot of what my page looks like now:

carol is an older lady who clicks the button several times in rapid succesion, i only need the first timestamp for records like hers

[attachment deleted by admin]

obsidian · April 30, 2007

Is it ever possible to have two start type actions in succession, or are you guaranteed to only have them in a start, stop, start, stop, etc sequence?

matthewst · May 1, 2007

yes, there are several starts in succession.

example:

task1---------start at 8:00

task2---------start at 8:01

task1---------pause at 8:04

task2---------end at 8:06

task1---------resume at 8:06

task1---------end at 8:10

//nothing at 8:05

task1 total time = 9 min

so I would need:

query=SELECT * FROM job_log WHERE ////get a record with a start action (200 or 210 or whatever), get the employee_id for that record then go to the next record with the same employee_id

obsidian · May 1, 2007

Is it ever possible to have two start type actions in succession, or are you guaranteed to only have them in a start, stop, start, stop, etc sequence?

Sorry, I should have been more specific. Is it safe to assume that there will never be two start actions of any sort applied to the same task back to back? If so, there are a couple ways to begin approaching a solution.

matthewst · May 1, 2007

no, several starts, stops, pauses and a lot of different actions

matthewst · May 1, 2007

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

<?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  #$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.

[attachment deleted by admin]

matthewst · May 2, 2007

I'm so close!!!

Here's what I have now. This code will place the timestamp and action being performed between each record.

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {

///////////////////////////////////
echo "$action6  ";
if ($action6=="Start Table PTG")
$start_table_ptg_time = $time_table4;
echo "  $start_table_ptg_time";
//////////////////////////////////

{
$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'];
        }

if($row['action_id'] != $last_value){ 

$last_value = $row['action_id'];
}
}

In the area between the ///// I need to do a little processing. The code that is in there now is just for s and g's. Instead of just placing the timestamp and action between each record I need to store the timestamp and action in a veriable. Then (probably with an if statement) do something like "if $action=start_1 then go to the next record with same employee_id and task_id and that has an action of stop_1 then do the math then display the result".

matthewst · May 2, 2007

baby steps

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {

if ($action6=="Start Outside Change"){
$start_action = $action6;
$start_outside_change = $time_table4;}

	elseif ($action6=="End Outside Change"){
	$end_action = $action6;
	$end_outside_change = $time_table4;
	$total_time = $end_outside_change - $start_outside_change;
	$total_time1 = date('h:i:s',$total_time);
	echo "$total_time1";}

else
echo "";

{
$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'];
        }

if($row['action_id'] != $last_value){ 

$last_value = $row['action_id'];
}
}

that code will generate this:

100621 Dawn Justice 03/05/07-08:49:50 End Outside Change

----------------06:06:04---------------------

6 hours 6 minutes 4 seconds

what i need is:

100621 Dawn Justice 03/05/07-08:49:50 End Outside Change

----------------06:04---------------------

this outside change only took 6 min 4 sec

however some times wil be in the hours (2:15:12 ---2 hours 15 min 12 sec)

matthewst · May 3, 2007

$total_time1 is returning "the time" not an amount of time.

In my code the result is 06:25:09-----(6:25 AM or PM)

What I need is the amount of time passed between $start and $end_outside_change which 25 min 9 sec

$total_time = $end_outside_change - $start_outside_change;
$total_time1 = date('h:i:s',$total_time);
echo "$total_time1";

matthewst · May 3, 2007

as it turns out, i'm retarted.

cfw (codewalkers) wrote this for me

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {

if ($action6=="Start Outside Change"){
$start_action = $action6;
$start_outside_change = $time_table4;}

	elseif ($action6=="End Outside Change"){
	$end_action = $action6;
	$end_outside_change = $time_table4;
	$sec = $end_outside_change - $start_outside_change;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "$total_time";}

else
echo "";

matthewst · May 3, 2007

How would I handle a pause/resume situation?

I'm currently trying to get this to work(no success yet):

if ($action6=="Start Table PTG"){
$start_action = $action6;
$start_table_ptg = $time_table4;}

	elseif ($action6=="Pause"){
	$pause_action = $action6;
	$pause_table_ptg = $time_table4;}

	elseif ($action6=="Resume"){
	$resume_action = $action6;
	$resume_table_ptg = $time_table4;
	$sec = $pause_table_ptg - $resume_table_ptg;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$pause_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec);}

	elseif ($action6=="Finish Table PTG"){
	$end_action = $action6;
	$end_table_ptg = $time_table4;
	$sec = $end_table_ptg - $start_table_ptg - $pause_time;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "$total_time";}

else
echo "";

The elasped time shows 02:09:42, which is correct when not counting the pause. Counting the pause it should be 01:53:48.

[attachment deleted by admin]

matthewst · May 3, 2007

I'm an idiot. If I echo $soft_stop_time or $soft_start_time in there respective {}'s I get the correct number of seconds. Where do I go from here? How do I "move" veriables from one set of {}'s to another?

if ($action6=="Start Table PTG"){
$start_table_ptg = $time_table4;}


	elseif ($action6=="Pause"){
	$pause_table_ptg = $time_table4;		
	$soft_stop_time = $pause_table_ptg - $start_table_ptg;}

	elseif ($action6=="Resume"){
	$soft_start_time = $time_table4;}

	elseif ($action6=="Finish Table PTG"){
	$end_table_ptg = $time_table4;
	$soft_end_time = $end_table_ptg - $soft_start_time;}

	elseif ($action6=="Finish Table PTG"){
	$end_table_ptg = $time_table4;
	$sec = $end_table_ptg - $start_table_ptg;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$soft_end_time</td>";
	echo "</tr></table></center>";}


else
echo ""

matthewst · May 3, 2007

Can't edit my previous post.

I'm an idiot. If I echo $soft_stop_time or $soft_start_time in their respective {}'s I get the correct number of seconds. If I echo $soft_stop_time or $soft_start_time (or $total_time for that matter) in the table where it's supposed to be I get nothing. Where do I go from here? How do I "move" veriables from one set of {}'s to another?

if ($action6=="Start Table PTG"){
$start_table_ptg = $time_table4;}


	elseif ($action6=="Pause"){
	$pause_table_ptg = $time_table4;		
	$soft_stop_time = $pause_table_ptg - $start_table_ptg;}

	elseif ($action6=="Resume"){
	$soft_start_time = $time_table4;}

	elseif ($action6=="Finish Table PTG"){
	$end_table_ptg = $time_table4;
	$soft_end_time = $end_table_ptg - $soft_start_time;}

	elseif ($action6=="Finish Table PTG"){
	$end_table_ptg = $time_table4;
	$sec = $end_table_ptg - $start_table_ptg;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$soft_end_time</td>";
	echo "</tr></table></center>";}


else
echo ""

Wildbug · May 3, 2007

Here's a thought related to letting MySQL do all the work. Do you have all of the possible start/stop actions? If so, assign them -1 for start and +1 for stop. You can add an extra column for this if you need to. Then finding the amount of time a particular project accrued across different time periods should be as simple as:

SELECT
projectname,
SEC_TO_TIME(SUM(start_or_stop * UNIX_TIMESTAMP(`time`)))
FROM job_log JOIN job_actions ON action=action.id
GROUP BY projectname (or WHERE projectname='whatever')

That way start times (in seconds, as a timestamp) will be negative, end times will be positive, and when added together will result in the positive difference of how long the job took. Of course, if a project has an "open" task, you'll get a very negative number. That could probably be accounted for.... somehow.

matthewst · May 4, 2007

I know even less about mysql then I do about php. I am having diffuculty accounting for pauses. Does anyone have any ideas on how to remove the paused time from my $total_time?

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {

if ($action6=="Start Creation"){
$start_creation = $time_table4;}
if ($action6=="Start Table PTG"){
$start_table_ptg = $time_table4;}
if ($action6=="Cutting - Begin"){
$start_cutting_begin = $time_table4;}
if ($action6=="Begin 1st Proof"){
$start_begin_proof = $time_table4;}
if ($action6=="Proof Print - Begin"){
$start_print_proof_begin = $time_table4;}
if ($action6=="Start In-House Fix"){
$start_inhouse_fix = $time_table4;}
if ($action6=="Final Print - Begin"){
$start_final_print_begin = $time_table4;}
if ($action6=="Start Outside Change"){
$start_outside_change = $time_table4;}

	elseif ($action6=="End Creation"){
	$end_creation = $time_table4;
	$sec = $end_creation - $start_creation;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="Finish Table PTG"){
	$end_table_ptg = $time_table4;
	$sec = $end_table_ptg - $start_table_ptg;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="Cutting - End"){
	$end_cutting_end = $time_table4;
	$sec = $end_cutting_end - $start_cutting_begin;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="End 1st Proof -OK"){
	$end_proof_ok = $time_table4;
	$sec = $end_proof_ok - $start_begin_proof;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="End 1st Proof - FIX"){
	$end_proof_fix = $time_table4;
	$sec = $end_proof_fix - $start_begin_proof;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="Proof Print - Finish"){
	$end_print_proof_finish = $time_table4;
	$sec = $end_print_proof_finish - $start_print_proof_begin;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="End In-House Fix"){
	$end_inhouse_fix = $time_table4;
	$sec = $end_inhouse_fix - $start_inhouse_fix;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="Final Print - End"){
	$end_final_print_end = $time_table4;
	$sec = $end_final_print_end - $start_final_print_begin;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

	elseif ($action6=="End Outside Change"){
	$end_outside_change = $time_table4;
	$sec = $end_outside_change - $start_outside_change;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

else{
echo "";}

I need something along the lines of "if task had a pause then find resume for that task and subtract the amount of paused time"

Wildbug · May 4, 2007

At least try the SQL solution. It's SO much more elegant than that PHP mess, and it's really simple -- no complicated SQL, no complicated PHP date handling code.

Since you have your job "actions" in a table already, just add a column to that table with -1, 0, or 1 if the action is a "starting/resume" action (-1), "ending/pause" action (+1), or neither (0). Then multiply this coefficient by a UNIX timestamp and add up the results. A UNIX timestamp is the number of seconds since 1/1/1970 GMT/UTC.

For example, this is what would happen (in detail):

Job Action	Time (mm:ss)	Difference	Unix timestamp	coefficient	=
Starting foo	00:00 (now)			1178291436	-1		-1178291436
Starting bar	02:00				1178291556	-1              -1178291556
Pausing foo	02:15		2:15		1178291571	 1               1178291571
Something foo	08:33				1178291949	 0	             0
Ending bar	10:00		8:00		1178292036	 1               1178292036
Resuming foo	10:00				1178292036	-1              -1178292036
Ending foo	15:22		5:22		1178292358	 1               1178292358

Sum										937 (seconds)
									15:37 (mm:ss)

But, with the addition of that coefficient column in your job actions table, this all happens behind-the-scenes in MySQL, and you don't have to screw with deciding where to stop and start.

And, like I said earlier, the only time you should get an incorrect number is when there is still an unfinished job -- you will get a large, negative number, but this should be correctable with the right SQL statement or code, but still less complex than writing your own period-handler.

Wildbug · May 4, 2007

I think the SQL solution is cool enough that I did some work for you. Here's an example based on a simple test set with the following sample data:

mysql> SELECT * FROM jobs ORDER BY job,ts; SELECT * FROM actions;
+----+--------+--------+---------------------+
| id | job    | action | ts                  |
+----+--------+--------+---------------------+
|  1 | Job #1 | Start  | 2007-05-04 11:37:24 |
|  2 | Job #1 | Pause  | 2007-05-04 11:47:58 |
|  3 | Job #1 | Resume | 2007-05-04 11:54:20 | // <-- Notice Job #1 is still "open"
|  4 | Job #2 | Start  | 2007-05-04 11:38:34 |
|  5 | Job #2 | Stop   | 2007-05-04 16:52:53 |
+----+--------+--------+---------------------+
5 rows in set (0.00 sec)

+--------+------+
| name   | coef |
+--------+------+
| Start  |   -1 |
| Stop   |    1 |
| Other  |    0 |
| Pause  |    1 |
| Resume |   -1 |
+--------+------+
5 rows in set (0.00 sec)

The trivial query works, but not well:

SELECT job,SEC_TO_TIME(SUM(coef*UNIX_TIMESTAMP(ts))) AS "Total Time" FROM jobs JOIN actions ON jobs.act
ion=actions.name GROUP BY job;
+--------+---------------+
| job    | Total Time    |
+--------+---------------+
| Job #1 | -327303:43:46 | // <-- Incorrect due to "open" action
| Job #2 | 05:14:19      |
+--------+---------------+
2 rows in set (0.00 sec)

Okay, I'll admit my final query is a little complicated. It doesn't need to be, but I included two possibilities of "total" time: one using only recorded timestamps (ignoring "open" actions), and one using the current time as the "closure" to "open" jobs (probably more accurate). The query, indented for legibility, is as follows:

SELECT
jobs.job,
SEC_TO_TIME(IF(SUM(coef*UNIX_TIMESTAMP(ts)) > 0, SUM(coef*UNIX_TIMESTAMP(ts)), SUM(coef*UNIX_TIMESTAMP(ts)) + last_unfinished)) AS "Total Time, recorded",
SEC_TO_TIME(IF(SUM(coef*UNIX_TIMESTAMP(ts)) > 0, SUM(coef*UNIX_TIMESTAMP(ts)), SUM(coef*UNIX_TIMESTAMP(ts)) + UNIX_TIMESTAMP())) AS "Total Time, current"
FROM
jobs JOIN actions ON jobs.action=actions.name
JOIN (
	SELECT
		job,
		UNIX_TIMESTAMP(MAX(ts)) AS last_unfinished
	FROM actions JOIN jobs ON actions.name=jobs.action
	WHERE coef=-1
	GROUP BY job
) AS sub ON jobs.job=sub.job
GROUP BY jobs.job;

+--------+----------------------+---------------------+
| job    | Total Time, recorded | Total Time, current |
+--------+----------------------+---------------------+
| Job #1 | 00:10:34             | 00:29:18            |
| Job #2 | 05:14:19             | 05:14:19            |
+--------+----------------------+---------------------+
2 rows in set (0.00 sec)

The subquery in the FROM list is to capture the latest, "open" timestamp in case we need to use it to subtract from the cumulative time (when it's negative -- indicating unfinished business) to get an accurate value. This value is used in the first IF() statement ("last_unfinished"). The second IF() statement assumes that whoever is working on the job is still working on the job and uses the current time in the calculation of the total time spent on the job; it uses no subqueries.

You can remove the jobs.job from the SELECT and the GROUP BY clause, replacing it with WHERE jobs.job='job name' to find times for a specific job.

Notes: The SUM() function works because of the additive/commutive properties of addition. You don't need to align each start/stop, pause/resume pair; you just need to know their timestamp. The UNIX_TIMESTAMP() function returns the number of seconds since 1/1/1970 UTC. SEC_TO_TIME() returns seconds in HH:MM:SS format.

Easy, right?

matthewst · May 4, 2007

Thanks again cfw.

I was able to put your code to good use. The page loads alot faster! The pause and resume times are now correct but I still can't figure out how to add them to get the correct amount of $total_time.

if ($action3=="Start Creation" || $action3=="Start Table PTG" || $action3=="Cutting - Begin" || $action3=="Begin 1st Proof" || $action3=="Proof Print - Begin" || $action3=="Start In-House Fix" || $action3=="Final Print - Begin" || $action3=="Start Outside Change" || $action3=="Start Outside Fix" || $action3=="Resume"){
$start_action = $time;}

	elseif ($action3=="End Creation" || $action3=="Finish Table PTG" || $action3=="Cutting - End" || $action3=="End 1st Proof -OK" || $action3=="End 1st Proof - FIX" || $action3=="End In-House Fix" || $action3=="Final Print - End" || $action3=="End Outside Change" || $action3=="End Outside Fix" || $action3=="Pause"){
	$end_action = $time;
	$sec = $end_action - $start_action;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}

else{
echo "";}

In the attachment I need to add 25:47 and 1:28:01 and make that the new $total_time.

[attachment deleted by admin]

matthewst · May 4, 2007

just caught your reply wild

i'll give it a shot

Sign In

Add timestamps to get total time

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information