Difference between two Datetimes

[the182guy]

Hi all,

 

I have two datetimes in the format of YYYY-MM-DD HH:MM:SS, I want to find out how much time there is between them.

 

It's for an auction script, so the two datetimes I have are the start_datetime and end_datetime. I want to be able to say "There is 2 Days 3 Hours and 28 Minutes left"

 

I don't mind doing this with a MySQL function if there is one, or through ordinary PHP.

 

Any ideas?

 

Thanks all

[beyzad]

Hi

 

Try use mktime() and date() functions

[GingerRobot]

If you want to be that specific with the time remaining, you're best bet will be to convert to a unix timestamp (you can use the UNIX_TIMESTAMP() MySQL function for this) and then do some maths. Basically, you're going to end up an integer which is the difference between the two dates in seconds. You'll then need to convert seconds into more reasonable units.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

[the182guy]

Ok I see. So this function takes two datetimes and returns the difference expressed as a time.

 

But how can I format the result of this function into human readbale form?

 

Query:

SELECT TIMEDIFF('2000:01:01 00:00:00',
                         '2000:01:01 00:00:00.000001');

 

Result:

-00:00:00.000001

 

I have tried using TIME_FORMAT() which can produce "52 Hours 28 Mins" from the above result, but how could I convert this to "2 Days 4 Hours 25 Mins"? I don't think TIME_FORMAT() can handle days.

[GingerRobot]

Basically, you're going to end up an integer which is the difference between the two dates in seconds. You'll then need to convert seconds into more reasonable units.

 

You'll have to do it yourself. Is it really that hard to divide by 86400(24*60*60) to get the number of days? Use floor to round it down. Take that number of seconds off. Repeat for hours (though, obviously, divide by 3600) then minutes

[uniflare]

I've actually written a little method to handle just that sort of counter;

 

<?php

// Take a UNIX Timestamp and work out how long ago it was made.
Function TimeSince($stamp,$estamp=null,$short=false,$both=true,$seperator=" \r\n"){
	// $stamp = unix timestamp to check from.
	// $short = Use short time names: Min, Sec, Etc.
	// $both = true to return minutes and seconds, or false only for minutes. etc...
	// $seperator = only needed when returning both, this is what is between the minutes and seconds.
	// $estamp = End Stamp to work out the difference. Uses current time by default.

	// If no end stamp provided, Use the current time.
	$dTime = ($estamp == null)? time() : $estamp;

	// This if is to make sure that $dTime is always higher than $stamp.
	if($dTime < $stamp){
		$temp = $dTime;
		$dTime = $stamp;
		$stamp = $temp;
		unset($temp);
	}

	// $worked is a flag to tell the loop below when to stop.
	$worked = false;

	// These are the variables that will hold the end result.
	$rDays = 0;
	$rHours = 0;
	$rMinutes = 0;
	$rSeconds = 0;

	// Time Since in Seconds (this var never changes, its only used in calcs)
	$TimeSince = $dTime-$stamp;

	// Just make sure there is at least 1 second.
	if($TimeSince == 0){ $TimeSince = 1; }

	// Time in seconds since stamp was made.
	$Remainder = $TimeSince;

	// These variables hold many many seconds for a given period of time.
	$nDays = (60 * 60) * 24; 	// Days in seconds
	$nHours = (60 * 60); 		// Hours in Seconds
	$nMinutes = 60;			// Minutes in Seconds

	// Get how many is in it
	$lastnumber = $Remainder / $nDays;

	// This is the cool part , this is where we go through each time length to see how many times it fits in "time since", we go through smaller and smaller time periods until we get to seconds.

	// You could quite easily add weeks.

	if($lastnumber >= 1){
		// Get the whole number
		$rDays = floor($lastnumber);

		// if it did not divide exactly, work out much much is left.
		if(1 != $lastnumber){
			$rHours = floor(($TimeSince - ($rDays * $nDays)) / $nHours);
		}
	}else{
		// Get how many is in it
		$lastnumber = $Remainder / $nHours;

		// If there is at least 1
		if($lastnumber >= 1){
			$rHours = floor($lastnumber);
			if(1 != $lastnumber){
				$rMinutes = floor(($TimeSince - ($rHours * $nHours)) / $nMinutes);
			}
		}else{
			// Get how many is in it
			$lastnumber = $Remainder / $nMinutes;

			if($lastnumber >= 1){
				$rMinutes = floor($lastnumber);
				if(1 != $lastnumber){
					$rSeconds = $TimeSince - ($rMinutes * $nMinutes);
				}
			}else{
				$rSeconds = $Remainder;
			}
		}
	}

	if($short == true){
		$txtMinutes = "Min";
		$txtSeconds = "Sec";
		$txtHours = "Hrs";
	}else{
		$txtMinutes = "Minutes";
		$txtSeconds = "Seconds";
		$txtHours = "Hours";
	}

	if($rDays != 0){
		$endstr = ($both == true && $rHours > 0)? $seperator.$rHours." ".$txtHours : "";
		$timesince = $rDays." Days".$endstr;
	}elseif($rHours != 0){
		$endstr = ($both == true && $rMinutes > 0)? $seperator.$rMinutes." ".$txtMinutes : "";
		$timesince = $rHours." ".$txtHours.$endstr;
	}elseif($rMinutes != 0){
		$endstr = ($both == true && $rSeconds > 0)? $seperator.$rSeconds." ".$txtSeconds : "";
		$timesince = $rMinutes." ".$txtMinutes.$endstr;
	}elseif($rMinutes == 0){
		$timesince = $Remainder." ".$txtSeconds;
	}else{
		$timesince = '<font color="#red">ERROR("Never see this")</font>';
	}

	return $timesince;
}
?>

 

 

