Search code examples
mysqldatedatetimesql-timestamp

MySQL incorrect timestamp value

I'm trying to insert datetime value '1970-01-01 00:00:01' in timestamp column but MySQL returned an error "Incorrect datetime value: '1970-01-01 00:00:01' for column 'timestamp'"

CREATE TABLE TST_TABLE
(
  tst_column timestamp NULL
)

INSERT INTO TST_TABLE(tst_column) VALUES('1970-01-01 00:00:01');

I'm confused because MySQL documentation claims that lowest valid value for timestamp is '1970-01-01 00:00:01'. What's wrong and what is real lowest timestamp value? Thanks.

Solution

  • This is a timezone issue. Set the timezone to UTC before the insert, for example : 

    SET time_zone='+00:00';
INSERT INTO TST_TABLE(tst_column) VALUES('1970-01-01 00:00:01');

    An other option is to convert you timestamp to the UTC timezone using CONVERT_TZ. For exemple, if your timezone is Europe/Paris : 

     INSERT INTO TST_TABLE(tst_column) VALUES(CONVERT_TZ('1970-01-01 00:00:01', 'Europe/Paris', 'UTC'));