Output format while building mysql php countdown function

Question

I've been struggling with this for quite sometime, after few long hours spend on forums I came up with this code:

SELECT *, TIMESTAMPDIFF(SECOND,NOW(),`pay_date`) AS `expire` FROM `users`

pay_date is datetime field which in the moment of tries had setup (current time + 7 days) so NOW() is showing 03-09-2013 23:30:20; pay_date is showing 10-09-2013 23:30:20. I'am using this code to extract my countdown:

echo $date['expire'];

It is working, its properly giving me amount of seconds left, what i dont know is how to make it to say something like: 2 years, 10 months, 20 days, 5 hours, 30 minutes, 46 seconds left.

ive tried in few ways for instance date('d-m-Y H:i:s', $date['expire']) but its giving me as output something like 1790-01-01, strtotime isnt also working, i dont know how to make it to work in the way i described above.

Answer 1

You can use DateTime class for time calculation:

Code:

$start = new DateTime;
$end = clone $start;
$end->modify("+{$date['expire']} seconds");
$diff = $start->diff($end);
print_r($diff);

Output:

DateInterval Object
(
    [y] => 0
    [m] => 0
    [d] => 0
    [h] => 0
    [i] => 4
    [s] => 29
    [invert] => 0
    [days] => 0
)

As you can see in output, you have all the info you need. To access it, just use $diff->i for minutes, $diff->s for seconds etc. or use DateInterval::format for formating interval.

Answer 2

I strongly suggest using PHP's DateTime object instead of strtotime.

http://www.php.net/manual/en/book.datetime.php

You can get the date difference using Datetime::diff method.

Also, you can style output however you want:

$datetime->diff()->format('%y Years, %m Months, %d Days')

Answer 3

sorry guys but none of these works, this stuff time_elapsed_string($ptime) no matter what i do, i can even put there date manually and it still returns 44 years ...

new so cold feature DateTime class isnt accurate, its so far from true while calculating, there is more professional and working solution for this, i will wait until someone present solution that actually works.

Again, i believe that nobody wants amateur function that sometimes works sometimes not and is badly written (months always 30 days ? no thanks). While reading php manual you will qucikly notice that there is a lot of proof's that datetime class isnt working, even on stackoverflow ive seen that kind of posts.

This stuff is good:

select *,Concat(hour(diffTime),' hours ',minute(diffTime),' Minutes ',second(diffTime),' seconds remaining') as timetaken
from (select *,sec_to_time(UNIX_TIMESTAMP(now())- UNIX_TIMESTAMP( ttable.pay_date))
as diffTime from users ttable )
as temptable1

i've found it on different topic also related to date calculating, adapted it to my database and it works, but unfortunetely it shows only Hours Minutes and Seconds, when its like few days it shows 800 hours for example, i dont understand how it works exactly, but i believe it could be modified to show also years months and days perhaps someone will know how to do it