Adding to a time() column

[Unknown98]

Is it possible to add (and subtract) time from a column that is in the mysql time() format (00:00:00)? I found the mysql ADDTIME() function:

 

 

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

 

Is this what I should be using? I tried it with variables instead of manual times and couldn't get it to work. I need to pull a time from a column, and then add a certain amount of time to it, from a user-input form. Say the column currently has 03:18:00 stored. The user inputs 01:00:00. The new time should be 04:18:00. Something like $newTime = $oldTime + $addedTime. I'm also using PDO, if that makes a difference in how I should be doing this.

 

-Unk

[PFMaBiSmAd]

I tried it with variables instead of manual times and couldn't get it to work.

 

If you posted what you tried and stated what result or error you got that leads you to believe that it didn't work, someone could directly help with the problem.

[Unknown98]

// insert route info into the DB
$stmt = $dbh->prepare("INSERT INTO routes (flt_num, depart_icao, arrive_icao, equipment, depart_time, arrive_time, days, block_time, distance, ticket_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bindParam(1, $flt_num);
$stmt->bindParam(2, $depart_icao);
$stmt->bindParam(3, $arrive_icao);
$stmt->bindParam(4, $equipment);
$stmt->bindParam(5, $depart_time);
$stmt->bindParam(6, $arrive_time);
$stmt->bindParam(7, $days);
$stmt->bindParam(8, $block_time);
$stmt->bindParam(9, $distance);
$stmt->bindParam(10, $ticket_price);

// insert one row
$flt_num = "".$_POST['flt_num']."";
$depart_icao = "".$_POST['origin']."";
$arrive_icao = "".$_POST['destination']."";
$equipment = "".$_POST['equipment'].""; //Registration number of the aircraft
$depart_time = "".$_POST['depart_time']."";
$arrive_time = "".$_POST['arrival_time']."";
$days = "".$_POST['days']."";
$block_time = "".$_POST['block_time'].""; //Total time of the route in HH:MM:SS format
$distance = "".$_POST['distance']."";
$ticket_price = "".$_POST['ticket_price']."";
$stmt->execute();

$sth = null;
$sth = $dbh->prepare("SELECT * FROM aircraft WHERE registration = ?");
$sth->execute(array($equipment));
while($data = $sth->fetch()){

$utilization = $data['utilization']; //Total utilization hours of the selected aircraft in HH:MM:SS format
}

//Attepmting to add block time of the route to the total utilization of the aircraft
$sth = null;
$sth = $dbh->prepare("SELECT ADDTIME('$utilization','$block_time') required_datetime");
$sth->execute();
while($row = $sth->fetch()){

//Update aircraft info with new utilization hours
$sth = null;
$sth = $dbh->prepare("UPDATE aircraft SET utilization = ? WHERE registration = ?");
$sth->execute(array($row['required_datetime'],$equipment));
}

 

 

 

I'm not getting any errors, the database just is not updating the utilization hours. I'm pretty sure that's not how you use the AddTime function, it doesn't seem correct to me anyway. I couldn't really find anything about using it with variables versus actual times. As for the route itself, it is being inserted into the table fine, no problems there.

Edited January 28, 2013 by Unknown98

[PFMaBiSmAd]

You don't have to select data, modify it, then update the column with the new value. All you need to do is execute the UPDATE query with the calculation in it. The following SINGLE query should work -

 

UPDATE aircraft SET utilization = ADDTIME(utilization,?) WHERE registration = ?

 

The 1st parameter is the $block_time, the 2nd parameter is the $equipment.

 

However, for what you are doing, you should treat the utilization an accounting ledger, where you add a row to a table with the registration number, the block_time, and things like the date/time of the trip, who entered the data, ... You would then just sum the block_times for any registration number to get the total utilization or the utilization between any two dates... This will keep a record of the entries and will prevent things like a duplicate update from messing up the data.

[PFMaBiSmAd]

Here's an issue with your present scheme. The TIME data type is limited to 838:59:59. If you have a need to store or calculate values greater than that, you should instead store the time in either the number of minutes or if you have the need for finer resolution, in the number of seconds, in an integer data type large enough to hold the maximum value you will ever need and perform the math in terms of minutes (or seconds) and convert the result to hours:minutes (or hours:minutes:seconds) when you display the value.

Edited January 28, 2013 by PFMaBiSmAd

[Unknown98]

You don't have to select data, modify it, then update the column with the new value. All you need to do is execute the UPDATE query with the calculation in it. The following SINGLE query should work -

 

UPDATE aircraft SET utilization = ADDTIME(utilization,?) WHERE registration = ?

 

The 1st parameter is the $block_time, the 2nd parameter is the $equipment.

 

However, for what you are doing, you should treat the utilization an accounting ledger, where you add a row to a table with the registration number, the block_time, and things like the date/time of the trip, who entered the data, ... You would then just sum the block_times for any registration number to get the total utilization or the utilization between any two dates... This will keep a record of the entries and will prevent things like a duplicate update from messing up the data.

 

Thank you, that works like a charm! As for treating the utilization as an accounting ledger, that makes sense and I'll look into doing that. I should be the only one who is entering data, however it would still be good to do that.

 

Here's an issue with your present scheme. The TIME data type is limited to 838:59:59. If you have a need to store or calculate values greater than that, you should instead store the time in either the number of minutes or if you have the need for finer resolution, in the number of seconds, in an integer data type large enough to hold the maximum value you will ever need and perform the math in terms of minutes (or seconds) and convert the result to hours:minutes (or hours:minutes:seconds) when you display the value.

 

Utilization would never go above 24 hours (or possibly 168 hours if I decide to change it to a per week calculation), so I don't think that would be a problem.