Subtract fetched datetime from current date time

Question

I need to calculate the time difference between the localDatetime and a Datetime fetched from a specific row in my database-table(csv) called ReportedDateAndTime. I searched a lots of solutions but it doesn't working for me. Displaying localtime & the fetched time from Database are working. But the time difference doesn't. Here is my code so far. Please help. Thanks in advance!

<?php

    include_once('connection.php');
    $sql="SELECT * FROM csv";
    $records=mysqli_query($conn, $sql);
    $date = new DateTime(null, new DateTimeZone('Asia/Colombo'));
    while ($csv=mysqli_fetch_assoc($records))
    {
    $exp = $csv['ReportedDateAndTime'];
    }
    $diff = $date->diff($exp);
    echo $diff->format('%H')

?>

score 1 · Answer 1 · answered Jun 29 '15 at 14:20

1

From the mysql doc

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

'2004-01-01 13:00:00'

SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

'2004-01-01 22:00:00'

https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

answered Jun 29 '15 at 14:20

Drew

24,851
10
43
78

Thanks for your help Drew Pierce. :) – Sugs Jun 29 '15 at 14:36
I have an issue displaying the elapsed time in each row now. all the rows' elapsed times display in one row now. Please help display the elapsed times in respective rows.Please check my coding here, [link] (http://stackoverflow.com/questions/31155746/how-to-send-emails-when-elapsed-time-reaches-specific-hours-automatically?noredirect=1#comment50324376_31155746)How to send emails when elapsed time reaches specific hours automatically? – Sugs Jul 01 '15 at 11:26
Will do. Could you green check mark this answer or any answer (with the green checkmark) if complete – Drew Jul 01 '15 at 13:32
Sure :). I will. Please help me to Display the elapsed time in respective rows. All the elapsed time display in every row. Hope you can help – Sugs Jul 01 '15 at 13:47
Hi.. I requested to take a look at my coding since this is my coding & the issue with my coding was displaying all the rows' elapsed time in each row instead of display the elapsed time in respective row. (stackoverflow.com/questions/31117682/… ) Its not working though I add a loop. Hope you can help me on that. Thanks in advance! – Sugs Jul 05 '15 at 05:06

score 1 · Answer 2 · answered Jun 29 '15 at 14:36

Thanks all. Fortunately I found an answer. :)

<?php
include 'connection.php';
date_default_timezone_set('Asia/Colombo');
$the_time = date('H:i');
echo $the_time." </br>";
$sql="SELECT ReportedDateAndTime FROM csv";
$result=$conn->query($sql);
while ($row = $result->fetch_assoc()) 
{
$new_time=$row['ReportedDateAndTime'];
echo "<br/>";
echo $new_time." ReportedDateAndTime</br>";
$datetime1 = new DateTime($the_time);
$datetime2 = new DateTime($new_time);
$interval = $datetime1->diff($datetime2);
$hours=$interval->format('%h');
$minutes= $interval->format('%i');
echo "<br/>";
echo $hours." Hours  ".$minutes."  Minutes</br></br>";
}
?>

Subtract fetched datetime from current date time

2 Answers2

Linked