Jopeth Deza Jopeth Deza - 2 months ago 5
MySQL Question

TIMEDIFF FUNCTION doesn't work

Query Ouput

From the Output, you can see that I can select the

MIN(TIME(Time))
but when I use it in
TIMEDIFF FUNCTION
it reads the
MIN(TIME(Time))
as 0 and I get a result of -07:00:00. I want to get the difference of
MIN(TIME(Time))
and 07:00:00 for me to determine the number of hours an employee is late. Any suggestions on how to fix this? All responses are much appreciated. Sorry for the bad english.

Here's the code:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>

<?php
$con=mysqli_connect("localhost","root","","september-system");


// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = "SELECT User_ID, MIN(TIME(Time)) as time, DATE(Time) as day, TIMEDIFF('MIN(TIME(Time))', '07:00:00')as tdiff FROM records GROUP BY User_ID, day ORDER BY User_ID, day;";



if (mysqli_multi_query($con,$sql))
{
do
{
// Store first result set
if ($result=mysqli_store_result($con)) {
echo "<table><tr><th>USER ID</th><th>TIME</th><th>DATE</th><th>ABSENCES</th></tr>";
// output data of each row
while ($row=mysqli_fetch_row($result))
{
//echo var_dump($row);
echo "<tr><td>" . $row[0]. "</td><td>" . $row[1]. "</td><td>" . $row[2]. "</td><td>" . $row[3]. "</td></tr>";
}
// Free result set
mysqli_free_result($result);
}
}
while(mysqli_more_results($con) && mysqli_next_result($con));
}

mysqli_close($con);
?>

Answer

I think it is better to remove the single quote from the first parameter lets try , i'm not sure

 TIMEDIFF(MIN(TIME(Time)), '07:00:00')as tdiff 
Comments