Cron times in the database have a double timezone correction

[AntonEvers]

The cron schedule times in the database are local datetimes and not UTC whereas the other datetimes in the system are saved as UTC in the database.

Is this something worth changing?

[melnikovi]

For columns of type TIMESTAMP MySQL returns date formatted according to timezone from config (server timezone by default). After you connect to the database, run query SET time_zone = '+0:00' to have it return dates in UTC. Let me know if this doesn't help

[piotrekkaminski]

@melnikovi it's rather an inconsistency issue. All dates should be saved in one format and displayed accordingly to user preference/needs. @ajpevers do you have any specific issues resulting out this - as in promotions getting live to early etc?

[AntonEvers]

I have my env.php setup with 'initStatements' => 'SET NAMES utf8, time_zone="+00:00";', so that works just fine.

No there are no specific issues as of yet. But as an extension developer I foresee problems here. You will encounter unexpected behaviour if a plugin developer decides to work with MySQL date functions.

[melnikovi]

@ajpevers how do you know that cron schedule in database is not in UTC? Also, you don't need to use initStatements in env.php to set timezone for connection, it's handled by framework.

[kitsor]

@melnikovi
Since default timezone has been hardcoded into bootrap.php
https://github.com/magento/magento2/blob/develop/app/bootstrap.php#L49

date_default_timezone_set('UTC');

You have big issue with getting scoped timestamp in Timezone.php#L209

...
@date_default_timezone_set($timezone);
$date = date('Y-m-d H:i:s');
@date_default_timezone_set($currentTimezone);
return strtotime($date);

You are getting STRING dateTime in scoped timezone, then reverting into default (UTC) and calculating TIMESTAMP from string ("scoped") using default (UTC) timezone.

Because of this all cron's $currentTime is incorrect.
In my case I see -08:00 in cron_schedule instead of correct -04:00 (EDT)

So fix should be like this:

    @date_default_timezone_set($timezone);
    $date = date('Y-m-d H:i:s');
    $retVal = strtotime($date);
    @date_default_timezone_set($currentTimezone);
    //return strtotime($date);
    return $retVal;

And... Since we are talking about cron..
I've found another odd code.
According to devDoc cron should be executed with defined .ini, but you are running child processes w/o any .ini: ProcessCronQueueObserver.php#L173

...
$phpPath = $this->phpExecutableFinder->find() ?: 'php';
...
$phpPath . ' %s cron:run --group=' . $groupId . ' --' . CLI::INPUT_KEY_BOOTSTRAP . '='

Is it correct?

[AntonEvers]

@kitsor is correct, I also saw cron times with 4 hours difference. Some of my dev servers have php's time zone at UTC and mysql's global time zone at Europe/Amsterdam. To prevent any differences there I set the mysql timezone with the init statement. This had the result of only 2 hours difference in the cron times, but still the cron times are saved as Amsterdam time in a UTC database from UTC php code.
So effectively the Amsterdam time becomes UTC time in the DB. But without the time correction.

In a multi time zone system like Magento all times should be - in my opinion - saved in UTC and displayed according to the viewers time zone where appropriate.

[melnikovi]

@ajpevers you don't need to use initStatements, as Magento sets timezone for connection in \Magento\Framework\DB\Adapter\Pdo\Mysql::_connect. We also set timezone in PHP in app/bootstrap.php.

Magento works with dates as you described. All dates stored in UTC and being formatted to user's timezone on frontend. Created internal task MAGETWO-54382 to double check if cron stores time in UTC.

When you connect to the database from terminal or MySQLWorkbench, do you set timezone for connection? Because if not, MySQL will return you dates with offset.

[AntonEvers]

Hi @melnikovi thanks for the very quick response.
I do think I see the times with correction in my terminal. However, there is a difference between the times on an order for instance and the times in the cron_schedule table:

Just placed an order, time I see in the DB: 2016-06-14 16:10:46
Just opened cron_schedule, last job finished at: 2016-06-14 18:11:13
Local time here: 2016-06-14 16:10:46 CET
UTC: 2016-06-14 14:10:46 UTC

So in any case it can never be that the cron times in the DB are ahead of my time in CET. If anything I would expect them to be equal or 2 hours behind.

So I think the cron times are not UTC because they are different than the sales entity times.

I removed the timezone from the env.php init statements and that does indeed not make a difference. Thanks for that tip.

[kitsor]

@ajpevers Use my solution.
You don't need to use initStatements.
The core issue in Timezone.php on calculating scopeTimeStamp.

Just make a small fix:

$timezone = $this->_scopeConfig->getValue($this->getDefaultTimezonePath(), $this->_scopeType, $scope);
$currentTimezone = @date_default_timezone_get();
@date_default_timezone_set($timezone);
$date = date('Y-m-d H:i:s');
+ $retVal = strtotime($date);
@date_default_timezone_set($currentTimezone);
- //return strtotime($date);
+ return $retVal;

It works for me as expected.

[AntonEvers]

Thanks @kitsor,

But my goal is not to patch my installation, it's to make Magento a healthier product. So if this solution works, and does not have a negative impact on other places in the installation, can you make it a pull request?

[AntonEvers]

@wert2all Can I turn this into a bug report instead of a question? The datetime in the cron_schedule contains another time zone than the datetime in sales tables.

[AntonEvers]

@wert2all friendly reminder