9

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 is throwing ErrorCode: 1067. Invalid default value for 'Finish Time' But I'm not giving any default value for finish time, also there is another time stamp StartTime which is exactly same and I'm not getting any exception for that one.

Thomas
  • 4,119
  • 2
  • 33
  • 49
  • 1
    Interestingly if I remove either the StartTime column or the FinishTime column, it will work fine. Looks like MySQL is not allowing a second time stamp without default value. – Thomas Feb 06 '16 at 04:24

3 Answers3

11

Although @jsnplank is right that timestamps are treated differently and you should consider using datetime datatype for these 2 particular columns, however, he fails to explain the error message.

The error message is most likely the result of a combination of how mysql treats timestamp fields when no default value is provided and your sql mode settings.

  1. You define both timestamp columns as not null, without any specific default value set. This means that the 1st timestamp column's default value 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, no matter which of the 2 is the 1st one.

    However, the 2nd not null timestamp column's default value will be '0000-00-00 00:00:00' if you do not explicitly define a default value.

    See this blog post for more details.

  2. Probably no_zero_date sql mode is also enabled on your server either explicitly or as part of strict sql mode. This sql mode generates an error if you want set '0000-00-00 00:00:00' as a default value or would like to insert this value into any date field.

So, you can use timestamp data type in your table, but make the 2nd one either nullable or provide 0 or any valid date (such as the epoch) as an explicit default value.

Since you are marking start and end dates with these fields, uding datetime instead of timestamp as datatype may be a good idea.

Machavity
  • 30,841
  • 27
  • 92
  • 100
Shadow
  • 33,525
  • 10
  • 51
  • 64
4

You should be using DATETIME data types for your StartTime and FinishTime columns. TIMESTAMPS have a very specific usage. See http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

jsnplank
  • 53
  • 5
1

Referencing an article from the year 2000 is not really helpful as a lot already changed since then and might not be true any longer. As others already mentioned in other related questions, TIMESTAMP values reference a specific point in time relative to January 1st, 1970 at UTC. DATETIME values on the contrary just store some date and time without a reference to any point in time. They are more like display values, a clock on the wall that shows you some value. A datetime of 2018-01-12 14:00:00 might be a different time in different timezones if you want to keep track of when something happened.

TIMESTAMP on the other hand is always stored as UTC and when read or used in datetime function, automatically is converted back to the connection's or database default timezone. So 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 a +05:00 connection, you will see 2018-01-12 17:00:00. A DATETIME value would always stay at 2018-01-12 14:00:00 no matter what timezone is set for the database or connection.

So for tracking when something has happened or when something will/should happen, TIMESTAMP is the way to go. When you just want to store a fixed date time independent of the timezone, then use DATETIME (e.g. users shall receive an email at 2 in the morning, as 2 in the morning is the same for everyone).

TheLevti
  • 41
  • 1
  • 4