Date difference in PHP to update the mysql db

Question

I am trying to log the time difference in a mysql-field and when I try on screen the values are correct. After submitting the field to the database the field is empty. Where do I go wrong? The field is declared in mysql as timestamp and format current_timestamp. The code:

$strStart = date("Y-m-d H:i:s");
sleep(5);
$strEnd   = date("Y-m-d H:i:s");
$dteStart = new DateTime($strStart);
$dteEnd   = new DateTime($strEnd);
$dteDiff  = $dteStart->diff($dteEnd);
//$calc = $dteDiff->format("YYYY-%M-%D %H:%I:%S");
$calc = $dteDiff->format("%Y%Y-%M-%D %H:%I:%S");
echo "Difference: " . $calc;

I am submitting the $calc to the db.

You are not putting anything in a database in the code you have shown. And what is the result of your `echo`, is that what you need? — jeroen, Jan 15 '18 at 14:01
A difference between two timestamps is not a date/time.... dates/times represent a fixed point in time, not a period of time or a duration of time. — Mark Baker, Jan 15 '18 at 14:04
It is a timestamp field and my code for updating is: $conn->query("INSERT INTO refTrackLogs (refTrackId, refPointId, name, floorId, refType, latitude, longitude, logtime) VALUES ('$data[id]', $bpoint, '$b[label]', '$b[floor]', 'calculated', '$b[latitude]', '$b[longitude]', '$calc')"); — Popeye, Jan 15 '18 at 14:22
Yes, the echo part works with 5 secs difference: 0000-00-00 00:00:05 and this is what I want the time to be. — Popeye, Jan 15 '18 at 14:26

score 1 · Accepted Answer · answered Jan 15 '18 at 19:32

As MySQL dates have a minimum value of '1000-01-01 00:00:00', you cannot store this value ('0000-00-00 00:00:05 for 5 seconds) as a date/time value.... nor should you, because it isn't a date/time..... date/time is a fixed point in time, not an interval, which is the period between 2 date/times. There is no specific datatype for a date interval.

I would suggest storing the actual date/time value rather than the interval, and only calculating the interval when you need to do so. Alternatively, if you must store the interval itself, then store a number of seconds as a float rather than trying to force it into a formatted an inappropriate datatype; so store 5.0 seconds instead.

Date difference in PHP to update the mysql db

1 Answers1