CiviCRM Community Forums (archive)

relative dates are not yet supported in advanced search / smart groups. This would be an awesome addition to CiviCRM

would be great if you can work on it and provide a patch. Alternatively you can sponsor a developer and/or core team to work on this. I suspect this is a 20-40 hour project

lobo

if you do think its a one hour project, please do jump in and make the needed changes. Please submit your changes as a patch. do remember to take care of the various search scenarios and component searches also.

lobo

I havent tested this for knock-on effects but i have been able to build a smart group with relative dates for Contribution 'Recieve Date' using the Search Builder.

I did this by modifying what is an acceptable 'Date' input type.
I added a few lines to

civicrm/CRM/Utils/Type.php

in "public static function escape" i added a new check to the case 'Date' case 'Timestamp' which looks for human readable dates then converts them into the proper yyyymmdd date format for return.

Old Code:


       case 'Date':
        case 'Timestamp':
            // a null date or timestamp is valid
            if ( strlen( trim( $data ) ) == 0 ) {
                return trim( $data );
            }
           
            if ( ( preg_match('/^\d{8}$/', $data) ||
                   preg_match('/^\d{14}$/', $data) ) &&
                 CRM_Utils_Rule::mysqlDate($data) ) {
                return $data;
            }
            break;
           
           
New Code checking for relative human readable dates:

case 'Date':
        case 'Timestamp':
            // a null date or timestamp is valid
            if ( strlen( trim( $data ) ) == 0 ) {
                return trim( $data );
            }
           
            if ( ( preg_match('/^\d{8}$/', $data) ||
                   preg_match('/^\d{14}$/', $data) ) &&
                 CRM_Utils_Rule::mysqlDate($data) ) {
                return $data;
            }
            if (strtotime($data)) {
                $timestamp = strtotime($data);
           $yyyymmdd = date('Ymd',$timestamp);
                return $yyyymmdd;
            }
            break;
           
           


So with the above code you can now enter anything php's string to time function can handle into Search Builder when looking up >= or <= on date fields.

For example try entering yesterday,last year, -2 year, -3 week, etc.
For more examples Google "php strtotime manual"


Enjoy!

David Shaw

This has been coming up more often. I suspect we will have a Make It Happen for the 4.1 release to include "flexible" date searching in all our searches

lobo

After the testing, I think this is not the solution for smart group. Because the saved query still have static datetime.
I suppose solution of relative date for smart groups need to use STR_TO_DATE function of mysql
( http://stackoverflow.com/questions/3818978/how-to-specify-a-relative-date-in-mysql )

I didn't have time to implement that, but seems that the much more trivial way to apply to smart group.

I havent tested this for knock-on effects but i have been able to build a smart group with relative dates for Contribution 'Recieve Date' using the Search Builder.
....
....
David Shaw