displayname displayname - 1 year ago 186
SQL Question

Datetime field overflow if date > '9999-12-31 23:59:59.4'

For my table


CREATE TABLE admin_tool_functionality (


admin_tool_functionality_type_id BIGINT NOT NULL,

CONSTRAINT fk__admin_tool_functionality__admin_tool_functionality_type
FOREIGN KEY (admin_tool_functionality_type_id)
REFERENCES admin_tool_functionality_type(id),

PRIMARY KEY (id, admin_tool_functionality_type_id),


valid_from_day DATETIME NOT NULL,

valid_until_day DATETIME NOT NULL,

CHECK(valid_from_day < valid_until_day)


This insertion works fine:

INSERT INTO admin_tool_functionality
(admin_tool_functionality_type_id, price, valid_from_day, valid_until_day)
(1, 13.37, '2016-01-01', '9999-12-31 23:59:59.4');

But any value lager than
'9999-12-31 23:59:59.4'
is failing:

INSERT INTO admin_tool_functionality
(admin_tool_functionality_type_id, price, valid_from_day, valid_until_day)
(1, 13.37, '2016-01-01', '9999-12-31 23:59:59.5');

Giving me:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Datetime function: datetime field overflow
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)

despite the documentation saying

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

Why am I getting this error?

Answer Source

Refer to fractional seconds documentation for MySQL and note @Pieter comments regarding MySQL version.

Before MySQL 5.6.4 the microseconds are discarded and not stored but can be used in the following situations as per the documentation:

A trailing fractional part is permissible in contexts such as literal values, and in the arguments to or return values from some temporal functions.

On or after MySQL 5.6.4, fractional seconds support was expanded.

Unless you define fractional seconds (fsp) for your DATETIME column, it will default to 0 i.e. no microseconds.

Based on this comment in the documentation:

Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding

An assumption and theory based on the above comment and your failing INSERT is that .4 or even .499999 (up to 6 fractional digits which MySQL 5.6.4 or newer supports) will round down, whereas .5 or greater will round up.

Since microseconds are considered fractional, that would make the seconds the integer or whole part of the number. If 59.5 were to round up, that would result in your date part having to change (move forward 1 day) because time part range is 00:00:00 to 23:59:59. This behaviour could be considered unexpected outside of a function that adjusts dates and times.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download