displayname displayname - 4 months ago 50
SQL Question

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

For my table

admin_tool_functionality


CREATE TABLE admin_tool_functionality (

id BIGINT NOT NULL AUTO_INCREMENT,

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),

price FLOAT NOT NULL,

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)
VALUES
(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)
VALUES
(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
DATETIME
values is
'1000-01-01 00:00:00.000000'
to
'9999-12-31 23:59:59.999999'



Why am I getting this error?

Answer

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.