'How to properly set "0000-00-00 00:00:00" as a DateTime in PHP

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")

enter image description here

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

phpsymfonydatetimedoctrine-orm


Solution 1:[1]

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.

Solution 2:[2]

$Date = date('Y-m-d H:i:s');

Isn't it easier to use the good old fashioned way at times?

Solution 3:[3]

It depends on your database field. In MySQL, a TIMESTAMP field is post 1970. You should make the field allow null if you want it nullable. Even if you use the DATE field, it is supposed to be post 1000AD.

If you are going to put a real timestamp you can use the following:

date_default_timezone_set('America/New_York');
$date = date('Y-m-d H:i:s');

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

OR ( if you want it more concise )

date_default_timezone_set('America/New_York');

$entEmail = new Email();
$entEmail->setViewedAt( date('Y-m-d H:i:s') );

Solution 4:[4]

Your problem springs from the fact that MySQL offers three types of values for datetime:

  1. a valid date "2022-04-18 16:42:45"
  2. a null value, if your design allows null
  3. an unknown date value 0000-00-00 00:00:00

In the PHP paradigm there are only valid dates or null values. You could even dismiss null values. Your design would need to decide what to do with (3) when changing values from the MySQLto the PHP paradigm. Should it change to null or do some error (exception) handling? In the opposite direction there is less of a problem. Another way would be treating negative years in PHP as special case as well.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 James P
Solution 3 lxg
Solution 4 theking2

Related Questions