Time left until a specified date

[paulbradley]

Using MySQL and php I need to be able to return the number of days, hours, mins and secs from the current date and time to a stored date and time in a table. Is there an easy way of doing this?

Many thanks in advance.

 

[r937]

select current_timestamp
     , stored_datetime
     , concat(
        case 
         when 
          unix_timestamp(stored_datetime)
         -unix_timestamp(current_timestamp) 
             > 86400
         then concat(''
                   , floor(
         (unix_timestamp(stored_datetime)
         -unix_timestamp(current_timestamp))
                 / 86400 )  
                   , ' days, ' )
         else '' end 
     , sec_to_time(    
         mod(
          unix_timestamp(stored_datetime)
         -unix_timestamp(current_timestamp)
                ,86400) 
                  )
            ) as diff
  from yourtable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[TonyGroves]

Unfortunately, that solution only works for date/times in the future, not in the past. You could use instead something like:
[tt]
SELECT
datetimefield,
secsdiff,
FLOOR(ABS(secsdiff)/86400) daysdiff,
SEC_TO_TIME(ABS(secsdiff)%86400) timediff
FROM
(
SELECT
datetimefield,
UNIX_TIMESTAMP(datetimefield)-UNIX_TIMESTAMP(NOW())
secsdiff
FROM tablename
)
s
[/tt]
where the sign of the secsdiff field indicates past/future. This works with MySQL 4.1 onwards.

 

[r937]

well, it's not exactly "unfortunate", tony, because that's actually what was asked



however, nice one on the plus-or-minus approach



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)