MySQL NO_ZERO_DATE and minimum timestamp default

It is a good idea to use MySQL with sql-mode = NO_ZERO_DATE,NO_ZERO_IN_DATE (among other options)

If the NO_ZERO_DATE flag is not set, MySQL will allow ‘0000-00-00’ in date and datetime fields. This also allows the silent conversion of invalid dates to this zero date as well.
You probably don’t want to find zero dates being allowed into your database if there is a problem with any of the data validation you are carrying out before inserting/updating data.

However you have to use something as a default value when creating a table that uses date and datetime fields. My recommendation is to go for the minimum allowed value: ‘1000-01-01’ for a date field and ‘1000-01-01 00:00:00’ for a datetime, if the field is a timestamp then this is ‘1970-01-01 00:00:01’.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.