1. Background reading for newbies like me
2. Default Settings
3. In the model
4. Important Notes:
5. Validation

Countries sometimes change their laws pertaining to time zones and daylight saving times. This means that GMT+2 can be a different local time this August compared to last August! It could even be a different date!

However, by using php's DateTime class (instead of timestamps), we can do both "format" and "time zone" conversions on a single DateTime object (only 2 lines of code) for date, time, timestamp and datetime types - while php takes care of nightmares such as current and historical time zones and daylight saving times.

(Php uses the IANA/Olson time zone database.)

Background reading for newbies like me ¶

Reading more about the following is essential:

The Database ¶

MySql's most common data types for dates and times are: ~~~ Type Format Range Date YYYY-MM-DD 1000-01-01 to 9999-12-31 Time HH:MM:SS 00:00:00 to 23:59:59

		HHH:MM:SS				-838:59:59 to 838:59:59

Timestamp numerical 1970-01-01 00:00:01 UTC to

								2038-01-19 03:14:07 UTC

DateTime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59 ~~~

PHP ¶

**Function**	**Format**									**Range**
date()		http://www.php.net/manual/en/function.date.php	timestamp range
time()		http://www.php.net/manual/en/function.date.php	timestamp range

**Class**		
DateTime (this class is doing all the work for us)

Note: When reading blogs, make sure about what is discussed. For example, there should be a difference between MySql date/time (referring to the individual date and time types) and datetime (a single type).

