I want to save a time reference in database which date part should be always 0000-00-00.

I have below code with me.

$entity = Mage::getModel('my_entity_reference')->load(1);
$entity->setCloseTime('0000-00-00 05:05:05');

But this gives me an entry 0000-00-00 00:00:00 for close_time column. Please note close_time field is of type DATETIME.

If I try below code :

$entity = Mage::getModel('my_entity_reference')->load(1);
$entity->setCloseTime('2016-09-09 05:05:05');

This gives me a result correctly as 2016-09-09 05:05:05 for the column close_time.

This means we cannot use 0000-00-00 as Y-m-d while we perform a save. So,

How can I specify year, month, date as 0000-00-00 in Magento and save it ?


AFAIK this is a MySQL validation issue.

Such date 0000-00-00 never existed in our timeline right ?

  • 0000 is a valid year
  • but 0 isn't a valid month
  • and the other 0 isn't a valid day either

A valid date could be: 0000-01-01 for example (I haven't tested it I ain't even sure this would work)

As you don't store real dates, I reckon your best bet would be to change the column type from DATETIME to another type like varchar/text.

| improve this answer | |
  • MySQL validation issue : If this is the case, then direct sql update should not work right ? I tried that and it worked. Secondly, 0000-01-01 worked and that sounds good. I also agree with the solution that you have given (change date type) – Rajeev K Tomy Nov 23 '16 at 10:07
  • But with datetime, it seems that we cannot save such date 0000-00-00. Reason is explaining here : stackoverflow.com/a/27127390 – Rajeev K Tomy Nov 23 '16 at 10:09
  • @RajeevKTomy well yeah this SO answer says the exact same thing as I said specific date "0000-00-00:00:00:00" is literary impossible and historically incorrect. – Raphael at Digital Pianism Nov 23 '16 at 10:10
  • Yes got it. I thought this is happening due to some internal Magento validations. With this answer and the referenced stackoverflow link, we can conclude that this is an impossible thing to achieve and Magento is not a culprit for this Magic. – Rajeev K Tomy Nov 23 '16 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.