subtracting time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

subtracting time

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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);
?>
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

hmm never seen that before i'll have a go, thanks for the tip :)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What was the code you were trying to use to do the subtraction?

Mac
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

woohoo

Post 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>';
}

?>
Post Reply