Also do not confuse MySql's date, time and datetime types (storing data) with php's date() and time() functions (getting the server's time) and php's datetime class (a class with its own functions, that we will use to do the conversions).

Also check out this amazing blog by Aaron Francis.

Locales and time zones ¶

A user's locale will help you to convert data to the user's preferred format - such as converting yyyy-mm-dd dates to dd/mm/yy and back. These "format" conversions will not change the value of the data - only how the data is displayed.

Doing "time zone" conversions will change the value of the data - not how it is displayed.

However, the locale will not give you the user's time zone - at least not in the format that php wants it. User time zones thus need to be stored/compiled/calculated separately. Here is a list of the php supported named time zones that take care of daylight savings etc.

To make things more understandable, this wiki uses a single time zone for "time zone" conversions; and a fixed set of formats (created in the model and not retrieved from a locale) for "format" conversions. Obviously you can use separate locales and time zones for individual users.

Locales are also most important when doing language translations.

Check out the available locales in your yii\i18n\data folder.

Locale

As already stated, this wiki does not use a locale for "format" conversions, but the following is interesting to note: The user's locale is chosen by setting the language Id. This can be done in config/main.php ...

'name'=>'myWebSite',

'language' => 'en_gb', // Target language (user's language / locale)

'sourceLanguage' => 'en_us', // Source Language (app language) (default english)

Locales can also be changed in your code: Yii::app()->language='pt_br';

Time Zone

Both MySql and PHP perform automatic time zone conversions - for timestamps.

In php, the date() function either returns a passed timestamp or time(). The time() function returns the server's time after it was converted to UTC.

MySql converts timestamps from the server's time zone to UTC for storage, and back from UTC to the server's time zone for retrieval. Important: To make things a little more complicated, this conversion does NOT occur for other types such as datetime and time.

These time zone conversions, plus the fact that it is only performed on timestamps (not on datetime and time types), can make life very confusing and things can get really interesting if you move your application to a server with a different time zone. Read more...

So the first thing you want to do is to level the playing fields by making sure that ALL dates and times you get from both MySql and php are received in UTC. Then you don't have to worry about them any more.

Now you only have to worry about converting the data in your code from UTC to the user's time zone and back to UTC:

read data from db in UTC > convert data to user's time zone > render data

receive data from user in user's time zone > convert to UTC > write to db

Default Settings ¶

In config/main.php: ¶

...
'name'=>'myWebSite',

/*  Setting PHP timezone to UTC. 
This is NOT setting the database time zone OR the server's time zone, but it sets the time zone in which php's date() and time() functions will return their results.

In our case we set it to UTC/GMT, which means php's date() and time() functions will take the server's time, and convert it to UTC - regardless of what the server's own time zone is.*/
'timeZone'=>'GMT',


/* Setting MySql timezone to UTC. 
Timestamps are stored in UTC and converted to the server's time zone on retrieval. [Read more...](http://dev.mysql.com/doc/refman/5.7/en/datetime.html "Read more...")
					   
But, since we set the database's time zone below to UTC, this conversion will not take place and if we store all timestamps (together with datetime and time types, which are not converted) in UTC, then php will also receive them in UTC.
					   
Note: this setting only influences the communication between your application and MySql. If you view the data outside your application (e.g. a tool such as phpMyAdmin), this conversion will still take place for timestamps i.e. their values will be displayed according to the server's time zone.
					   
Ps. If the MySql server has the time zone names installed, then use 'UTC' instead of '+00:00'. */

'components' => array(
	
	'db'=>array(
		'connectionString' => '...',
		...		
		/* SQL statements that should be executed right after the DB
		connection is established. */
		'initSQLs'=>array("set time_zone='+00:00';"),
		...

In the model ¶

If you missed it above: this wiki uses a single timezone for "timezone" conversions; and a fixed set of formats (created in the model and not retrieved from a locale) for "format" conversions. Obviously you want to use separate locales and timezones for individual users in your own code.

<?php
class myModel extends CActiveRecord 
{
	// User's timezone
	public $user_timezone = 'Africa/Johannesburg'; // yes, we have computers :)
	
	/*** PHP FUNCTION FORMATS ***/
	// Between PHP and User					 		*** ISO RESULT ***
	public $php_user_short_date = 'd/m/Y'; 			// dd/mm/yyyy
	public $php_user_time		= 'H:i:s'; 			// HH:mm:ss
	public $php_user_datetime	= 'd/m/Y H:i:s'; 	// dd/mm/yyyy HH:mm:ss
	// Between PHP and Db (MySql)
	public $php_db_date 		= 'Y-m-d'; 			// yyyy-mm-dd
	public $php_db_time		 	= 'H:i:s'; 			// HH:mm:ss
	public $php_db_datetime	 	= 'Y-m-d H:i:s';	// yyyy-mm-dd HH:mm:ss

	protected function afterFind()
	{
		foreach($this->metadata->tableSchema->columns as $columnName => $column)
	    {
			/* Test if current column is date/time/timestamp/datetime */    
			if (($column->dbType == 'date') 	||
				($column->dbType == 'time') 	||
				($column->dbType == 'timestamp')||
				($column->dbType == 'datetime'))
			{	
				/* Test for null column */
				if($this->$columnName === null){
					$test = 0;
				}
				else{
					$test = str_replace(array('/','-', '.', ':', ' '),'',
						$this->$columnName);
				}
				
				/* 	Continue if column is not null, else set column to false -
					which will prevent column being displayed in gridviews if
					gridview data is set like:
					'value' => '($data->mycolumn) ? $data->mycolumn : "" ', 
				*/
				if($test > 0)
				{
					/* 	Create a new php DateTime object using the 
						date/time/timestamp/datetime retrieved from the
						database. 
						Set the object's timezone to UTC (data received 
						in UTC) */
					$datetime_object = new
						DateTime($this->$columnName, 
							new DateTimeZone('UTC') );

			 		/*  Change the DateTime object's timezone 
					and format, based on the column's data
					type in the DB.
					Note: changing the object's timezone will
					automatically also change its time. */
					switch ($column->dbType) 
					{
						case 'date':
							/* Convert the object's time to the user's time */
								// Do not take any action here. Date columns do
								// not include the time and thus cannot be
								// converted.
							/* Output the required format */
							$this->$columnName =
								$datetime_object->format(
									$this->php_user_short_date);
							break;
							
						case 'time':
							/* 	Convert the object's time to the user's time */
							$datetime_object->setTimeZone(new
								DateTimeZone($this->user_timezone));
							/* Output the required format */
							$this->$columnName =
								$datetime_object->format($this->php_user_time);
							break;
							
						case 'timestamp':
							/* 	Convert the object's time to the user's time */
							$datetime_object->setTimeZone(new
								DateTimeZone($this->user_timezone));
							/* Output the required format */
							$this->$columnName =
								$datetime_object->format(
									$this->php_user_datetime);
							break;
							
						case 'datetime':
							/* 	Convert the object's time to the user's time */
							$datetime_object->setTimeZone(new
								DateTimeZone($this->user_timezone));
							/* Output the required format */
							$this->$columnName =
								$datetime_object->format(
									$this->php_user_datetime);
							break;
					}
				}
				else{
					$this->$columnName = false;
				}
			}
	    }
	    return parent::afterFind();
	}

	protected function beforeSave()
	{	

		/*	Reformat date/time/timestamp/datetime from local format and timezone
			to database format (yyyy-mm-dd OR HH:mm:ss OR yyyy-mm-dd HH:mm:ss) and UTC. */
		foreach($this->metadata->tableSchema->columns as $columnName => $column)
	    {
			/* Test if current column is date/time/timestamp/datetime */    
			if (($column->dbType == 'date') 	||
				($column->dbType == 'time') 	||
				($column->dbType == 'timestamp')||
				($column->dbType == 'datetime'))
			{	
				/* Test for null column */
				if($this->$columnName === null){
					$test = 0;
				}
				else{
					$test = str_replace(array('/','-', '.', ':', ' '),'',
						$this->$columnName);
				}
				
				/* Continue if column is not null. */
				if($test > 0)
				{
					switch ($column->dbType) 
					{
						case 'date':
							/* create datetime object */
							$datetime_object = DateTime::createFromFormat(
								$this->php_user_short_date,
								$this->$columnName,
								new DateTimeZone($this->user_timezone));
							/* change timezone to UTC */
								// Do not take any action. Do not convert the
								// timezone for dates, because the time is not
								// included in the data saved to the db, which
								// means that the data cannot be converted back.
							/* change format to DB format */
							$this->$columnName =
								$datetime_object->format($this->php_db_date);
							break;
						
						case 'time':
							/* create datetime object */
							$datetime_object = DateTime::createFromFormat(
								$this->php_user_time,
								$this->$columnName,
								new DateTimeZone($this->user_timezone));
							/* change timezone to UTC */
							$datetime_object->setTimeZone(new
								DateTimeZone('UTC'));
							/* change format to DB format */
							$this->$columnName = 
								$datetime_object->format($this->php_db_time);
							break;
						
						case 'timestamp':
							/* create datetime object from user's format */
							$datetime_object = DateTime::createFromFormat(
								$this->php_user_datetime,
								$this->$columnName,
								new DateTimeZone($this->user_timezone));
							/* change timezone to UTC */
							$datetime_object->setTimeZone(new 
								DateTimeZone('UTC'));
							/* change format to DB format */
							$this->$columnName = 
								$datetime_object->format($this->php_db_datetime);
							break;
						
						case 'datetime':
							/* create datetime object */
							$datetime_object = DateTime::createFromFormat(
								$this->php_user_datetime,
								$this->$columnName,
								new DateTimeZone($this->user_timezone));
							/* change timezone to UTC */
							$datetime_object->setTimeZone(new 
								DateTimeZone('UTC'));
							/* change format to DB format */
							$this->$columnName = 
								$datetime_object->format($this->php_db_datetime);
							break;
					}
				}
			}
	    }
		return parent::beforeSave();
	}
}
?>

Important Notes: ¶

The model's declared "PHP FUNCTION FORMATS" can be divided into two groups namely

"Between PHP and User" and "Between PHP and Db". I also added the "Iso Result" behind each of these formats.

The db wants the data to be stored in these ISO 8601 formats: yyyy-mm-dd OR HH:mm:ss OR yyyy-mm-dd HH:mm:ss. Thus, to get these "ISO 8601" results, use the "Between PHP and Db" formats with the php functions.

When working with user input: To ensure that DateTime::createFromFormat() will receive the data in the right format, you have to:

a)	make sure that your fields/widgets in your views use formats that produce the
	same "Iso Result" as the formats in the "Between PHP and User" group;

b)	make sure that your automatic timestamps that you create in your code, 
	produce the same "Iso Result" as the formats in the "Between PHP and User" group;

For example:

a)	use the following formats for the CJuiDateTimePicker extension:	
	// JQUERY WIDGET FORMATS
	public $jq_user_short_date 	= 'dd/mm/yy'; // Iso Result: dd/mm/yyyy
	public $jq_user_time 		= 'hh:mm:ss'; // Iso Result: HH:mm:ss

