CODEWITHSUNDEEP
phpmysqlsymfonydoctrinetimezone
Guillaume
Guillaume

Reputation: 172

Doctrine: date timezone not preserved after creating an entity

I create an entity with datetime set to 08:50:35 and timezone "+00:00". I reload the entity and the datetime is 08:50:35 with timezone "+02:00". The timezone changed !

Using Symfony 3.4 with Doctrine, PHP 7.2, MySQL 5.7, and Ubuntu 16.4 or Debian 8.

I use an external library which uses the DateTime::createFromFormat from timestamp instruction. So it would be nice to keep it.

Here is the code I reduce the problem to:

// Create entity with date.
$report = new CronReport();
$date = \DateTime::createFromFormat('U', '1534755035');
var_dump($date); // object(DateTime)#536 (3) {
    // ["date"]=> string(26) "2018-08-20 08:50:35.000000" 
    // ["timezone_type"]=> int(1)
    // ["timezone"]=> string(6) "+00:00" }

$report->setRunAt($date);
$report->setRunTime(0);
$report->setExitCode(0);
$report->setOutput('');
$this->em->persist($report);
$this->em->flush();

// Reload Entity.
$id = $report->getId();
$this->em->detach($report);
$report = this->em->getRepository('CronCronBundle:CronReport')->find($id);

$date = $report->getRunAt();
var_dump($date); // object(DateTime)#550 (3) {
    // ["date"]=> string(26) "2018-08-20 08:50:35.000000"
    // ["timezone_type"]=> int(3)
    // ["timezone"]=> string(12) "Europe/Paris" }

The table creation declaration :

CREATE TABLE `cron_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  `run_at` datetime NOT NULL,
  `run_time` double NOT NULL,
  `exit_code` int(11) NOT NULL,
  `output` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_B6C6A7F5BE04EA9` (`job_id`),
  CONSTRAINT `FK_B6C6A7F5BE04EA9` FOREIGN KEY (`job_id`) REFERENCES `cron_job` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

What's wrong? How to set an entity datetime and preserve its timezone?

Upvotes: 4

Views: 6403

Answers (1)

lxg
lxg

Reputation: 13107

The problem seems to be that your PHP timezone is set to a different value than UTC, namely Europe/Paris. This is either configured in your phi.ini or set at runtime with date_default_timezone_set("Europe/Paris") or ini_set("date.timezone", "Europe/Paris"). You can check the current value by running php -i | grep timezone (for CLI) or by var_dump(ini_get("date.timezone")) at runtime.

Handling DateTime objects with Doctrine in combination with MySQL is a topic of it’s own. In a nutshell, your database doesn’t care about the timezone of values stored in datetime columns. When you pass a DateTime object to Doctrine, it will store the time but not the timezone in the database. When Doctrine retrieves the value again, it creates the DateTime object with the value from the database, but in your PHP’s default timezone.

There are several solutions for this:

  1. You might consider using a column type with timezone support and use datetimetz. Note that this does NOT work with MySQL as underlying database.

  2. If you have only one timezone in your app, set it as default timezone in PHP and convert all DateTime objects to that timezone before persisting.

  3. If you have multiple timezones, you must store the timezone in the database together with the datetime value. In this case, you should set your internal timezone to UTC, and convert DateTime objects to UTC before persisting. Then, when retrieving the DateTime from the entity, you must set the correct timezone again. Or you might find it easier to store the time as UNIX timestamp directly (which is UTC by definition) and just create DateTime instances on the fly. Depends on your use case.

Read the article about DateTime handing, written by the Doctrine team, for an extended discussion with pros and cons to learn more: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/working-with-datetime.html#handling-different-timezones-with-the-datetime-type

Upvotes: 4

Related Questions