Question 293230 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Column has zero default value: 0000-00-00 00:00:00

I’m upgrading from MySql 5.x to 8.x and one of the MySQL updater checks states:

Warning: By default zero date/datetime/timestamp values are no longer allowed
in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
SQL_MODE by default. These modes should be used with strict mode as they will
be merged with strict mode in a future release. If you do not include these
modes in your SQL_MODE setting, you are able to insert
date/datetime/timestamp values that contain zeros. It is strongly advised to
replace zero values with valid ones, as they may not work correctly in the
future.

On the last sentence:

"It is strongly advised to
replace zero values with valid ones, as they may not work correctly in the
future."

What do i change them to then? Perhaps 1000-01-01 00:00:00?

What do i set the default date to? Also 1000-01-01 00:00:00?

And what would the syntax be?

ALTER TABLE {table} ADD {column} datetime not null default '1000-01-01 00:00:00';

I’m a bit confused. Thanks!

Tags:

3

Answers


  1. 0

    The best "value" to use if there is no date on a given row is NULL. That’s the meaning of NULL — for a missing or inapplicable value.

    But if you have defined the column NOT NULL, obviously you can’t store a NULL in that column. You can define the column with no DEFAULT. If a column has no default but has the NOT NULL option, it means you must specify a value when you INSERT a row.

    But if you are adding a column to a table that already has rows, you can’t add it as NOT NULL with no DEFAULT.

    You must first add the column as a nullable column:

    ALTER TABLE {table} ADD {column} DATETIME NULL;

    Then UPDATE the rows until no row has a NULL:

    UPDATE {table} SET {column} = {some value} WHERE ...;

    Then finally modify the column to be NOT NULL:

    ALTER TABLE {table} MODIFY COLUMN {column} DATETIME NOT NULL;
    Login or Signup to reply.
  2. 0

    If your column is not null, and allowing nulls won’t break things, consider making removing not null from the column definition and store nulls.

    If you can’t store nulls, for example if you have start_date and end_date columns and you have queries like where some_date between start_date and end_date that won’t work if there are nulls in either date column, chose a date "long in the past" that your app code can handle.

    A reasonable choice is 1900-01-01 00:00:00, because most datetime libraries from most app languages can parse that yet it’s well in the past.

    If you have app code/libraries that are less adaptable, linux epoch is a good choice: 1970-01-01 00:00:00.

    If you want something that visually stands out, 1111-11-11 11:11:11 is a good choice.

    Login or Signup to reply.
  3. 0

    WordPress’s table definitions contain zeros for date defaults. (Sure, it would be nice if WordPress updated their definitions to something more in line with 2023 thinking about date validity, but YOU tell all those millions upon millions of site owners to do that. I’M not telling them that.)

    If you issue this SQL command at the beginning of every session you get the old behavior back. (WordPress code does this.)

    SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

    I offer this because it may be the best way to get your MySql / MariaDb upgrade job done without having to change your table definitions and retest your code. In my opinion the gains to be had by upgrading are worth the tech debt you incur with that sql_mode hack.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search