MySQL：数据截断：日期时间值不正确：'2006-10-01 02:22:44'

MySQL: Data truncation: Incorrect datetime value: '2006-10-01 02:22:44'

提问by Cameron Price-Austin

I'm getting the following exception updating a row using MySQL via JDBC:

我在通过 JDBC 使用 MySQL 更新一行时出现以下异常：

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2006-10-01 02:22:44'

com.mysql.jdbc.MysqlDataTruncation：数据截断：日期时间值不正确：'2006-10-01 02:22:44'

The column is defined as:

该列定义为：

'created_on_servicetimestamp NULL DEFAULT NULL'

'created_on_service时间戳空默认空'

There are no indexes or foreign keys on that column.

该列上没有索引或外键。

Obviously it's not a problem with data type. I have values in that table from both before and after that datetime. I also have values with times both before and after 2:22 AM.

显然这不是数据类型的问题。我在那个日期时间之前和之后的那个表中都有值。我也有 2:22 AM 之前和之后的时间值。

回答by Cameron Price-Austin

Solved it.

解决了。

Turns out that the 1st of October 2006 in South Australia was the start of daylight savings. Clocks get set forward one hour at 2.00am, so there was no 2:22am on that date: it went straight from 2:00am to 3:01am.

事实证明，2006 年 10 月 1 日在南澳大利亚是夏令时的开始。时钟在凌晨 2 点拨快一小时，所以那个日期没有凌晨 2:22：它从凌晨 2:00 一直到凌晨 3:01。

I'll change the db timezone to UTC, which should solve this issue.

我会将数据库时区更改为 UTC，这应该可以解决这个问题。

回答by Ppacheco

I fixed the same problem (com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'perev_start_time' at row 1) by upgrading my MySQL connector JAR, and copying the mysql.jar to the Tomcat lib directory.

我com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'perev_start_time' at row 1通过升级 MySQL 连接器 JAR 并将 mysql.jar 复制到 Tomcat lib 目录来修复了相同的问题 ( )。

The version of MySQL Server is 5.6 and the MySQL connector is mysql-connector-java-5.1.30-bin.jar.

MySQL Server 的版本是 5.6，MySQL 连接器是mysql-connector-java-5.1.30-bin.jar.

回答by Nikita Bosik

My problem was caused by DST, too. I've fixed it by changing column data type from timestampto datetime. This answerdescribes the difference, in short:

我的问题也是由 DST 引起的。我通过将列数据类型从 更改为timestamp来修复它datetime。这个答案描述了不同之处，简而言之：

• timestampstores time as Unix epoch time, so converts it to/from UTC according to server's time zone. Once you change server time zone, you have different interpretation for INSERT/UPDATEand different SELECTresults. Some time points are invalid due to DST;
• datetimestores time as is, regardless of server time zone. When passing UTC time, any time is valid (there are no DST "holes").

• 时间戳将时间存储为Unix 纪元时间，因此根据服务器的时区将其转换为 UTC 或从 UTC 转换。更改服务器时区后，您对INSERT/ 的解释UPDATE不同，SELECT结果也不同。部分时间点因夏令时无效；
• datetime按原样存储时间，与服务器时区无关。当超过 UTC 时间时，任何时间都是有效的（没有 DST“漏洞”）。

Note: you may still have to deal with "missing" time. This approach just shifts responsibility from DB level to application level.

注意：您可能仍然需要处理“丢失”的时间。这种方法只是将责任从 DB 级别转移到应用程序级别。

See also: MySQL documentationfor TIMESTAMP vs DATETIME

另请参阅： TIMESTAMP 与 DATETIME 的MySQL 文档

回答by rohit369

We upgraded MySQL server but didnt upgrade the mysql connector jar. We encountered this issue. Later I figured out it was due to the old jar. I upgraded it and this issue went away.

我们升级了 MySQL 服务器，但没有升级 mysql 连接器 jar。我们遇到了这个问题。后来我发现这是由于旧罐子造成的。我升级了它，这个问题就消失了。

回答by Gregory Nozik

You did not show exact update SQL. But may be you forget the date part

您没有显示确切的更新 SQL。但可能你忘记了日期部分

The correct format is yyyy-mm-dd hh:mm:ss format

正确的格式是 yyyy-mm-dd hh:mm:ss 格式

Date value should be in following format 2011-11-01 12:32:01

日期值应采用以下格式 2011-11-01 12:32:01