1

I have a script that daily do bulk import of log files to a "my_logs_db.logs" Table that will be queried by CakePHP3 app. In the "logs" table I have a datetime field named 'date_time'. Imported Date for 'date_time' field refers to system timezone that is set to 'Europe/Paris' and not 'UTC'

What I want is to display date_time field with the application timezone, for example : 'Indian/Reunion' (UTC+4).

So I tried this :

I set timezone to SYSTEM for on the data source config/app.php. With this value MySQL should work with system time that is set to Europe/Paris:

'my_logs_db' => [
    'className' => 'Cake\Database\Connection',
    'driver' => 'Cake\Database\Driver\Mysql',
    'persistent' => false,
    'host' => 'localhost',
    //'port' => 'non_standard_port_number',
    'username' => 'user',
    'password' => '*********',
    'database' => 'my_logs_db',
    'encoding' => 'utf8',
    'timezone' => 'SYSTEM',
    'flags' => [],
    'cacheMetadata' => true,
    'log' => false,
    'quoteIdentifiers' => false,
    //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],
    'url' => env('DATABASE_URL', null),
],

Somewhere in my Controller I set the timezone to use for display : 

date_default_timezone_set('Indian/Reunion')

Somewhere in my Controller the SQL query is :

$logs = $this->Logs->find();
$logs->select([
        'id',
        'date_time',
        'client_ip',
        'domain',
        ]);

When I debug date_time, I get this :

object(Cake\I18n\FrozenTime) {

    'time' => '2019-02-28T01:56:58+00:00',
    'timezone' => 'UTC',
    'fixedNowTime' => false

}

And the displayed date is 2019-02-28 05:56:58 (UTC+4).

This is not the right time to display.

The date I want to display is 2019-02-28 04:56:58 (Europe/Paris+3)

So the output I want if I debug date_time must be like :

object(Cake\I18n\FrozenTime) {

    'time' => '2019-02-28T01:56:58+00:00',
    'timezone' => 'Europe/Paris',
    'fixedNowTime' => false

}

The datetime I want should be unchanged and the timezone should be set to 'Europe/Paris'

For "FrozenTime" how to set timezone to 'Europe/Paris' and not 'UTC' ? Or do I have to add something in my SQL query ?

Note that I don't want to change system timezone to UTC as work arround.

Siva
  • 1,481
  • 1
  • 18
  • 29
benoit974
  • 35
  • 6
  • Did you `pre-heat` the oven or bake the cake? ... ha ha. Just having some fun with the Cake guys. Check you `my.cnf` file. if you use the Mysql functions `NOW()` ect. it has it's own timezone settings for that. See this https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql – ArtisticPhoenix Mar 22 '19 at 12:23
  • `select NOW()` give the date GMT+1 (so Europe/Paris). I have tried `set time_zone = '+01:00';'` change nothing FrozenTime timezone still UTC. – benoit974 Mar 24 '19 at 03:23
  • I found a work arround that consist to convert date into UTC : `$logs->select(['date_time' => $dnslogs->func()->convert_tz(['date_time' => 'identifier','+01:00','+00:00'])]);` But my question about FrozenTime still open. – benoit974 Mar 24 '19 at 05:15
  • CakePHP's datetime related database type classes do not yet support timezones for data read from the database, currently it's treated as being in the current PHP timezone. Support for this is going to be introduced in 4.x, see **https://github.com/cakephp/cakephp/pull/13005**. You could implemented this in a [**custom/extended database type**](https://book.cakephp.org/3.0/en/orm/database-basics.html#adding-custom-types) if you need it now. – ndm Mar 24 '19 at 16:24

0 Answers0