I'm trying to display on a webpage the number of days, hours, minutes and seconds remaining until a certain time stored as in a MySQL database (datetime data type).

I've played around and so far I have:

SELECT TIMEDIFF(fldEndTime,NOW()) AS TimeLeft

in my query which returns the number of hours, minutes and seconds remaining (e.g. 167:54:00) and from this I can divide it by 24 to work out the number of days:

$DaysLeft = intval($TimeLeft / 24);
$HoursLeft = $TimeLeft - ($DaysLeft * 24);
$TimeRemaining = $DaysLeft." days ".$HoursLeft." hours ";

But I don't know how I can get the minutes and seconds out. As $TimeLeft is not a proper date/time but a numbe of hours, minutes and seconds I can't use date("i", $TimeLeft) to extract the minutes.

Any ideas? Not sure if this is the best way to do it or if I could do it easier in the SQL too?

Thanks.

    How is your time stored in database?

      It's in a datetime field stored in the format 2007-12-30 13:58:51

        You could have the query calculate the total seconds with:

        SELECT UNIX_TIMESTAMP(fldEndTime) - UNIX_TIMESTAMP() AS TimeLeft . . .

        Then you could get the days, etc., as:

        $days = floor($row['TimeLeft'] / (60 * 60 * 24));
$remainder = $row['TimeLeft'] % (60 * 60 * 24);
$hours = floor($remainder / (60 * 60));
$remainder = $remainder % (60 * 60);
$minutes = floor($remainder / 60);
$seconds = $remainder % 60;

        Note, however, that this may be off by an hour if the timespan happens to cross a changeover between standard and daylight time. If that is an important concern, you could take a look at this function I came up with some time ago.

          That works perfectly! Thanks 🙂

            Write a Reply...