The internet suggests for CakePHP...
- Set your database time zone to UTC
- Set you CakePHP default time zone to UTC
- Store all datetime values in UTC
- Convert between UTC <==> Your Users Time zone
My application is producing "pallet" records and saving a print_date field which needs to display and report in Australian time zones.
I could of course leave everything in one time zone and be blissfully time zone indifferent but that does not advance my knowledge.
The following is my learnings from trying to make my applicaton TZ aware.
I also want to store some TIME fields in the database and ran into the problem that CakePHP was prepending a date to the time and then causing the time to jump between days even though it was stored as a time in the DB i.e. 14:00
So this is my attempt to get a handle on both DATETIME and TIME data types in CakePHP 4
Set the MySQL Time Zone
Firstly check the MySQL database server is set to use UTC. I have a Ubuntu box so I just ran dpkg-reconfigure tzdata and set the time zone to UTC for the whole system and it pulled it in to MySQL
SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
CakePHP 4 - Set DB Connection and Default Time Zones
Next configure CakePHP 4 as follows
Set the defaultTimezone to UTC
// in config/app.php
// Leave defaultTimezone as UTC
'App' => [
'defaultLocale' => env('APP_DEFAULT_LOCALE', 'en_US'), // you can leave this en_US
// as the defaultLocale can be dymanically changed using middleware (see below)
'defaultTimezone' => env('APP_DEFAULT_TIMEZONE', 'UTC'),
Set the database timezone to UTC
'Datasources' => [
'default' => [
'host' => 'localhost',
'username' => '_your_user_',
'password' => '_your_pass_',
'database' => '_your_db_',
'encoding' => 'utf8mb4',
'timezone' => 'UTC',
'cacheMetadata' => true,
Use LocaleSelectorMiddleware to get correctly formatted Dates & Times on the front end
This isn't strictly related to time zones but with display of dates, times & numbers.
Globally change your application so that a connecting client automatically sets the default locale so their Dates, Times & Numbers look right for their locale
// in src/Application.php
// in the middleware function add this (I did it at the bottom)
// ... other middlewares
$middlewareQueue->add( new LocaleSelectorMiddleware(['*']));
// or specify selected locales
// $middlewareQueue->add( new LocaleSelectorMiddleware(['en_AU', 'en_US']));
Example screenshot of DATETIME values with the defaultLocale set to 'en_US' in app.php without middleware
Once the middleware is configured you will see the correct date formats when connecting with a browser that passes a different locale. It uses the Accept-Language browser header for example Accept-Language: en-AU,en-GB;q=0.9,en;q=0.8
Modify add / edit actions to convert the submitted data to the current timezone of the submitting user
The person interacting with the website will logically be setting dates as their local time that means that before CakePHP saves it to the database you need to tell CakePHP what timezone the submitted time is. Then you need to convert it back to UTC using the setTimezone('UTC') method
// one liner to convert request POST data from localtime to utc
$tzdate = (new FrozenTime($data['tzdate'], 'Australia/Melbourne'))->setTimezone('UTC');
Here is an example of the add method
public function add()
{
$user = $this->Users->newEmptyEntity();
if ($this->request->is('post')) {
$data = $this->request->getData();
$tzdate = new FrozenTime($data['tzdate'], 'Australia/Melbourne');
$data = array_merge($data, [
'tzdate' => $tzdate->setTimezone('UTC')
]);
$user = $this->Users->patchEntity($user, $data);
if ($this->Users->save($user)) {
$this->Flash->success(__('The user has been saved.'));
return $this->redirect(['action' => 'index']);
}
$this->Flash->error(__('The user could not be saved. Please, try again.'));
}
$this->set(compact('user'));
}
Edit method is the same. Grab the submitted date convert the string to a FrozenTime value with the correct timezone
public function edit($id = null)
{
$user = $this->Users->get($id, [
'contain' => [],
]);
if ($this->request->is(['patch', 'post', 'put'])) {
$data = $this->request->getData();
// check the value submitted from the page here
// dd($data);
$tzdate = new FrozenTime($data['tzdate'], 'Australia/Melbourne');
// check the value once it is converted to timezone aware datetime
// dd($tzdate);
$data = array_merge($data, [
'tzdate' => $tzdate->setTimezone('UTC')
]);
$user = $this->Users->patchEntity($user, $data);
if ($this->Users->save($user)) {
$this->Flash->success(__('The user has been saved.'));
The time submitted from the CakePHP frontend looks as follows when you dd($this->request->getData()) - dump and die it in the Controller. Notice how it is a DATETIME string value, there is no time object or time zone
After converting it to a FrozenTime with the time zone of the web user (in this case Australia/Melbourne) you will see this when you dd()
Finally convert it to UTC for saving to the DB
Once the value is converted to the correct timezone and CakePHP performs the ->save() it will store it in the DATETIME field in the datebase as follows
Sadly there is nothing in the MySQL DATETIME field to indicate it is indeed UTC it's just a DATETIME value. You have to know what your application defaultTimezone and MySQL timezone are set to have confidence what the value in the DB is.
Important: Perform thorough testing to make sure what you save and view converts in both directions.
Modify views to display in the correct local time
As DATETIME's are being saved and retreived in UTC in your views you will need to convert them to your local timezone. The following example is me hard coding the timezone in the view.
// templates/Users/index.php
<td><?= $this->Time->i18nFormat($user->tzdate, null, 'invalid', 'Australia/Melbourne'); ?></td>
// templates/Users/view.php
To have dynamic timezones you can add a dynamic timezone field to the user table, set and get it from the PHP Session or see below on how to set the user timezone when loading the TimeHelper.
<td><?= $this->Time->i18nFormat($user->tzdate, null, 'invalid', $timezone); ?></td>
Edit Form Control Changes
When you get the record from the database as UTC and the application defaultTimezone is set to UTC the retrieved records will be in UTC format:
So in each edit form you need to tell the control that despite being given a UTC datetime you want it to display the value in your local timezone:
<?php
echo $this->Form->control('tzdate', [
'timezone' => 'Australia/Melbourne',
'empty' => true]);
Handling TIME field
How to stop CakePHP 4 from murdering time values by plonking a date before them and skewing the time because of timezones.
In config/bootstrap.php tell CakePHP not to bother converting time fields to FrozenTime and treat them as strings
This is a global change for all time columns in the database. See below for an individual column change.
TypeFactory::map('time', StringType::class);
From now on when you retrieve a TIME field it will be returned as a String
Here you can see the tztime field before and after changing the time type to String
But now it is a string you may still want to format it as a time. Again use the TimeHelper and specify the TZ as UTC to make sure it won't adjust it to localtime.
Important: If you make global changes to make the TimeHelper to display with a specific timezone (see outputTimezone below) you may have to individually change the display of some fields:
<td><?= $this->Time->i18nFormat(
$user->tztime,
[\IntlDateFormatter::NONE, \IntlDateFormatter::SHORT],
'invalid'
'UTC'
); ?></td>
In the edit / add form views once you set the time type to the StringClass the controls might become text inputs so you need to specify that the control is a time field to get the nice editing ability back
echo $this->Form->control('tztime',[
'type' => 'time',
'empty' => true]);
// or use the time() method
echo $this->Form->time('tztime',[
'empty' => true]);
Then you can grab the time from the database and make it into a datetime in the correct timezone and convert it back to UTC in the right MySQL format to make your query
<?php
$start_time = '14:28:08';
$tz = "Australia/Melbourne";
$minutes = 720;
$start_date_time = new FrozenTime('2021-04-29' . ' ' . $start_time, $tz);
$end_date_time = $start_date_time->addMinutes($minutes);
# convert to UTC and Mysql format
$start_date_time = $start_date_time->setTimezone('UTC')->format('Y-m-d H:i:s');
$end_date_time = $end_date_time->setTimezone('UTC')->format('Y-m-d H:i:s');
Changing the time type to StringType on just one column
In bootstrap create a map between customtime and the StringType::class then in the Table add the _initializeSchema method and set the specific column type. 'customtime' is something you make up.
// config/bootstrap.php
use Cake\Database\Type\StringType;
TypeFactory::map('customtime', StringType::class);
// in the src/Table/UsersTable.php or whichever table you have the time field in
// add the _intializeSchema method
use Cake\Database\Schema\TableSchemaInterface;
// your other use statements
class UsersTable extends Table
{
protected function _initializeSchema(TableSchemaInterface $schema): TableSchemaInterface
{
$schema->setColumnType('tztime', 'customtime');
return $schema;
}
Globally set the timezone so you don't have to pass it to the TimeHelper each time
This will set the TimeHelper to always display in the 'Australia/Melbourne' timezone by default
// in src/View/AppView.php
class AppView extends View
{
/**
* Initialization hook method.
*
* Use this method to add common initialization code like loading helpers.
*
* e.g. `$this->loadHelper('Html');`
*
* @return void
*/
public function initialize(): void
{
$this->loadHelper('Time', [ 'outputTimezone' => 'Australia/Melbourne' ]);
}
How to dynamically feed the time zone into all your views
In AppController set a timezone or tz variable then reference it in AppView.php when you load the Helper
The benefit to doing a set from AppController is that it will be available to all views
// src/Controller/AppController.php
public function beforeFilter(EventInterface $event)
{
// if user object specifies timezone
// $user = $this->request->getAttribute('identity');
// $tz = $user->get('timezone');
// make it available as a property in all controllers
$this->tz = 'Australia/Melbourne';
// make it available to all views
$this->set('tz' , 'Australia/Melbourne');
}
// src/View/AppView.php
public function initialize(): void
{
$this->loadHelper('Time', [ 'outputTimezone' => $this->get('tz') ]);
}
// templates/Users/view.php
// change the views as follows from the first to the second
// from this
<tr>
<th><?= __('Tzdate') ?></th>
<td><?= h($user->tzdate) ?></td>
</tr>
// to this
// because we are setting outputTimezone it will
// output as, in my case, 'Australia/Melbourne'
<tr>
<th><?= __('Tzdate') ?></th>
<td><?= $this->Time->i18nFormat($user->tzdate); ?></td>
</tr>
// you can also explicitly set the tz by passing it in
<tr>
<th><?= __('Tzdate') ?></th>
<td><?= $this->Time->i18nFormat($user->tzdate, null, 'bad date', $tz); ?></td>
</tr>
0 Comments