5

When my MySQl dates are null, PHP returns them as '-0001-11-30 00:00:00'.

I know this is because I am using Europe timezone but how can I handle this?

I keep getting:

InvalidArgumentException in Carbon.php line 425:
Unexpected data found.
in Carbon.php line 425
at Carbon::createFromFormat('Y-m-d H:i:s', '-0001-11-30 00:00:00') in Model.php line 2915

How can I fix this?

Mick

NB: This is different from : How do you explain the result for a new \DateTime('0000-00-00 00:00:00')?

It is different because I am asking what I am supposed to do about it.

Community
  • 1
  • 1
Mick
  • 1,401
  • 4
  • 23
  • 40
  • 1
    Possible duplicate of [How do you explain the result for a new \DateTime('0000-00-00 00:00:00')?](http://stackoverflow.com/questions/10450644/how-do-you-explain-the-result-for-a-new-datetime0000-00-00-000000) – Dylan Su Mar 24 '16 at 10:53
  • 1
    It's nothing to do with timezones (European or otherwise), and all to do with how PHP's DateTime will interpret an empty date value – Mark Baker Mar 24 '16 at 10:53
  • Great, so how do I prevent the error? – Mick Mar 24 '16 at 11:01
  • I guess that you will have to write a function that recognizes null values *based on the timezone* and set em to null – Phate01 Mar 24 '16 at 11:08

1 Answers1

5

Basing on this great answer: https://stackoverflow.com/a/19075291/4499267 at point 8) it says that

MySQL stores "0000-00-00 00:00:00" if you set a datetime column as NULL, unless you specifically set the column to allow null when you create it.

So that might be the solution, to create the table with a nullable datetime column

Community
  • 1
  • 1
Phate01
  • 2,499
  • 2
  • 30
  • 55
  • That looks like it should work, but I still get the same error, when I try to turn the collection into an array: in Carbon.php line 425 at Carbon::createFromFormat('Y-m-d H:i:s', '-0001-11-30 00:00:00') in Model.php line 2915 at Model->asDateTime('-0001-11-30 00:00:00') in Model.php line 2435 at Model->attributesToArray() in Model.php line 2412 at Model->toArray() in Collection.php line 969 – Mick Mar 24 '16 at 12:03
  • Could you post the script that generates the error? Try also to `var_dump()` the value that MySQL returns – Phate01 Mar 24 '16 at 13:18
  • Just: //products is an Elastiquentcollection and needs to be an array $arrProducts = $products->toArray(); – Mick Mar 24 '16 at 14:01
  • I got it. If your date column didn't allow nulls, mysql stored them as `0000-00-00 00:00:00`. Now if you changed it in order to allow nulls, those value are still `0000-00-00 00:00:00`. You'll have to manually set them to `NULL` – Phate01 Mar 24 '16 at 14:06
  • I deleted all the rows, amended the db, then added the rows again. However! I am using Elastic Search and I had to delete the index and then add all the records again. Looks like it is working! Thanks – Mick Mar 24 '16 at 14:18
  • Thanks for your help. I will avoid this problem in future by always allowing nulls. – Mick Mar 24 '16 at 14:37