Difference between 2 datetime columns in phpmyadmin

[andrelemonius2]

Hi guys i have been trying to get the difference between 2 datetime columns stored in phpmyadmin database. the columns are createddate which has current_timestamp as it’s default value and waitingtime. The problem is when i execute my php query it returns a negative value. How can i correct this to show the correct time difference.

Here is my code:

$mydata=mysqli_query($connection,"SELECT log,TIMESTAMPDIFF(hour,createddate, waitingtime) AS TimeDifference
FROM memberloaninfo");
if($mydata == FALSE) { 
        die(mysqli_error($connection)); 
	}else {
	
	echo "	<div class= datagrid>
<table>
<thead>
	<tr><th>timedifference</th><th>Log</th></tr>
</thead>
</div>";



	while($records= mysqli_fetch_array ($mydata)){ 
	echo "<div tbody>";
	echo "<tr>";
	echo "<td>" .$records ['TimeDifference']."</td>";
	echo "<td>" .$records ['log']."</td>";
	echo "</div></tbody>\n";
	}
	echo "</table>";
	
 	}

[coderboy]

The query returns a negative number because TIMESTAMPDIFF returns a value in integer.

In your case, it returns the hour difference between the two dates. If the returned value is 5, the difference between the two dates are 5 hours.

Here’s a quick rundown of the functions you can use in MySQL:
###TIMESTAMPDIFF()
TIMESTAMPDIFF() returns the difference in the given unit.

TIMESTAMPDIFF(year, createddate, waitingtime)    # returns years as integer
TIMESTAMPDIFF(month, createddate, waitingtime)   # returns months as integer
TIMESTAMPDIFF(week, createddate, waitingtime)    # returns weeks as integer
TIMESTAMPDIFF(day, createddate, waitingtime)     # returns days as integer
TIMESTAMPDIFF(hour, createddate, waitingtime)     # returns hours as integer
TIMESTAMPDIFF(minute, createddate, waitingtime)     # returns minutes as integer
TIMESTAMPDIFF(second, createddate, orders.arrival_time)  # returns seconds as integer

###DATEDIFF()
DATEDIFF() returns the difference in days.

DATEDIFF(createddate, waitingtime)     # returns days as integer

###TIMEDIFF()
TIMEDIFF() returns the difference in time format.

TIMEDIFF(createddate, waitingtime)     # returns time HH:MM:SS.ssss

Note: You can always use the combination of these functions to return your desired output.

I hope this helps.

[andrelemonius2]

@coderboy

TIMEDIFF(day, createddate, waitingtime)

I keep getting this error when i try using Timediff or datediff
Incorrect parameter count in the call to native function ‘TIMEDIFF’

[andrelemonius2]

I think it’s because the one of the datetime field which has been set to current timestamp post results in army time while the other has results in regular time.
So created time is posted as
2017-02-06 22:03:42
and waitingtime as
2017-02-06 10:16:22
Now i’m clueless on how to fix this.

[coderboy]

Edit: Sorry, I’ve fixed the above code, the TIMEDIFF() and DATEDIFF() takes only two parameters. In your case, it’s createddate and waitingtime.

And, it’s always better to save the time in UTC, so that it also saves the time zone. If you’re facing the time zone issue, I guess you can only revert and fix them.

But, as long as you are using similar code and it runs on the server side, it always takes the same time zone.

Try the following code for your program and let me know if it works. (This might not be the best way to do this though)

##sql Command

SELECT log,
    TIMESTAMPDIFF(year, createddate, waitingtime) AS YearDiff,
    TIMESTAMPDIFF(month, createddate, waitingtime) AS MonthDiff,
    TIMESTAMPDIFF(day, createddate, waitingtime) AS DayDiff,
    TIMEDIFF(createddate, waitingtime) AS TimeDiff
FROM memberloaninfo;

##PHP Code

echo "<tbody>";
while($records= mysqli_fetch_array ($mydata)) { 
    echo "<tr>";

    echo "<td>";
    // Print the difference
    echo $records['YearDiff'] . ' years ';
    echo $records['MonthDiff'] . ' months ';
    echo $records['DayDiff'] . ' days ';
    echo $records['TimeDiff'];
    echo "</td>";

    echo "<td>" .$records ['log']."</td>";
    echo "</tr>";
}
echo "</tbody>";

[andrelemonius2]

Ok i am still getting the time difference as 11hrs 47 mins instead of 13mins 40 sec. Not sure whats happening there.
Maybe if i can find a way for createddate to be entered into the database as 12hr am/pm time would be great or by changing waitingtime as 24hr army time.
Either way would be good but not seeing a way in myphpadmin for this to happen.

This is my table setup in myphpadmin for both time fields

Name Type Null Default
createddate datetime No CURRENT_TIMESTAMP
waitingtime datetime Yes NULL

[droopsnoot]

@andrelemonius2

Ok i am still getting the time difference as 11hrs 47 mins instead of 13mins 40 sec.

But the difference between the two times you posted in #4, 22:03:42 and 10:16:22, is 11h 47m, it’s not 13 minutes. You’re either going to have to specify AM/PM in your time, or use 24h clock instead. A datetime column definition in MySQL uses 24h clock.

[coderboy]

Like @droopsnoot said, DATETIME uses a 24 hour format and your database schema looks fine.

How are you inserting the waitingtime into the database? Can you post the insertion code? That might give us some ideas.

I feel you’re using PHP date in a wrong format.

[andrelemonius2]

Yeah i was definitely using the wrong format didn’t even notice until i went thru the code. Was using date(‘y-m-d h:i:s’); instead of date(‘y-m-d H:i:s’). Thank you for your help.

[coderboy]

Great! Glad it’s fixed.