Getting the difference between 2 DATETIME fields

[pocobueno1388]

I have a DATETIME filed in the database that I would like to subtract it from the current time and get the remaining time.

 

Here is the row in the database:

2007-03-13 05:37:00

 

I would like to do something like this:

 

2007-03-13 05:37:00 - CURRENT TIME/DATE = REMAINING TIME

 

So I am basically trying to get how long it will take for the current date/time to reach 2007-03-13 05:37:00  date/time.

 

I would like to display it like this:

2 days 5 hours 45 minutes left

 

Any help would be greatly appreciated =] Thanks.

 

 

 

[per1os]

PHP is probably your better bet for date modification.

 

I would do this

 

<?php
$resArr = mysql_fetch_assoc(mysql_query("Grab date field out of DB"));

list($year, $month, $day, $time) = split($resArr['datefield'], "-");
list($hours, $minutes, $seconds) = split($time, ":");

//int mktime ( [int $hour [, int $minute [, int $second [, int $month [, int $day [, int $year [, int $is_dst]]]]]]] )
$timestamp = mktime($hours, $minutes, $seconds, $month, $day, $year));

$timeLeft = date('formatHere', $timestamp - time());

print "The time left is " . $timeLeft . "!";
?>

 

I did not test this, but should work Let me know if not.

 

--FrosT

[pocobueno1388]

After messing with your code a little, this is what it prints to the screen:

 

The time left is 1970-01-03!

 

I have no idea how to get that into the format of:

 

The time left is 2 hrs 3 minutes 2 seconds

 

Here is the code:

 

<?php

$query = mysql_query("SELECT start, end FROM auctions WHERE auctionID='5'");

list ($start, $end) = mysql_fetch_row($query);
list($t_date, $t_time) = split(' ', $end);
list($year, $month, $day) = split("-", $t_date);
list($hours, $minutes, $seconds) = split(":", $t_time);

$left = mktime($hours, $minutes, $seconds, $month, $day, $year);

$timeLeft = date('Y-m-d', $left - time());

print "The time left is " . $timeLeft . "!";

?>

 

The variable $end holds the date from the database we are working with.

[pocobueno1388]

=/ I really need help with this one.

[per1os]

<?php
#list ($start, $end) = mysql_fetch_row($query); // I have never seen this done I would do it like below

$row = mysql_fetch_assoc($query);
$end = $row['end'];
$start = $row['start'];

?>

 

Another thing to try is print out the variable names for debugging making sure that everything is display right.

 

--FrosT

[pocobueno1388]

I have already tested every single line of that script and made sure everything printed out correctly =D

 

If you want to know where I got how to use the list function like that, read over the page in the manuel on it. php.net/list

[pocobueno1388]

Anyone else want to give it a go?

[chronister]

here is a function I found online that I use to convert seconds since epoch into a format like you want. Just feed it a unix timestamp and it will return the hours minutes and seconds. With a little modification you could probably add the days part as well.

 

function convert_seconds ($sec, $padHours = false) 
  {

    // holds formatted string
    $hms = "";
    
    // there are 3600 seconds in an hour, so if we
    // divide total seconds by 3600 and throw away
    // the remainder, we've got the number of hours
    $hours = intval(intval($sec) / 3600); 

    // add to $hms, with a leading 0 if asked for
    $hms .= ($padHours) 
          ? str_pad($hours, 2, "0", STR_PAD_LEFT). ':'
          : $hours. ':';
     
    // dividing the total seconds by 60 will give us
    // the number of minutes, but we're interested in 
    // minutes past the hour: to get that, we need to 
    // divide by 60 again and keep the remainder
    $minutes = intval(($sec / 60) % 60); 

    // then add to $hms (with a leading 0 if needed)
    $hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). ':';

    // seconds are simple - just divide the total
    // seconds by 60 and keep the remainder
    $seconds = intval($sec % 60); 

    // add to $hms, again with a leading 0 if needed
    $hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT);

    // done!
    return $hms;
    
  }
$time=36950;
  
echo convert_seconds($time);

 

Returns 10:15:50

[pocobueno1388]

That code converts seconds to the h:m:s format...that isn't exactly what I am looking for.

[pocobueno1388]

