[ricardograna]

In portuguese forum, we are having a discussion about date formats and best practices about this.

I reached to a solution that can be used with MySQL for all different date formats. I hope this can help others.
It is a suggestion that can be improved for reusability, for sure.


Suppose that you have a 'mydate' field in your AR. So, add the following lines to your AR:

// convert input date to mysql date format
protected function beforeSave(){
        $this->mydate = date('Y-m-d', CDateTimeParser::parse($this->mydate, Yii::app()->locale->dateFormat));
        return true;
}

/* when loading records, reconvert date from mysql format to your locale format.
 NOTE: this method converts just to a date format, not datetime format. 
I don't know how to differentiate them.        
*/
protected function afterFind(){
         $this->mydate = Yii::app()->dateFormatter->formatDateTime(
                             CDateTimeParser::parse($this->mydate, 'yyyy-MM-dd'),'medium',null);
         return true;
}

// rule to validate date according to locale date format
public function rules(){
        return array(                   
          array('mydate', 'type', 'type'=>'date', 'dateFormat'=>Yii::app()->locale->dateFormat),                  
        );
}

Really long line codes, but I am thinking we can extend CAtiveRecord to catch all date fields and do the conversions.

Any problems or doubts, please advice. Improvements and new ideas are welcome.

[ricardograna]

Modified beforeSave and afterFind.

- Now it searchs for all date/datetime fields and convert them.
- Now I know how to diferenttiate date from datetimes (so easy...duh!)

protected function beforeSave(){
	foreach($this->metadata->tableSchema->columns as $columnName => $column){
		if ($column->dbType == 'date'){
			$this->$columnName = date('Y-m-d', CDateTimeParser::parse($this->$columnName, Yii::app()->locale->dateFormat));
		}elseif ($column->dbType == 'datetime'){
			$this->$columnName = date('Y-m-d H:i:s', CDateTimeParser::parse($this->$columnName, Yii::app()->locale->dateFormat));
		}
	
	}
	
        return true;
}
	

protected function afterFind(){
						
	foreach($this->metadata->tableSchema->columns as $columnName => $column){
			
		if (!strlen($this->$columnName)) continue;
			
		if ($column->dbType == 'date'){				
			$this->$columnName = Yii::app()->dateFormatter->formatDateTime(
					CDateTimeParser::parse($this->$columnName, 'yyyy-MM-dd'),'medium',null);
			}elseif ($column->dbType == 'datetime'){
				$this->$columnName = Yii::app()->dateFormatter->formatDateTime(
					CDateTimeParser::parse($this->$columnName, 'yyyy-MM-dd hh:mm:ss'));
			}
	}
	return true;
}

[qiang]

There is an alternative way:

public function getMyDateInput()
{
   return date('Y-m-d', CDateTimeParser::parse($this->mydate, Yii::app()->locale->dateFormat));
}

public function setMyDateInput($value)
{
   $this->mydate = Yii::app()->dateFormatter->formatDateTime(CDateTimeParser::parse($value, 'yyyy-MM-dd'),'medium',null);
}

Now, in your form view, you should collect the date input via "myDateInput" attribute rather than "mydate". And you should also validate "myDateInput".

[ricardograna]

Interesting, too.

[Ben]

I think when dealing with date/time and localization, you should also consider different time zones. I know they are not coupled to a locale, but nonetheless chances are high that if I enter 12:00, and you read 12:00 that we mean different moments. So I think in a localized environment dates/times should always be saved in the same timezone, for example UTC.

So I think what is missing is a DateTimeC, that stores data in UTC, can store an optional timezone offset, and uses these two properties together with the dateFormatter and the current locale to display the time in the string representation the user is used to read.

Joomla comes with such a class, and although I don't like Joomlas documentation, the class itself is really helpfull if you once realized how to use it. See JDate documentation for details.

[Tom Fazakas]

public function getMyDateInput()
{
   return date('Y-m-d', CDateTimeParser::parse($this->mydate, Yii::app()->locale->dateFormat));
}

public function setMyDateInput($value)
{
   $this->mydate = Yii::app()->dateFormatter->formatDateTime(CDateTimeParser::parse($value, 'yyyy-MM-dd'),'medium',null);
}

This exactly is what I'm missing from the perfect Yii framework's documentation: more real examples. The guide part is great but for instance this isn't mentioned. You should put these samples at least into the guide, because there is not much information about how to get the localized date, or probably I'm the only one I couldn't find:
http://www.yiiframew.../CDateFormatter

This forum is a very good place for getting infromation about the usage but would be great if the guide contains even more detailed examples.

ps: Yes, it is!!!! and keep on....

[herculesmorse]

