Doctrine ORM: NULL to DateTime conversion error

Question

I use PHP7, Symfony 2.8 andDoctrine ORM 2.5. I have an entity with a datetime field:

 /** @ORM\Column(name="data_ordine", type="datetime", nullable=true) */
private $dataOrdine;

/**
 * @param mixed $dataOrdine
 */
public function setDataOrdine($dataOrdine = null)
{
    $this->dataOrdine = $dataOrdine;
}

/**
 * @return mixed
 */
public function getDataOrdine()
{
    return $this->dataOrdine;
}

When i try to get the $dataOrdine field ($ordine->getDataOrdine()) of a persisted entity on MySQL database if the dataOrdine column is NULL i got:

object(DateTime)#551 (3) {
  ["date"]=>
  string(27) "-0001-11-30 00:00:00.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(13) "Europe/Berlin"
}

instead of NULL

Answer 1

I ran into the same issue some time ago. As @goto said your column is not coming as NULL because new DateTime(NULL) is a valid statement.

As this is exactly what happened to me I am entirely sure that the value for the problematic row is coming as 0000-00-00 00:00:00 which makes new DateTime('0000-00-00 00:00:00') to return the wrong date.

$null_date = new DateTime(null);
$wrong_date = new DateTime('0000-00-00 00:00:00');

var_dump($null_date);
var_dump($wrong_date);

object(DateTime)#1 (3) {
  ["date"]=>
  string(26) "2018-01-17 13:22:01.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(10) "US/Pacific"
}

object(DateTime)#2 (3) {
  ["date"]=>
  string(27) "-0001-11-30 00:00:00.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(10) "US/Pacific"
}

I've got a workaround which I didn't like at all and instead of what is posted and accepted as an answer I made the DBA to no allow NULL|invalid dates at those columns.

More info at: How to handle default values for a DateTime type in Doctrine2 SELECT query?

Answer 2

Some clients show null for 0000-00-00 00:00:00 values. So check via the command line client to be sure (CLI shows correct values). And then just update to null where the value is 0000-00-00 00:00:00