How to handle default values for a DateTime type in Doctrine2 SELECT query?

Question

I have the following Doctrine2 entity:

<?php

use Doctrine\ORM\Mapping as ORM;
use Gedmo\Timestampable\Traits\TimestampableEntity;
/**
 * @ORM\Entity
 */
class Account
{
    use TimestampableEntity;

    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\Column(type="integer", unique=true, nullable=false)
     */
    private $id;
}

As you can see I am using Gedmo Timestampable with the Trait provided by the bundle. It's working fine.

The problem happen when I fetch the data and the column updated_at is coming as NULL 0000-00-00 00:00:00. For such case the DateTime object is translated into an invalid date as shown below:

‌array (
  'RowId' => 26944,
  'synchflag' => 1,
  'updatedAt' => 
  DateTime::__set_state(array(
     'date' => '-0001-11-30 00:00:00.000000',
     'timezone_type' => 3,
     'timezone' => 'UTC',
  )),
)

I did check docs but there isn't anything helpful (or at least I didn't found it if you do let me know)

What is the right way to deal with this?

EDIT:

Maybe the right question here should be: if updated_at is coming as 0000-00-00 00:00:00 how do I turn it into NOW()? As you may notice the getUpdatedAt() return a DateTime based on the fetched data. Is there any event for SELECT statement?

Answer 1

As the problem is that the database already have those invalid values, you can create your own doctrine datetime type to make the conversion to null when reading that invalid value from the database:

<?php
namespace AppBundle\Doctrine\DBAL;

use DateTimeZone;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\ConversionException;
use Doctrine\DBAL\Types\DateTimeType;

class MyDateTimeType extends DateTimeType
{


   public function convertToPHPValue($value, AbstractPlatform $platform)
   {
       if (null === $value || $value instanceof \DateTime) {
           return $value;
       }

       if ('0000-00-00 00:00:00' === $value) {
          return null;
       }

       return parent::convertToPHPValue($value, $platform);
   }
}

And register this custom type in doctrine configuration:

doctrine:
   dbal:
       types:
           datetime: AppBundle\Doctrine\DBAL\MyDateTimeType

Note: you can enhanced this code using regexp and/or datetime validity checking instead of a simple comparison