Page 1 of 1

subtracting time

Posted: Wed Aug 13, 2003 7:00 am
by irealms
i have 2 fields in the TIME format i need to subtract, when i try it just subtracts the hour value. I've tried looking in the manual and can't seem to get anything to work. Anyone help me out on this?

Posted: Wed Aug 13, 2003 8:23 am
by JAM
By using DATE()?

Code: Select all

<?php
    function getlmktime ($timeStamp) {
        // arrange to fit your needs...
        return date("\[ F d, Y \] h:i A", $timeStamp+date("Z")); 
    }

    echo getlmktime(1042857927);
?>

Posted: Thu Aug 14, 2003 5:06 am
by irealms
the time is in format TIME not TIMESTAMP so not sure how to do it atm, just keeps subracting hours:

10:30 and 12:00 will show 2 hours and not 1:30 as it works on the hour value only

Posted: Thu Aug 14, 2003 10:35 am
by JAM
I think my previous post belonged elsewhere as I found another post with a similar problem, that I thought I replied to earlier...

Oh well... Sorry for that. Tried playing around with this?

Code: Select all

select sec_to_time(time_to_sec(field1) - time_to_sec(field2)) from table

Posted: Thu Aug 14, 2003 12:15 pm
by irealms
hmm never seen that before i'll have a go, thanks for the tip :)

Posted: Fri Aug 15, 2003 9:04 am
by twigletmac
What was the code you were trying to use to do the subtraction?

Mac

Posted: Mon Aug 18, 2003 3:38 am
by irealms
just a standard subtract line as in $a = $a - $b, didn't know it only worked on hours or that there were things like sec_to_time

woohoo

Posted: Mon Aug 18, 2003 4:06 am
by irealms
thanks for the help, i managed to do it after reading JAM's 2nd post and using the mysql manual. This is the final code:

Code: Select all

<?php
if (isset($_POST['tselect']))
   {
	   $tdate = ''.$_POST['yearstart'].'-'.$_POST['monthstart'].'-'.$_POST['daystart'].'';
$disptsheet = "SELECT tid,groupname,username,tdate,starttime,endtime,billable,description FROM timesheets WHERE groupname='$_SESSION[groupname]' AND username='$_SESSION[valid_user]' AND tdate='$tdate'";
$tsheetq = mysql_query($disptsheet, $db_conn) or die("Query tsheetq Failed: ".mysql_error());
if (mysql_num_rows($tsheetq)==0)
	   {
	echo '<p class="main">There are no entries for the selected date.</p>';
	   }
	   else
	   {
echo '<table class="main" cellspaing="5" cellpadding="5" border="0">';
echo '<tr><td valign="top"><b>Start time</b></td><td valign="top"><b>End time</b></td><td valign="top"><b>Duration</b></td><td valign="top"><b>Billable?</b></td><td valign="top"><b>Description</b></td><td></td></tr>';
while ($trow = mysql_fetch_assoc($tsheetq))
{
//took me a while to figure out where to put the time query but it works now
	$timesum = "SELECT sec_to_time(time_to_sec(endtime) - time_to_sec(starttime)) AS total_time FROM timesheets WHERE groupname='$_SESSION[groupname]' AND username='$_SESSION[valid_user]' AND tid='$trow[tid]'";
	$tsumq = mysql_query($timesum, $db_conn) or die("Query timesum Failed: ".mysql_error());
	$trow2 = mysql_fetch_assoc($tsumq);
	echo '<tr>';
	echo '<td>'.$trow['starttime'].'</td>';
	echo '<td>'.$trow['endtime'].'</td>';
	echo '<td>'.$trow2['total_time'].'</td>';
	echo '<td>'.$trow['billable'].'</td>';
	echo '<td>'.$trow['description'].'</td>';
	if ($trow['username']==$_SESSION['valid_user'])
	{
		echo '<td><a href="index.php?page=timesheet&&action=edit&&tid='.$trow['tid'].'" />Edit</a></td>';
	}
	echo '</tr>';
}

?>