4

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

Community
  • 1
  • 1
ReynierPM
  • 17,594
  • 53
  • 193
  • 363
  • 1
    `$entEmail->setViewedAt("0000-00-00 00:00:00");` doesn't work? This is certainly a timezone issue. – miken32 Oct 29 '15 at 21:57
  • @miken32 why should be a timezone issue? Did you see the test below where I set timezone properly and get the same as before using Symfony and Doctrine? – ReynierPM Oct 29 '15 at 21:58
  • Hmm, perhaps a calendar issue? Adjusting for Julian vs Gregorian calendar because of the old date? – miken32 Oct 29 '15 at 22:00

4 Answers4

9

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.

Niels Keurentjes
  • 41,402
  • 9
  • 98
  • 136
  • MySQL is quite happy to use "0000-00-00 00:00:00" as a date. – miken32 Oct 29 '15 at 22:02
  • 1
    As @miken32 says `0000-00-00 00:00:00` is a *valid* date since MySQL in `STRICT` mode accept it (I have add this info to the main post) so the arch is not bad at all. The problem is PHP changing date to `-0001-11-30 00:00:00` and that is what I am trying to prevent – ReynierPM Oct 29 '15 at 22:07
  • 3
    Sorry to say it, but "MySQL accepts it" is never a valid reason to call something 'good'. MySQL also accepts incomplete `GROUP BY` clauses. Hell, MySQL lets you insert strings in integer fields and vice versa. Try doing either of these things in MSSQL or Oracle and watch the exceptions fly. What you're trying to do is just plain wrong, and that's the reason PHP's `\DateTime` object doesn't accept it. And neither does Doctrine as it has to work with all these DBMS's. MySQL just accepts it for legacy reasons. The code you're writing is fundamentally broken and unportable. But be my guest. – Niels Keurentjes Oct 29 '15 at 22:21
  • Actually following up on 'MySQL brilliant behaviour' - the easiest way to get `0000-00-00 00:00:00` into a `datetime` field is just to put an invalid string in there. MySQL changes anything that it can't translate to datetime in any possible way to the zero date. [MySQL is a really really really bad DBMS](http://grimoire.ca/mysql/choose-something-else). – Niels Keurentjes Oct 29 '15 at 22:24
3
$Date = date('Y-m-d H:i:s');

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

James P
  • 101
  • 10
2

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') );
lxg
  • 12,375
  • 12
  • 51
  • 73
Derek Kier
  • 322
  • 3
  • 5
0

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.

theking2
  • 2,174
  • 1
  • 27
  • 36