Adding Time

[Jamz]

Hi again guys,

 

Right, I have two times being output from a database... The first time is the time something is booked in for (eg: 16:30:00) and the second time is the duration that thing is booked in for (eg an hour and a half - 01:30:00)

 

Now, I want to figure out when that thing will be finished, so using the two examples above, it will be finished at 18:00:00, I've tried a few things with no success

 

Please help

 

Cheers

[RussellReal]

uhm, date_add would probably work for you

[Jamz]

Hmm think thats another SQL function that I dont really need... This is the code I have so far, and I would like the added time to be output into $till_time. You can see in the code I already get an output, but I would like the full output and not just the hour

 

<?php
dbconnect(); // Include function for database connection
$result = mysql_query("SELECT * FROM booking WHERE date='$date' and store='$store' ORDER BY time") or die ('Could not select ' . mysql_error());	
echo '<table class="cal" width="500px"><tr><th>Bookings</th></tr><tr><td>';

if(mysql_num_rows($result)==0){
	echo "Nothing Booked In Today";
}else{
	echo '<table width="660px" border="1"><tr>
    		<th>Time</th><th>Name</th><th>Car</th>
    		<th>Description</th><th width="100%">Comments</th><th>Paid?</th>
    		<th>Contact</th><th>Misc</th></tr>';
	while($row = mysql_fetch_array($result)){
		$till_time = $row['time'] + $row['duration'];
	echo'
  			<tr>
    			<td>'.$row['time'].'<br>till<br>'.$till_time.'</td>
   			 	<td>'.$row['customer_name'].'</td>
    			<td>'.$row['car_make'].' '.$row['car_model'].'<br>'.$row['car_year'].'</td>
    			<td>'.$row['work_desc'].'</td>
    			<td>'.$row['comments'].'</td>
    			<td>'.$row['paid'].'</td>
    			<td>'.$row['contact'].'</td>
			<td>Edit<br>Cancel</td>
  			</tr>';
	}
	echo '</table>';

[Pikachu2000]

SELECT DATE_ADD(start, INTERVAL TIME_TO_SEC(duration) SECOND) AS end_time FROM table

[Jamz]

So how would I implement that in my code above? Just add another SQL query?

[silkfire]

What do you mean by "full output"? Doesn't your code already add the times up?

[RussellReal]

select what pikachu's query is selecting

[Psycho]

Change your query as follows and this should get you an additional field returned in your db results called 'till_time'

 

$query = "SELECT *, DATE_ADD(start, INTERVAL TIME_TO_SEC(duration) SECOND) AS till_time
          FROM booking
          WHERE date='$date'
            AND store='$store'
          ORDER BY time";
$result = mysql_query($query) or die ('Could not select ' . mysql_error());

[Jamz]

Is there anyway to get it to output in an array:

 

ie:

 

<?php
dbconnect(); // Include function for database connection
$result = mysql_query("SELECT *, DATE_ADD(time, INTERVAL TIME_TO_SEC(duration) SECOND) AS till_time FROM booking WHERE date='$date' and store='$store' ORDER BY time") or die ('Could not select ' . mysql_error());	

while($row = mysql_fetch_array($result)){
echo'
  			<tr>
<td>'.$row['time'].'<br>till<br>'.$row['till_time'].'</td>
   			 	<td>'.$row['customer_name'].'</td>
    			<td>'.$row['car_make'].' '.$row['car_model'].'<br>'.$row['car_year'].'</td>
</tr>';
}

 

As what ive just added

<?php $row['till_time']; ?>

does not output anything

[PFMaBiSmAd]

DATE_ADD() expects a DATE or DATETIME for the first parameter and produced an error in my query browser - Incorrect datetime value: '16:30:00'.

 

Use this for that part of the query -

 

SEC_TO_TIME(TIME_TO_SEC(time) + TIME_TO_SEC(duration)) AS till_time

[Jamz]

DATE_ADD() expects a DATE or DATETIME for the first parameter and produced an error in my query browser - Incorrect datetime value: '16:30:00'.

 

Use this for that part of the query -

 

SEC_TO_TIME(TIME_TO_SEC(time) + TIME_TO_SEC(duration)) AS till_time

 

Perfect, works as it should

 

Cheers for that

[Pikachu2000]

Ah, see I was thinking the start field would be a DATETIME field so you'd get the correct value if the event ran past midnight.

[RussellReal]

^^ thought so too