[FIXED] How to properly set "0000-00-00 00:00:00" as a DateTime in PHP

Issue

I have a column viewedAt which is a DATETIME and accept NULL values. It's a software restriction to set that column on each new record as 0000-00-00 00:00:00 so I go through the easy way using Symfony and Doctrine as show below:

$entEmail = new Email();
$entEmail->setViewedAt(new \DateTime('0000-00-00 00:00:00'));

But surprise PHP change that date to this -0001-11-30 00:00:00 and SQL mode in MySQL server is set to STRICT so query fails. I have read a lof of topics here as this, this and some other but didn't answer my doubt at all. I have made a few test with PHP alone and I got almost the same result:

$date = new DateTime("0000-00-00 00:00:00", new DateTimeZone('America/New_York'));
echo $date->format('Y-m-d h:i:s');

// outputs
// -0001-11-30 12:00:00

Even with MySQL mode set to STRICT I did test the following query and it works as image below shows:

INSERT INTO emails(`emails_category`, `deliveredAt`, `viewedAt`, `data`, `createdAt`, `updatedAt`, `reps_id`, `targets_id`) VALUES ("sent","2015-10-29 06:08:25","0000-00-00 00:00:00",null,"2015-10-29 06:08:25","2015-10-29 06:08:25","005800000058eYcAAI","0018000001GO8omAAD")

So viewedAt is accepting 0000-00-00 00:00:00 that value even if is not valid (I think it's kind of NULL or so)

How I can fix this? Is there any work around? What did you suggest me on this specific case where this is a software requirement and can't be changed?

I'm using Symfony 2.7.6, Doctrine 2.5.2 and PHP 5.5.30

Solution

Your architecture is wrong to begin with. The problem is not setting the date itself, which is so obviously invalid that both MySQL and PHP are right to reject it, as there is no year 0 and no day 0 of a month 0, and the output you see is just the correction to a sort-of-valid date (it's 1 year, 1 month and 1 day before 01/01/01). But you're also just missing the point that Doctrine abstracts this away if you just do it right:

$entEmail = new Email();
$entEmail->setViewedAt(null);

Doctrine will now happily put NULL in the database column as it should be.

Answered By - Niels Keurentjes

Answer Checked By - David Marino (PHPFixing Volunteer)