b)	create timestamp:
	$format = $this->php_user_datetime; // Iso Result: dd/mm/yyyy HH:mm:ss
	$utc_date_and_time = date($format);

Example-b should give you the date and time in UTC - regardless of what your computer's timezone is set to.

If your widget cannot produce the same "iso result", you could have DateTime::createFromFormat() testing for different "incoming" formats.

Tip:

The above code handles specific data types, but does not further differentiate between fields of the same data type. To improve this, some people end their column names with specific words such as ..._user_timestamp and ..._auto_timestamp. This allows you to take different action on different kinds of timestamps. Just examine $columnName.

Validation ¶

In the above code, the data is converted from the user's format to the db format in the model's beforeSave(). However, some validation rules in the model might expect the data in different formats - of which some might be the db format and others the uset format. This happens during validation, which is BEFORE the data was converted to db format in beforeSave(). In these cases you will have to make additional custom conversions - just for validation purposes. Or perhaps you could do these conversions in beforeValidate().

Whatever the case, make sure what format is required for the different validators. Here are two such examples in the model's validation rules.

The first rule tests whether the user entered valid dates. The validator expects the data in a format that can be read by CDateTimeParser - which is the same as the "ISO Result" for formats in the "Between PHP and User" group.

array('from_date, to_date', 'date',  'format'=>'dd/mm/yyyy'),

