MySQL Datetime insert issue. Weird bug?

Question

I'm having a problem inserting a record into MySQL version 5.6.15. The problem is the date time.

The first record inserts without a problem. The second complains about the date.

INSERT INTO fish_db.fish_tank_temperature VALUES(NULL,'2015-10-04 14:00:27',30);
INSERT INTO fish_db.fish_tank_temperature VALUES(NULL,'2015-10-04 02:00:27',20.5);
**Error Code: 1292. Incorrect datetime value: '2015-10-04 02:00:27' for column 'time_value' at row 1**


select @@sql_mode
**'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'**

The schema create script is below for any testing you may try.

It's a very simple schema and table with 3 columns. An auto-incrementing primary key, time_value and temperature reading

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `fish_db` ;
CREATE SCHEMA IF NOT EXISTS `fish_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `fish_db` ;

-- -----------------------------------------------------
-- Table `fish_db`.`fish_tank_temperature`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `fish_db`.`fish_tank_temperature` ;

CREATE TABLE IF NOT EXISTS `fish_db`.`fish_tank_temperature` (
  `idfish_tank_temperature` INT NOT NULL AUTO_INCREMENT,
  `time_value` TIMESTAMP NOT NULL,
  `temperature` DOUBLE NOT NULL,
  PRIMARY KEY (`idfish_tank_temperature`),
  UNIQUE INDEX `idfish_tank_temperature_UNIQUE` (`idfish_tank_temperature` ASC),
  INDEX `fish_tank_temperature_idx1` (`time_value` ASC, `temperature` ASC))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Please help if you can.

Are you from Australia? What timezone do you live?

ojovirtual
– ojovirtual

2015-10-07 10:51:10 +00:00
Commented Oct 7, 2015 at 10:51 — ojovirtual
– ojovirtual, Commented Oct 7, 2015 at 10:51
Execute select @@system_time_zone; and check results.

ojovirtual
– ojovirtual

2015-10-07 10:53:46 +00:00
Commented Oct 7, 2015 at 10:53 — ojovirtual
– ojovirtual, Commented Oct 7, 2015 at 10:53
@ojovirtual 100% aussie. AUS Eastern Daylight Time

Peter H
– Peter H

2015-10-07 11:02:58 +00:00
Commented Oct 7, 2015 at 11:02 — Peter H
– Peter H, Commented Oct 7, 2015 at 11:02

ojovirtual · Accepted Answer · 2015-10-07 10:56:41Z

3

The problem you are having is that, based on your location (Sydney is what I see in your profile), your system is probably configured to do automatic Daylight Saving Time: on Oct 4th at 2 a.m. it is 3 a.m., so 2015-10-04 02:00:27 is an invalid datetime.

Maybe you should configure your server to use UTC.

answered Oct 7, 2015 at 10:56

ojovirtual

3,36219 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter H Over a year ago

100% Spot On. Daylight Savings killed the DB. Excellent find.

Collectives™ on Stack Overflow

MySQL Datetime insert issue. Weird bug?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related