Formatted date filter in CGridView

Question

I display my date in CGridView as: "22.6.2012 22:53" with:

array('name' => 'date',
            'value' => date("j.n.Y G:i", strtotime($model->date))
        ),

But in my filter, I need to search in this format (which is in the database) to get results: "2012-06-22 22:53".

How can I make my filter to work in the format that is displayed in my CGridView? I've searched for an answer but haven't found one, I've also tried adding the date function in my model search() for this attribute:

$criteria->compare('date', date("j.n.Y G:i", strtotime($this->date), true);

but then I just get an empty list :)

Help would be greatly appreciated.

Answer 1

Like many others I also struggled with this, well displaying the grid wasn't the problem, but filtering in the localized datetime was! So I created my own formatter, used it in the search() function of my models (when passing the search parameters to compare()) and it works like a charm. I can now filter on date/datetime fields in any localization (I use Dutch):

"30-12-2018" becomes "2018-12-30"
">30-12-2018" becomes ">2018-12-30"
"30-12-2018 23:59:49" becomes "2018-12-30 23:59:49"
">=30-12-2018 23:59:49" becomes ">=2018-12-30 23:59:49"

My localization:

// dateFormat['short'] = 'dd-MM-yyyy'
// timeFormat['medium'] = 'HH:mm:ss'
Yii::app()->format->datetimeFormat = strtr(Yii::app()->locale->dateTimeFormat, 
                            array("{0}" => Yii::app()->locale->getTimeFormat('medium'),
                                  "{1}" => Yii::app()->locale->getDateFormat('short')));
       Yii::app()->format->dateFormat = 'short';
       Yii::app()->format->timeFormat = 'medium';

My CGridView contains the following date time column:

'mutation_date_time:dateTime'

And (a snippet of) my own formatter with some handy functions:

