It can be useful to allow the default value of a MySQL date / datetime column to be set to zero, it makes checking if it’s initialised simple and saves you having to deal with pesky null values. It also allows you to set the column as NOT NULL. However, from version 5.6 onwards MySQL has STRICT_MODE for queries turned on, as well NO_ZERO_IN_DATE mode turned on. If you try to create new column with something like:

`some_field` datetime DEFAULT ‘0000-00-00 00:00:00’

You’ll see this error:

Invalid default value for ‘some_field’

This is pesky if you’ve got code written for earlier versions of MySQL and you don’t want to go back and re-write it all to check for NULL instead of zero. Turning these things off is pretty simple. On Ubuntu I created a file in /etc/mysql/conf.d/ called disable_strict_mode.cnf. The file contents are shown below:

[mysqld]
sql_mode=IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then restart MySQL with sudo service mysql restart and you’ll be able to create date and datetime fields with a zero default value.