mysql time function

rulian

is there a way that I can put a datetime field in my mysql table, refer to it in my page as a count down, for example, I set the time from one hour from now, I need to access the time and subtract now and the result should be the time remaining until the time expires, is there any way I can achieve this effect?

jordan

Rulian,

With your inspiration I wrote two little functions that will take a unix timestamp (you can get a unix timestamp in MySQL using the MySQL UNIX_TIMESTAMP() function) and the current time (or for that matter a second timestamp) and print out a string that says how many days, hours, minutes, and seconds until the ending time. The second function could be improved a lot, and I think the first has a bug when working with negative numbers (e.g. the end date has passed), but you get the idea. I hope it's useful:

// get_timeuntil() will return an array containing the number
// of days, hours, and seconds from $starttime to $endtime
function get_timeuntil($starttime,$endtime) {
$secs = $endtime - $starttime; // get the difference in seconds

$timeuntil[] = floor($secs/86400);	// the number of days

$secs %= 86400;
$timeuntil[] = floor($secs/3600);	// the number of hours

$secs %= 3600;
$timeuntil[] = floor($secs/60);	// the number of minutes

$timeuntil[] = $secs % 60;	// the number of seconds

return $timeuntil;	// return the array

}

// print_timeuntil() will print the values in the array returned
// by get_timeuntil in a pretty way, but could be improved a lot
function print_timeuntil($timeuntil) {
print(
(($timeuntil[0] != 0) ? "$timeuntil[0] day(s), " : "") .
(($timeuntil[1] != 0) ? "$timeuntil[1] hour(s), " : "") .
(($timeuntil[2] != 0) ? "$timeuntil[2] minute(s), " : "") .
"$timeuntil[3] second(s) remaining."
);
}

$now = time();

// EXAMPLE CODE showing the use of timeuntil() and print_timeuntil()
$endtime = mktime(12,0,0,5,15,2002); // a UNIX timestamp (noon on my next birthday)
$starttime = time(); // now

print_timeuntil(get_timeuntil($starttime,$endtime));