Sunday 3 February 2013

PHP How To : Creating date ranges for filtering in PHP for MySQL query - Part 2

In Part 1,  I provided the code snippets and explanation for building dynamic date ranges in PHP that can be used with MySQL queries with BETWEEN keyword. The snippets were

 // For last six months, 
     $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -6 month

')); 
     $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days')); 
 // For last month, 
           $start = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days -1

month')); 
           $end = date('Y-m-d', strtotime('-' . (date('j') - 1) . ' days ')); 
 // For last year, 
           $start = date('Y-m-d', strtotime('1 jan last year')); 
           $end = date('Y-m-d', strtotime('31 dec last year +1 day ')); 

 However,  it still has a problem. Since, we are including an extra day in order to include results for the whole day of the end date (see Part 1 for explanation), this also leads to the inclusion of results with timestamp right at the start of the extra day. Lets take the "Last Six Month" case as an example. If I execute this statement today (3rd of Feb), it will get me "2013- 02-01".  So if I use this as end date, then MySQL will return me a result set that will also include records with timestamp ="2013-02-01 00:00:00"  which is undesirable.  Therefore, in order to get rid of these undesirable records from the result set, I simply subtract a second from the end date.  So, the code snippets now become

 // For last six months, 
     $start = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . ' days

-6 month')); 
     $end = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . ' days

-1 second')); 
 // For last month, 
           $start = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) .

' days -1 month')); 
           $end = date('Y-m-d H:i:s', strtotime('today -' . (date('j') - 1) . '

days -1 second ')); 
 // For last year, 
           $start = date('Y-m-d H:i:s', strtotime('1 jan last year')); 
           $end = date('Y-m-d H:i:s', strtotime('31 dec last year +1 day -1

second')); 

No comments:

Post a Comment