How to display time countdown using PHP and MySQL

Question

How do I get a time countdown using PHP?

I want to display something like 3Days 4Hours. I have a date field coming from MySQL table and want to calculate it with today's date. As of now I have only date and not the time stored in the database, but eventually I will. So at that time, I might show as 3Days 4Hours 10Minutes 43seconds.

This is what I tried but am getting some wrong answer:

$datetime1 = new DateTime($starton);//$starton - date stored in db
$datetime2 = new DateTime(date());
$interval = $datetime1->diff($datetime2);
echo $interval->format('%d days);

I am confused if this works based of server time or the zone where the user is coming from. Please guide me. When I have the time field, I guess I might need jQuery to show the seconds live and so the minutes too.

Answer 1

IMHO when you are thinking about comparing time, you automatically start talking Unix time. Essentially, Unix time is a count in seconds that always increments. When you have 2 Unix timestamps, then you can use simple arithmetic to figure the difference and then translate the difference into human readable form.

Note that Unix timestamps are common in almost all programming languages, so you can choose whether to do this in PHP, MySQL or JavaScript and it could all turn out the same way. I'll show you the PHP version.

So you want to do this:

1. Find the Unix timestamp for your target event
2. Store that in the MySQL database
3. Retrieve from the database
4. Get current Unix timestamp
5. Subtract to get difference
6. Multiply/divide to get human readable format

The PHP code:

//Unix timestamp to Dec. 21, 2012 (midnight)
$unix_time = mktime(0,0,0,12,21,2012); 
//Connect to MySQL
//"INSERT INTO table (target_timestamp) VALUES ($unix_time);"


//----- user goes to page -----
//Connect to MySQL
$sql = "SELECT target_timestamp FROM table WHERE foo = bar";
$unix_time = do_query($sql);//do_query not defined, assume it gets the timestamp
$current_time = time();
$diff = $unix_time - $current_time
//$diff should be positive and not 0
if( 1 > $diff ){
   exit('Target Event Already Passed (or is passing this very instant)');
} else {
   $w = $diff / 86400 / 7;
   $d = $diff / 86400 % 7;
   $h = $diff / 3600 % 24;
   $m = $diff / 60 % 60; 
   $s = $diff % 60;

   return "{$w} weeks, {$d} days, {$h} hours, {$m} minutes and {$s} secs away!"
}

Answer 2

PHP will use the timezone set by the server, which can be manually set in your script using date_default_timezone_set(). You can do this a couple ways:

You can actually use your MySQL query to do the calculation for you. See the TimeDiff() function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff. Then, you can use TimeFormat() to format it as how you want it.

Alternatively you can do it through PHP. You can either convert to unix epoch -> format as a date. The other option would be to start with two DateTime objects as you have done. Try:

$datetime1 = new DateTime($starton);
$datetime2 = new DateTime();
$interval = $datetime1->diff($datetime2);
echo $interval->format('%d days);

Or procedurally:

$datetime1 = date_create($starton);
$datetime2 = date();
$interval = date_diff($datetime1,$datetime2);
echo $interval->format('%d days');

Also, if you want to display the counter as a real-time updating counter; you'll probably have to hand this over to a Javascript function later.

Answer 3

Since you mentioned jQuery, it is possible to do it just by JavaScript if that's ok with you.

jQuery countdown plugin and working example: http://keith-wood.name/countdown.html

Will save you making calls to your db also.

Answer 4

Not 100% answering your question, but a manual way to do it would be this:

$start = strtotime($dbTime);
$now = time();
$differenceInSeconds = $start - $now;

From there you just need to format it, and you can find an algorithm in this answer.

Answer 5

Well, I think if you're calling date() on the server, you're going to get the server date and time zone. You can force the timezone with the function date_default_timezone_set([TIMEZONE]);, though, if that's a concern.

If I were doing this, and wanted a second-by-second countdown, I'd probably pass the calculated interval to javascript and let jQuery do the formatting, particularly if you want an active counter.

For that matter, you could offload the whole thing to javascript, and pass it the two dates, and use date1.getTime() - date2.getTime() to get the time difference in milliseconds, and do the math yourself in javascript (this is cribbed from this answer)

That's probably how I would do it, if I were altering it in JS anyway.

Answer 6

This is a solution that some frameworks (like Ruby on Rails) has tackled, PHP doesn't appear to be one of them, I think what you are looking for is something like:

http://www.phpro.org/examples/Convert-Seconds-To-Hours-Minutes-Seconds-Words.html