I'm using a different locale (in GB here, we have dates as dd/mm/yyyy rather than mm/dd/yyyy and it does not appear to be working with the code:

public function getMyDateInput()
{
   return date('Y-m-d', CDateTimeParser::parse($this->mydate, Yii::app()->locale->dateFormat));
}

public function setMyDateInput($value)
{
   $this->mydate = Yii::app()->dateFormatter->formatDateTime(CDateTimeParser::parse($value, 'yyyy-MM-dd'),'medium',null);
}

[nye.cat]

Oooh thank you ricardograna!! I was going crazy with this, thank you so much!!

[ChessSpider]

What do you guys think of this suggestion, to be included in the Model.

    public function __get($name)
    {
        $type = isset($this->getMetaData()->tableSchema->columns[$name]) ? $this->getMetaData()->tableSchema->columns[$name]->dbType : "";

        $return = parent::__get($name);
        if ($return) // an actual value has been set
        {
            $dtp = Yii::app()->dateFormatter;
            if ($type == "date")
                return $dtp->formatDateTime($return, 'short', null);
            else if ($type == "datetime")
                return $dtp->formatDateTime($return, 'short', 'full');
        }

        return $return;
    }

    public function __set($name, $value)
    {
        $type = isset($this->getMetaData()->tableSchema->columns[$name]) ? $this->getMetaData()->tableSchema->columns[$name]->dbType : "";
        if ($type == "date")
            parent::__set($name, date ("Y-m-d", strtotime($value)));
        elseif ($type == "datetime")
            parent::__set($name, date ("Y-m-d H:i:s", strtotime($value)));
        else
            parent::__set($name, $value);
    }

Note that other values than 'short' for date will not work: strtotime() does not seem to understand non-english months

[Say_Ten]

Hmmm... Casting dates to a particular format should not really be the models responsibility, imo. The model should just store the date data, in my case I use Date(Time) fields in MySQL and all data is stored in UTC. It is the controller responsibility to ensure the data coming from the view reaches the model in the right format and the views responsibility to read back the data and format it accordingly. I make use of the in built PHP DateTime and DateTimeZone objects to handle the timezone stuff and Yii's date formatting stuff to handle the output. The model always has the time in MySQL's almost ISO8601 formatting. DRY would dictate the use of a helper class that encapsulated these translations though.

[ChessSpider]

Say_Ten, on 25 July 2011 - 10:19 AM, said:

Hmmm... Casting dates to a particular format should not really be the models responsibility, imo. The model should just store the date data, in my case I use Date(Time) fields in MySQL and all data is stored in UTC. It is the controller responsibility to ensure the data coming from the view reaches the model in the right format and the views responsibility to read back the data and format it accordingly. I make use of the in built PHP DateTime and DateTimeZone objects to handle the timezone stuff and Yii's date formatting stuff to handle the output. The model always has the time in MySQL's almost ISO8601 formatting. DRY would dictate the use of a helper class that encapsulated these translations though.

Agreed. But,

One could argue that the model should offer a standard way of presenting data, independent of the DBMS. My solution obviously does not; it is locale dependant. But it could easily be adapted into letting the model represent dates or datetimes in a PHP-friendly way (aka UNIX timestamps) with the strtotime() function, and accepting unix-timestamps for setting data.

I am not sure how said helper class would be implemented in Yii (I just started using). Thinking out loud: Model-behaviours?

Also, most of Yii edit functions (CActiveForm, etc) expects a CModel as parameter. Without rewriting these functions I do not see how one could give the Controller the responsibility of doing translations between Viewer and Model, or let the Viewer present data in another way than the Model provides it. For now I'll stick with my solution posted previously - I don't feel like rewriting the Yii widgets.

This post has been edited by ChessSpider: 26 July 2011 - 07:05 AM

[jacksmirk]

I had the same problems with dates until I find this extension i18n datetime behavior It worked great for me and it uses behaviours.

ChessSpider, on 26 July 2011 - 06:20 AM, said:

Agreed. But,

One could argue that the model should offer a standard way of presenting data, independent of the DBMS. My solution obviously does not; it is locale dependant. But it could easily be adapted into letting the model represent dates or datetimes in a PHP-friendly way (aka UNIX timestamps) with the strtotime() function, and accepting unix-timestamps for setting data.

I am not sure how said helper class would be implemented in Yii (I just started using). Thinking out loud: Model-behaviours?

Also, most of Yii edit functions (CActiveForm, etc) expects a CModel as parameter. Without rewriting these functions I do not see how one could give the Controller the responsibility of doing translations between Viewer and Model, or let the Viewer present data in another way than the Model provides it. For now I'll stick with my solution posted previously - I don't feel like rewriting the Yii widgets.