MySQL Invalid default value for timestamp when no default value is specified.

Question

Take a look at the following sql.

CREATE SCHEMA IF NOT EXISTS `scheduler`; USE `scheduler` ; CREATE TABLE IF NOT EXISTS `scheduler`.`JobHistory` ( `Id` INT NOT NULL AUTO_INCREMENT, `Job` INT NOT NULL, `StartTime` TIMESTAMP NOT NULL, `FinishTime` TIMESTAMP NOT NULL, PRIMARY KEY (`Id`), INDEX `fk_JobHistory_Job_idx` (`Job` ASC)); 

It throws ErrorCode: 1067. Invalid default value for 'Finish Time' But I do not give a ErrorCode: 1067. Invalid default value for 'Finish Time' end time, there is also another StartTime time StartTime that is exactly the same and I get no exceptions for this.

Answer 1

Although @jsnplank is right that timestamps are handled differently, and you should consider using the datetime data type for these two specific columns, it cannot explain the error message.

The error message is most likely the result of a combination of how mysql handles the timestamp fields when the default value is not set, and setting your operating mode in sql.

• You define both columns of the timestamp as non-zero, without any specific default value. This means that the default value for the 1st timestamp column will be current_timestamp () and will also be updated to current_timestamp () whenever the record changes. This is why the 1st timestamp field does not generate an error message, regardless of which of the 2 is the first.

  However, the default value for the second non-zero mark time value will be "0000-00-00 00:00:00" unless you explicitly specify a default value.

  See this blog post for more details .

• Perhaps no_zero_date sql mode is also enabled on your server either explicitly or as part of sql strict mode. This sql mode generates an error if you want to set "0000-00-00 00:00:00" as the default value or want to insert this value in any date field.

Thus, you can use the timestamp data type in your table, but make the second either zero, or specify 0 or any valid date (for example, an epoch) as an explicit default value.

Since you mark start and end dates with these fields, it is recommended that you use datetime instead of the timestamp name, since a data type might be a good idea.

Answer 2

You must use the DATETIME data types for the StartTime and FinishTime columns. TIMESTAMPS have a very specific use. See http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

Answer 3

The link to the 2000 article does not help much, as much has already changed since then and may no longer be true. As already mentioned in other related issues, TIMESTAMP values ​​refer to a specific point in time relative to January 1, 1970 in UTC. DATETIME values, in contrast, simply store some date and time without reference to any time. They are more like the displayed values, the clock on the wall that shows you some kind of value. 2018-01-12 14:00:00 may be different times in different time zones if you want to track when something happened.

TIMESTAMP, on the other hand, is always stored as UTC, and when read or used in the datetime function, it is automatically converted back to the default time zone of the connection or database. Therefore, when your connection is set to +02: 00, the actual value that will be stored in the TIMESTAMP column will be 2018-01-12 12:00:00 instead of 2018-01-12 14:00:00 . When you then read the column with the +05: 00 connection, you will see 2018-01-12 17:00:00 . The DATETIME value will always remain at 2018-01-12 14:00:00 no matter what time zone is set for the database or connection.

So for tracking when something happened or when something happens / should happen, TIMESTAMP is the way to go. If you just want to keep a fixed time regardless of the time zone, use DATETIME (for example, users will receive an email at 2 a.m., since 2 a.m. is the same for everyone).