Okay, I think I found a function that will work...but now I feel really stupid because I can't get the stupid function to work -shakes head-

 

Here is the function code:

 

<?php

function dateDiff($date, $date2 = 0)
{
    if(!$date2) {
        $date2 = mktime();
    }

    $date_diff = array('seconds'  => '',
                       'minutes'  => '',
                       'hours'    => '',
                       'days'     => '',
                       'weeks'    => '',
                       
                       'tseconds' => '',
                       'tminutes' => '',
                       'thours'   => '',
                       'tdays'    => '',
                       'tdays'    => '');

    ////////////////////
    
    if($date2 > $date) {
        $tmp = $date2 - $date;
    }

    else {
        $tmp = $date - $date2;
    }

    $seconds = $tmp;

    // Relative ////////
    $date_diff['weeks'] = floor($tmp/604800);
    $tmp -= $date_diff['weeks'] * 604800;

    $date_diff['days'] = floor($tmp/86400);
    $tmp -= $date_diff['days'] * 86400;

    $date_diff['hours'] = floor($tmp/3600);
    $tmp -= $date_diff['hours'] * 3600;

    $date_diff['minutes'] = floor($tmp/60);
    $tmp -= $date_diff['minutes'] * 60;

    $date_diff['seconds'] = $tmp;
    
    // Total ///////////
    $date_diff['tweeks'] = floor($seconds/604800);
    $date_diff['tdays'] = floor($seconds/86400);
    $date_diff['thours'] = floor($seconds/3600);
    $date_diff['tminutes'] = floor($seconds/60);
    $date_diff['tseconds'] = $seconds;

    return $date_diff;
    
    echo $date_diff[seconds];
    
}

?>

 

This is how I tried to call the function:

 

dateDiff('2007-03-10 17:37:00', '2007-03-13 05:37:00');

 

I get a blank screen 0_0 ... I don't know how to display any of the stuff.

[chronister]

It looks like the function works off of the unix timestamp.. try passing a timestamp into it.

 

I got the function to return

 

54

29

21

1

0

163794

2729

45

1

0

 

by doing this at the end.

 

 

foreach(dateDiff(1173762000) as $v){
echo $v.'<br>';
}

as the dateDiff() returns an array.

 

I know this is not the solution, but I hope it helps a little.

[kenrbnsn]

Here's a solution:

 

<?php
$test_time = strtotime('2007-03-17 16:08');
$today = time();
$time_diff = $test_time - $today;
$diff_days = floor($time_diff / 86400);
$diff_hours = floor($time_diff / 3600) - ($diff_days * 24);
$diff_mins = floor($time_diff % 3600)/60;
echo sprintf("<br>%d days %2d hours %2d minutes left<br>",$diff_days, $diff_hours,$diff_mins);
?>

 

Modify this to fit your needs.

 

Ken

 

[pocobueno1388]

Ken - That worked perfectly! Now I am struggling to get the seconds out of it =/

 

I tried this:

$diff_secs = floor($diff_mins * 60)/$diff_mins;

 

It doesn't seem to work though. I have googled tons and still can't figure it out.

[tippy_102]

I use this....

$sql = 'SELECT title, url, eventdate, unix_timestamp(eventdate) - unix_timestamp(NOW()) AS distance FROM events WHERE active = 1 ORDER BY distance ASC'; 
$result = mysql_query($sql) or die ("couldn't execute query");
$num_rows = mysql_num_rows($result);
        	
if ($num_rows == 0)
{ echo "<p>No events</p>"; }

else
{
while ($row = mysql_fetch_array($result))
{
extract($row);

if ($distance >= 0)
{
if (($distance / 86400) >= 1)
{
$distance = floor($distance / 86400);
echo "<p><b>$distance</b> days until <a href='$url'>$title</a></p>\n";
}
if (($distance / 3600) >= 1)
{
$hours = floor($distance / 3600);
$mins = (($distance / 3600) - $hours);
$mins = floor($mins * 60);
echo "<p><b>$hours</b> hours <b>$mins</b> minutes until <a href='$url'>$title</a></p>\n";
}
} 
}
} 

To get the seconds, just add another "if distance..."

[pocobueno1388]

tippy_102 - The thing is I don't know the formula from Kens code to get the seconds.