Usage:

string TimeSince (  timestamp $Start_Stamp,  timestamp $End_Stamp,  bool $Short_Names,  bool $Show_Both_Names,  string $Name_Seperator);

 

Parameters:

• Start_Stamp
  
  • This is the first timestamp you pass, this should be the lower one.

   

  [*]End_Stamp

  • This is the second timestamp to match against, this should be the higher number. By default, this will be the current timestamp given by time().

   

  [*]Short_Names

  • As NULL, or by Default, TimeSince() will display full names, eg; 12 Minutes. As True; TimeSince() will show abbreviated names, eg; 12 Min.

   

  [*]Show_Both_Names

  • As NULL, or by Default, TimeSince() will show both names, eg; 12 Minutes, and 24 Seconds. As True; TimeSince() will show only one name, eg; 12 Minutes.

   

  [*]Name_Seperator

  • As NULL, or by Default, TimeSince() will concatenate the names with a space, followed by a windows carriage return, eg; " \r\n" - 12 Min. \r\n24 Sec.

   

 

Examples:

<?php
echo(TimeSince(1233314664, 1233215664, true, true, " - ")."<br />"); // ECHOS: 1 Days - 3 Hours
echo(TimeSince(1233314664, 1233315664, false, true, " & ")."<br />"); // ECHOS: 16 Minutes & 40 Seconds
echo(TimeSince(1233314664, 1233324664, true, false, " and ")."<br />"); // ECHOS: 2 Hrs
echo(TimeSince(1233314664, 1233314564, false, false, " <> ")."<br />"); // ECHOS: 1 Minutes
?>

 

Notes:

I know this function looks a bit shabby but it's not slow . Maybe you could optimize it a little .

 

To get the current timestamp use time(). use date() to convert a timstamp to a readable date. To get a timestamp from a readable date, use strtotime().

 

Have fun with it .

 

 

[printf]

Something like...

 

// change 

start_datetime > to your column start time column name
end_datetime > to your column end time column name
time_table > to your table name
some_column > to the column name you are querying against
some_thing > to the value of column name you are querying against

// returns.. result array element

[time_left] = There is # Days # Hours and # minutes left

SELECT CONCAT('There is ', DAYOFYEAR(start_datetime)-DAYOFYEAR(end_datetime),' Days ', DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",SEC_TO_TIME(TIME_TO_SEC(start_datetime)-TIME_TO_SEC(end_datetime))),'%k Hours and %i minutes left')) AS time_left FROM time_table WHERE some_column = 'some_thing';

[the182guy]

Theres some great ideas! I will have a play around with the different ways.

 

Thanks all for the help on this, it is very much appreciated!

[uniflare]

the function ive created is fast, very fast, using so many functions is a little overkill i think. use microtime to see which script is faster.....