The following example tests whether a from_date precedes a to_date. For this 'mathematical' testing, the dd/mm/yyyy format does not work - so I'm converting the dates to the ISO format preferred by the Db (the names of the attributes are passed in one string - joined by a '+' sign - to a custom validator).

array('from_date+to_date',  'common.extensions.icvalidators.fromtodate'),

Custom validator:

<?php
class fromtodate extends CValidator
{
	protected function validateAttribute($model,$combinedAttribute) 
	{
		/* 	Only continue authentication if model has no validation errors. 
			Dates will thus already be valid dates if you have a rule like:
			array('from_date, to_date', 'date',  'format'=>'dd/mm/yyyy'), */
		if(!$model->hasErrors())
		{
			$attributes = null;
			
			/* Extract attributes */
			if(strpos($combinedAttribute, "+"))
			{
				$attributes = explode("+", $combinedAttribute);
			}
			else
			{
				throw new CDbException( ... $combinedAttribute format is incorrect);
			}
			
			/* Read the two dates */
			$i = 1;
			foreach($attributes as $attribute)
			{
				if($i == 1)
				{	
					/*** Process from_date ***/
					$fromdate = $model->$attribute;
					$fromLabel = $model->getAttributeLabel($attribute);
					
					/* Test for null value */
					$test = str_replace(array('/','-', '.', ':', ' '), '',
						$fromdate);
					if(($fromdate===null || $fromdate==='' || $test == 0))
						throw new CDbException(... from-date cannot be null);
					
					/* Convert user from-date to php datetime object */
					$fromdateObj = DateTime::createFromFormat(
						$model->php_user_short_date, $fromdate);
					/* Test for any errors during convertion */
					$e = DateTime::getLastErrors();

					if($e['error_count'] > 0)
					{
						foreach($e['errors'] as $error)
						{
							$this->addError($model,$attribute,$error);
						}
						return;
					}
					if($e['warning_count'] > 0)
					{
						foreach($e['warnings'] as $error)
						{
							$this->addError($model,$attribute,$error);
						}
						return;
					}
					
					/* Convert datetime object to db format */
					$fromdateDb = $fromdateObj->format($model->php_db_date);
				}
				else
				{	/*** Process to_date ***/
					$todate = $model->$attribute;
					$toLabel = $model->getAttributeLabel($attribute);
					
					/* Test for null value */
					$test = str_replace(array('/','-', '.', ':', ' '), '',$todate);
					if(($todate===null || $todate==='' || $test == 0))
						throw new CDbException(... to-date cannot be null);
					
					/* Convert user to-date to php datetime object */
					$todateObj = DateTime::createFromFormat(
						$model->php_user_short_date, $todate);
					/* Test for any errors during convertion */
					$e = DateTime::getLastErrors();

					if($e['error_count'] > 0)
					{
						foreach($e['errors'] as $error)
						{
							$this->addError($model,$attribute,$error);
						}
						return;
					}
					if($e['warning_count'] > 0)
					{
						foreach($e['warnings'] as $error)
						{
							$this->addError($model,$attribute,$error);
						}
						return;
					}
					
					/* Convert datetime object to db format */
					$todateDb = $todateObj->format($model->php_db_date);
				}
				$i++;
			}
			
			/* Test if From-date precedes To-date */
			if($fromdateDb > $todateDb)
			{
				$error = 'The ' . $fromLabel . ' must precede the ' . $toLabel .
				'.';
				$this->addError($model,$attribute,$error);
			}
		}
	}
}
?>

Hope this helps.