class Formatter extends CLocalizedFormatter
{
    public function formatWithoutSearchOperator($value)
    {
        // This snippet is taken from CDbCriteria->compare()
        if(preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
        {
            $value=$matches[2];
            $op=$matches[1];
        }
        else
            $op='';

        return $value;
    }

    public function formatOnlySearchOperator($value)
    {
        // This snippet is taken from CDbCriteria->compare()
        if(preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
        {
            $value=$matches[2];
            $op=$matches[1];
        }
        else
            $op='';

        return $op;
    }

    /*
    * Format a localized datetime back to a database datetime (Y-m-d H:i:s).
    * If a comparison operator is given, it is preserved. So strip it if you need to save the date in the database.
    * If no time given, it's also not returned (MySQL database appends '00:00:00' as time to it upon saving).
    * With this function the following localized datetimes just work like the stock datetime filters:
    *   - "30-12-2018" becomes "2018-12-30"
    *   - "30-12-2018 " becomes "1970-01-01" (note the extra space in input)
    *   - ">30-12-2018" becomes ">2018-12-30"
    *   - "30-12-2018 23:59:49" becomes "2018-12-30 23:59:49"
    *   - ">=30-12-2018 23:59:49" becomes ">=2018-12-30 23:59:49"
    * 
    * For save() and afterFind() integration see:
    * https://github.com/YetOpen/i18n-datetime-behavior
    */
    public function formatToDatabaseDatetime($value)
    {
        // get the comparison operator from the string:
        $comparator = $this->onlySearchOperator($value);

        // get the datetime without the comparison operator:
        $datetime = $this->withoutSearchOperator($value);

        // parse the given datetime according to the locale format to a timestamp
        $datetime_parsed = CDateTimeParser::parse(
            $datetime, 
            strtr(
                Yii::app()->locale->datetimeFormat, 
                array(
                    "{0}" => Yii::app()->locale->getTimeFormat(Yii::app()->format->timeFormat),
                    "{1}" => Yii::app()->locale->getDateFormat(Yii::app()->format->dateFormat)
                )
            )
        );

        // if its not a valid date AND time, check if it can be parsed to a date only:
        if($datetime_parsed === false)
        {
            $date_parsed = CDateTimeParser::parse(
                $datetime, 
                Yii::app()->locale->getDateFormat(Yii::app()->format->dateFormat)
            );
        }

        // If no time part given, also output only the date
        if($datetime_parsed===false)
        {
            $transformed = date(
                'Y-m-d', 
                $date_parsed
            );
        }
        else
        {
            $transformed = date(
                'Y-m-d H:i:s', 
                $datetime_parsed
            );
        }

        return  $comparator . $transformed;
    }
}

And within my search() function in my CActiveRecord model I use the following to compare the localized datetime with the records in the database:

$criteria->compare('mutation_date_time',Yii::app()->format->toDatabaseDateTime(trim($this->mutation_date_time)),true);

Please note the trim() there, that's by design (see function description formatToDatabaseDateTime()).

A big difference with filtering directly in correct database format: an invalid date converts to "1970-01-01"!

I highly appreciate feedback and I really hope my code helps somebody!

Answer 2

compare() makes a sql sentence with the input, so I had to change the input to my wanted format.

my function:

function changeDateToDBformat($datum) {
        if (strstr($datum, '.') || strstr($datum, ':')) {
            $formats = array('!j.n', '!j.n.Y', '!j.n.Y H:i', '!n.Y H:i', '!n.Y', '!H:i', '!j.n.Y H', '!n.Y H', '!Y H:i', '!Y H');
            $date = false;

            foreach ($formats as $format) {
                $date = DateTime::createFromFormat($format, $datum);
                if (!($date === false)) {
                    $izbraniFormat = $format;
                    break;
                }
            }

            if (!$date === false) {
                $datum1 = $date->format('Y-m-d H:i');
                $date2 = DateTime::createFromFormat(substr($izbraniFormat, 1, strlen($izbraniFormat)), $datum);
                $datum2 = $date2->format('Y-m-d H:i');

                $datumcas1 = explode(' ', $datum1);
                $datumcas2 = explode(' ', $datum2);

                $prvidatum = explode('-', $datumcas1[0]);
                $drugidatum = explode('-', $datumcas2[0]);
                $koncniDatum = '';
                for ($a = 0; $a < sizeof($prvidatum); $a++) {
                    if ($prvidatum[$a] == $drugidatum[$a])
                        $koncniDatum .= '-' . $prvidatum[$a];
                }
                $koncniCas = '';
                $prvicas = explode('-', $datumcas1[1]);
                $drugicas = explode('-', $datumcas2[1]);
                for ($a = 0; $a < sizeof($prvicas); $a++) {
                    if ($prvicas[$a] == $drugicas[$a])
                        $koncniCas .= ':' . $prvicas[$a];
                }
                $koncniDatum = substr($koncniDatum, 1, strlen($koncniDatum));
                if (strlen($koncniCas) > 0)
                    $koncniDatum .= ' ' . substr($koncniCas, 1, strlen($koncniCas));

                $datum = $koncniDatum;
            }
        }
        return $datum;
    }
//translations:
//izbrani == selected
//datum == date
//cas == time
//koncni == end
//prvi == first
//drugi == second

With this, a user can enter date in the format "j.n.Y H:i" and also just portions of this format (j.n, n.Y, Y H:i,...).

I would like to thank Jon and nickb for help! link

Answer 3

To begin with, you should not be using the value property to control the formatting of dates. The proper way is to set the type property to 'date' and, if you do not do this already, set CApplication.language to target the appropriate locale.

For the filter it would be best for the user if you use a CJuiDatePicker widget to let the user visually pick the date; there's a short and to-the-point guide on how to do that here.

Update:

Formatting columns with type == 'date' is done through CGridView.formatter, for which if you do not explicitly set a value the default is whatever the 'format' application component is. So you can specify and configure a CFormatter on the spot, or if you want to use the application's formatter but with slight modifications you can do

$formatter = clone Yii::app()->format;
$formatter->dateFormat = 'whatever'; // or $formatter->dateTimeFormat

and then assign this instance to CGridView.formatter.