the182guy Posted January 28, 2009 Share Posted January 28, 2009 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 Quote Link to comment Share on other sites More sharing options...
beyzad Posted January 28, 2009 Share Posted January 28, 2009 Hi Try use mktime() and date() functions Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 28, 2009 Share Posted January 28, 2009 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. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 28, 2009 Share Posted January 28, 2009 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff Quote Link to comment Share on other sites More sharing options...
the182guy Posted January 30, 2009 Author Share Posted January 30, 2009 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. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted January 30, 2009 Share Posted January 30, 2009 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 Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 30, 2009 Share Posted January 30, 2009 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_StampThis 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 . Quote Link to comment Share on other sites More sharing options...
printf Posted January 30, 2009 Share Posted January 30, 2009 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'; Quote Link to comment Share on other sites More sharing options...
the182guy Posted January 30, 2009 Author Share Posted January 30, 2009 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! Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 30, 2009 Share Posted January 30, 2009 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..... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.