MySql TIMESTAMP error: Data truncation: Incorrect datetime value

Viewed 4344

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 ... ...

3 Answers

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

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.

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.

Related