mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00

Question

I have a column created as

`date_start` datetime NOT NULL DEFAULT '1970-01-01'

However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ?

Answer 1

datetime means your column stores date and time in format Y-m-d H:i:s (0000-00-00 00:00:00).

date means your column stores just date in format Y-m-d (0000-00-00).

Answer 2

NULL and 'a blank entry' are not always treated the same in MySql. In the case of the datetime data type, blank entries are automatically converted to 0000-00-00 00:00:00. You can think of this as a form of typecasting, where a blank gets typecast to zero.

In your data file, try replacing all blank dates with the DEFAULT keyword.

Answer 3

If you don't specify a value when inserting data, the default is used, but when you insert the data from the CSV file it does specify a value for the field, so the default is not used.

A blank entry is an invalid value for a datetime field, so the value 0000-00-00 00:00:00 is used instead, just as with any value that can't be parsed to a valid date (or a partial date).

Answer 4

The MySQL SQL mode by default allows zero dates.

My belief is that the LOAD DATA INFILE command is reading the blank position intended to be a DATETIME, and automatically using a zero date before the insertion. This would explain why your default constraint isn't being applied - the value isn't null when being inserted.

I think you have two options:

1. Update all the blanks in your CSV from ", ," to ", NULL, " or ", DEFAULT, " so they'll be interpreted correctly
2. Change the SQL mode: SET SQL_MODE='NO_ZERO_DATE'

Answer 5

MySQL has the annoying (in my opinion) behaviour that under some circumstances, if given an invalid value to store in a column, it will store a predefined "error value" instead. For example, in a date or datetime context, it will store the "zero date" you have noticed; for an ENUM column it will store '' (corresponds to 0 in a numeric context) as an error value.

Although it is inelegant, you might try doing the import and allowing the invalid zero dates to be inserted, and then issuing

UPDATE
    table_name
SET
    date_start = '1970-01-01 00:00:00'
WHERE
    date_start = '0000-00-00 00:00:00'