Difference between #22 and #27 of
i18n All-in-one format and timezone conversions for date, time, timestamp and datetime.

Revision #27 has been created by Gerhard Liebenberg on Jul 19, 2016, 7:39:34 PM with the memo:

Added details.
« previous (#22) next (#28) »

Changes

Title unchanged

i18n All-in-one format and timezone conversions for date, time, timestamp and datetime.

Category unchanged

How-tos

Yii version unchanged

Tags unchanged

i18n locale timezone UTC date time timestamp datetime

Content changed

Most of the currently available functions/extensions/behaviors directly or indirectly use timestamps to perform "format" and "Countries sometimes change their laws pertaining to time zone" conversions. This creates problems for dates falling outside the timestamp range; and the two forms of conversions must often be done in separate processes.s and daylight saving times. This means that GMT+2 can be a different time this August compared to last August! 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 daylight saving times and more importantly, historical changes incurrent and historical time zones and daylight saving times. (Php uses the IANA/Olson time zone database.)

Background reading for newbies like me
[...]
Also check out this amazing blog by [Aaron Francis](http://aaronfrancis.com/blog/2013/4/21/wrangling-timezones-in-php-mysql-and-yii "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](http://php.net/manual/en/timezones.php "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
[...]
Locales can also be changed in your code: Yii::app()->language='pt_br';

**Time
z Zone** Both MySql and PHP perform automatic time zone conversions - for timestamps. MySql converts timestamps from the current time zone (by default,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 currentserver's time zone for retrieval. Important: This does not occur for other types such as DateTime.
 
 
 
In php, the date() function either returns a passed timestamp or time(). T
o make things a little more complicated, this conversion does NOT occur for other time() function returns the server's time after it was converted to UTCypes such as datetime and time. These time zone conversions, plus the fact that it is only performed on timestamps (not on datetime and datetime 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...](http://dev.mysql.com/doc/refman/5.7/en/datetime.html "Read more...") So the first thing you want to do is to level the playing fields by making sure that allALL dates and times you get from both MySql and php are in UTC (GMT +00:00)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
[...]
/* Setting PHP timezone to UTC.
This is
notNOT setting the database time zone OR the server's time zone, but it sets the time zone used byin which php's date() and time() functions.
 
It
 will return their results.
 
 
In our case we set it to UTC/GMT, which means php's date() and time() functions will
takes the server's time, and converts it to UTC.
 
So even if the server's clock is set to a different
 - regardless of what the server's own time zone, this should still give you UTC.  is.*/ 'timeZone'=>'GMT',
 
/* Setting MySql timezone to UTC.  SinceTimestamps 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 is set to UTC and since timestamps are storbelow 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, no conversion of timestamps will take place in the database.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(
[...]
date/time/timestamp/datetime retrieved from the
database.
Set the object's timezone to UTC (
same as the 
 
server's timezone
data received 
 
in UTC
) */
$datetime_object = new
DateTime($this->$columnName,
[...]
/* 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)
{
[...]
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
[...]
b) create timestamp:
$format = $this->php_user_datetime; // Iso Result: dd/mm/yyyy HH:mm:ss
$
this->record_createutc_date_and_timestamp = date($format);
 
Remember: To test example-b, remember to set the timezone of your computer to GMT+00:00 - just like your production server - otherwise php's date() and time() functions will further convert the data. Ps. don't forget to change it back - otherwise you will be late :)
 
~~~
 
 

 
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 iso 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.
 
 
Here are two such examples in the model's validation rules.
 
 
The first rule tests whether the user entered a valid date. 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.
 
 
 
```php 
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 back to Db format.(The names of the attributes are passed in one string - joined by a '+' sign - to a custom validator.)

 
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.
 
 
 
```php 
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).



```php
array('from_date+to_date', 'common.extensions.icvalidators.fromtodate'),
```
[...]
6 0
13 followers
Viewed: 32 754 times
Version: 1.1
Category: How-tos
Written by: Gerhard Liebenberg
Last updated by: Gerhard Liebenberg
Created on: Sep 20, 2013
Last updated: 7 years ago
Update Article

Revisions

View all history