Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding and subtracting timestamps

Status
Not open for further replies.
matthewst

matthewst

IS-IT--Management
May 1, 2007
25
US
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 data in another table. Is this possible with mysql?
 
Sort by date Sort by votes
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>

how do i post an attachment? i have a sample of my database (less than 200k)
 
Upvote 0 Downvote
Forget the php get it working as plain sql first

using yourtable
major
minor
timelog
userid
action

you need to join the table to itself using userid and join the start record with the end record

select
userid,
timediff(a.timelog,b.timelog)
from yourtable a inner join yourtable b
on(a.userid=b.userid and a.action=200 and b.action=999)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Stored Procedures and triggers
Replies
9
Views
414
penguinspeaks
penguinspeaks
EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
389
EdLentz
EdLentz
Darryl Johnson
  • Locked
  • Question
Adding labor hours to a date time field
Replies
5
Views
199
Andrzejek
Andrzejek
Yossi Nixon
  • Locked
  • Question
MySQL CPU usage and CPU wait
Replies
1
Views
126
feherke
feherke
jyothi12389
  • Locked
  • Question
create a schedule and MYSQL loading csv data to table
Replies
0
Views
102
jyothi12389
jyothi12389

Part and Inventory Search

Sponsor

Back
Top