MYSQL - time left from now to a future date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dimi3_178
Forum Newbie
Posts: 1
Joined: Fri Aug 27, 2004 4:09 am

MYSQL - time left from now to a future date

Post by dimi3_178 »

I try to make a request such that it gives me the TIME LEFT FROM NOW TO A DATE IN THE FUTURE.
Example : TIME LEFT to 2004-08-30 12:00:00 : 3 days, 20 hours

Could anyone help ??
thanks a lot !
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

you may need to use a function like this to get a truly accurate answer, unfortunately, this funtion doesn't allow for hours, so you would have to add that yourself

Code: Select all

<?php 
/* 
    License:  do whatever you want with this code 
    
    Disclaimer:  This code works well on my system, but may not on yours.  Use 
    with circumspection and trepidation.  If this code blows up your system, 
    I recommend vituperation. 
*/ 



/* 
    function smoothdate simply takes a year, month, and a day, and 
    concatenates them in the form YYYYMMDD 
    
    the function date_difference uses this function 
*/ 

function smoothdate ($year, $month, $day) 
{ 
    return sprintf ('%04d', $year) . sprintf ('%02d', $month) . sprintf ('%02d', $day); 
} 


/* 
    function date_difference calculates the difference between two dates in 
    years, months, and days.  There is a ColdFusion funtion called, I 
    believe, date_diff() which performs a similar function. 
    
    It does not make use of 32-bit unix timestamps, so it will work for dates 
    outside the range 1970-01-01 through 2038-01-19.  This function works by 
    taking the earlier date finding the maximum number of times it can 
    increment the years, months, and days (in that order) before reaching 
    the second date.  The function does take yeap years into account, but does 
    not take into account the 10 days removed from the calendar (specifically 
    October 5 through October 14, 1582) by Pope Gregory to fix calendar drift. 
    
    As input, it requires two associative arrays of the form: 
    array (    'year' => year_value, 
            'month' => month_value. 
            'day' => day_value) 
    
    The first input array is the earlier date, the second the later date.  It 
    will check to see that the two dates are well-formed, and that the first 
    date is earlier than the second. 
    
    If the function can successfully calculate the difference, it will return 
    an array of the form: 
    array (    'years' => number_of_years_different, 
            'months' => number_of_months_different, 
            'days' => number_of_days_different) 
            
    If the function cannot calculate the difference, it will return FALSE. 
    
*/ 

function date_difference ($first, $second) 
{ 
    $month_lengths = array (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31); 

    $retval = FALSE; 

    if (    checkdate($first['month'], $first['day'], $first['year']) && 
            checkdate($second['month'], $second['day'], $second['year']) 
        ) 
    { 
        $start = smoothdate ($first['year'], $first['month'], $first['day']); 
        $target = smoothdate ($second['year'], $second['month'], $second['day']); 
                            
        if ($start <= $target) 
        { 
            $add_year = 0; 
            while (smoothdate ($first['year']+ 1, $first['month'], $first['day']) <= $target) 
            { 
                $add_year++; 
                $first['year']++; 
            } 
                                                                                                            
            $add_month = 0; 
            while (smoothdate ($first['year'], $first['month'] + 1, $first['day']) <= $target) 
            { 
                $add_month++; 
                $first['month']++; 
                
                if ($first['month'] > 12) 
                { 
                    $first['year']++; 
                    $first['month'] = 1; 
                } 
            } 
                                                                                                                                                                            
            $add_day = 0; 
            while (smoothdate ($first['year'], $first['month'], $first['day'] + 1) <= $target) 
            { 
                if (($first['year'] % 100 == 0) && ($first['year'] % 400 == 0)) 
                { 
                    $month_lengths[1] = 29; 
                } 
                else 
                { 
                    if ($first['year'] % 4 == 0) 
                    { 
                        $month_lengths[1] = 29; 
                    } 
                } 
                
                $add_day++; 
                $first['day']++; 
                if ($first['day'] > $month_lengths[$first['month'] - 1]) 
                { 
                    $first['month']++; 
                    $first['day'] = 1; 
                    
                    if ($first['month'] > 12) 
                    { 
                        $first['month'] = 1; 
                    } 
                } 
                
            } 
                                                                                                                                                                                                                                                        
            $retval = array ('years' => $add_year, 'months' => $add_month, 'days' => $add_day); 
        } 
    } 
                                                                                                                                                                                                                                                                                
    return $retval; 
} 

?>
usage

Code: Select all

/* 
    This code is merely an example of use of the function 
*/ 

print '<pre>'; 
$begin = array ('year' => 2001, 'month' => 3, 'day' => 14); 
$end = array ('year' => 2004, 'month' => 3, 'day' => 14); 

$foo = date_difference ($begin, $end); 
if ($foo !== FALSE) 
{ 
    print_r ($foo); 
} 
else 
{ 
    print 'FALSE'; 
}
Post Reply