使用TIMESTAMPDIFF PHP MYSQL同时更新结束时间和持续时间

Question

I want to update 'duration' and 'end_time' field in same query/action. 'end_time' field filled current time, 'duration' field filled from different minute between 'start' and 'end_time' field. when i execute this update, the result in 'duration' field is 0. How to get end_time and duration in same time.

this is the php code :

<?php
include("koneksi.php");
$id     = $_GET['id'];
$start  = gmdate("Y-m-d H:i:s", time()+60*60*7);
$end_time   = gmdate("Y-m-d H:i:s", time()+60*60*7);
$duration  = $_POST['duration'];


$query = "update billing set end_time='$end_time', duration = TIMESTAMPDIFF(MINUTE, '$start', '$end_time') where id='$id'";

$result = mysql_query($query);
if ($result){
echo '<script language="javascript">window.location = "../?p=los"</script>'; 
} ?>

Answer 1

Duration is being set to 0 because $start and $end_time are the same.

Consider your code:

$start  = gmdate("Y-m-d H:i:s", time()+60*60*7);
$end_time   = gmdate("Y-m-d H:i:s", time()+60*60*7);

$query = "... TIMESTAMPDIFF(MINUTE, '$start', '$end_time') ...";

Because $start == $end_time, the difference will always be 0.

You probably want to use the value of start_time already stored in the database, not the recently created php variable $start. Perhaps something like this:

$query = "
 update billing set 
 end_time='$end_time', 
 duration = TIMESTAMPDIFF(MINUTE, start_time, '$end_time') 
 where id='$id'
";

where start_time is a column in the database.

---

Note: mysql_* functions are deprecated, and you are susceptible to SQL injection. Consider using mysqli or pdo and utilize prepared statements.