MySql TIMESTAMP error: Data truncation: Incorrect datetime value

Question

I have a table like this:

    CREATE TABLE event (
                       id              BIGINT          NOT NULL AUTO_INCREMENT PRIMARY KEY,

                       name            VARCHAR(80)     NOT NULL,                       
                       start_datetime  TIMESTAMP       NOT NULL DEFAULT '1970-01-01 00:00:01',
                       end_datetime    TIMESTAMP       NOT NULL DEFAULT '1970-01-01 00:00:01',

                       description     TEXT,                      

                       created         TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
                       modified        TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

);

There is no error when this new record is inserted:

insert into event (name, start_datetime, end_datetime)
value
('myName', '2020-01-05 18:00:00', '2020-01-07 23:59:00')
;

But this record is throwing error:

insert into event (name, start_datetime, end_datetime)
value
('myName', '2045-01-05 18:00:00', '2045-01-07 23:59:00')
;

The error is

Data truncation: Incorrect datetime value: '2045-01-05 18:00:00' for column 'start_datetime' at row 1

Could anyone please help ? Thanks so much!

Updated: Found this in MySql document: The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

So I guess 2045 is too far away ... ...

So you found the answer by yourself. You might as well post this as an answer, since you were actually first! — GMB
– GMB, Commented Aug 28, 2020 at 21:47

nbk · Accepted Answer · 2022-07-12 21:40:38Z

10

TIMESTAMP is only vaild for

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

For "bigger" Dates switch to DATETIME

the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

For more information consult the manual

edited Jul 12, 2022 at 21:40

answered Aug 28, 2020 at 21:47

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

Comments

Alyssa · Accepted Answer · 2020-08-28 21:47:24Z

2

From the MySQL documentation

The TIMESTAMP value has a range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

The issue is that the year 2045 is not allowed for the TIMESTAMP column.

answered Aug 28, 2020 at 21:47

Alyssa

9051 gold badge10 silver badges25 bronze badges

Comments

Evan_HZY · Accepted Answer · 2020-08-28 21:54:58Z

1

Answering my own(as in updated section of the original question) Found this in MySql document: The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

answered Aug 28, 2020 at 21:54

Evan_HZY

1,0446 gold badges19 silver badges35 bronze badges

Collectives™ on Stack Overflow

MySql TIMESTAMP error: Data truncation: Incorrect datetime value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related