Better to use zero-date '0000-00-00 00:00:00' or NULL in MySQL?

Question

Is it a better practice to use default date '0000-00-00 00:00:00' or NULL on a MySQL database?

I have read best to use default date '0000-00-00 00:00:00' for the reason of calculations

i.e. >than a date less than a date.

Also on time best to store 00:00 or NULL if time is not known.

Answer 1

MySQL 5.7 defaults to disallowing 0000-00-00 00:00:00 as default value, unless you set sql_mode = ''. It seems MySQL wants you to use NULL as default value.

Answer 2

You should use NULL because 0000-00-00 00:00:00 is not a valid date. If you use libraries such as moment.js or Carbon to manage date, they both know how to deal with NULL dates.

The MySQL configuration should always be set to:

SET sql_mode = 'NO_ZERO_DATE';

unless you have to deal with an old database.

That said, according to MySQL Documentation:

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

If you are on Windows:

“Zero” date or time values used through Connector/ODBC are converted automatically to NULL because ODBC cannot handle such values.

An additional point to consider is: what is the true meaning of a zero date?

• Does '0000-00-00' < '2018-12-12'?
• Does '0000-00-00' means 'unknown'?
• Does '0000-00-00' means 'null'?

Unless your answer is the first one and you need to do comparisons with null dates, using a NULL value is always less confusing.