php 不正确的日期时间值数据库错误号：1292

Incorrect datetime value Database Error Number: 1292

提问by Luke O'Regan

Incorrect datetime value 0000-00-00 00:00:00 +0000 Database Error Number: 1292

不正确的日期时间值 0000-00-00 00:00:00 +0000 数据库错误号：1292

Hi Everyone I'm having a problem a with a server upgrade done by my hosting company and I'm trying to understand what is occurring so i can fix the problem

大家好，我的托管公司完成服务器升级时遇到问题，我正在尝试了解发生的情况，以便我可以解决问题

My sever has recently been upgraded to Server version: 5.6.17 and I'm getting errors all over the place saying my datetime value is incorrect?

我的服务器最近已升级到服务器版本：5.6.17 并且到处都是错误，说我的日期时间值不正确？

It seem to be add +0000 to the end of the datetime but I'm not sure why. This used to work perfectly fine on 5.5 but a recent upgrade has affected how my timestamps work

似乎将 +0000 添加到日期时间的末尾，但我不确定为什么。这曾经在 5.5 上运行良好，但最近的升级影响了我的时间戳的工作方式

Error Number: 1292

Incorrect datetime value: '2014-04-02 08:49:43 +0000' for column 'created' at row 1

INSERT INTO `activitylog` (`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) VALUES ('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43 +0000')

If I modify this sql query without +0000 it works?

如果我在没有 +0000 的情况下修改这个 sql 查询，它会起作用吗？

It affects anything that is a type of DATETIME on my table.

它会影响我的桌子上任何一种 DATETIME 类型的东西。

Has anyone else had a similar problem and now what the solution is to get this to work. At the moment I'm thing I will have to change all my PHP functions to echo the Date/Time rather than me calling NOW() on the query string

有没有其他人遇到过类似的问题，现在解决方案是什么让它起作用。目前我必须更改我所有的 PHP 函数来回显日期/时间，而不是我在查询字符串上调用 NOW()

回答by Simon East

I discovered after upgrading to MySQL 5.7 that this error started occurring in random situations, even when I wasn't supplying a date in the query.

我在升级到 MySQL 5.7 后发现这个错误开始在随机情况下发生，即使我没有在查询中提供日期。

This appears to be because previousversions of MySQL supported dates like 0000-00-00 00:00:00(by default) however 5.7.4 introduced some changes to the NO_ZERO_DATEsetting. If you still have old data present when using a newer MySQL version, then random errors may crop up.

这似乎是因为以前版本的 MySQL 支持日期0000-00-00 00:00:00（默认情况下），但是 5.7.4 对NO_ZERO_DATE设置进行了一些更改。如果在使用较新的 MySQL 版本时仍然存在旧数据，则可能会出现随机错误。

I needed to perform a query like this to reset all the zero dates to another date.

我需要执行这样的查询以将所有零日期重置为另一个日期。

# If the columns supports NULL, use that
UPDATE table SET date_column = NULL WHERE date_column < '1000-01-01';

# Otherwise supply another default date
UPDATE table SET date_column = '1970-01-01' WHERE date_column < '1000-01-01';

Alternatively, you may be able to adjust the NO_ZERO_DATEsetting, although note what the docs say about it:

或者，您可以调整NO_ZERO_DATE设置，但请注意文档对此的说明：

The NO_ZERO_DATEmode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

• If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

• If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

• If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNOREand UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

As of MySQL 5.7.4, NO_ZERO_DATEis deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATEdoes nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATEdoes have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATEis enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_DATEis deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

该NO_ZERO_DATE模式会影响服务器是否允许将“0000-00-00”作为有效日期。它的效果还取决于是否启用了严格的 SQL 模式。

• 如果未启用此模式，则允许使用“0000-00-00”并且插入不会产生警告。

• 如果启用此模式，则允许使用“0000-00-00”并且插入会产生警告。

• 如果启用此模式和严格模式，则不允许使用 '0000-00-00' 并且插入会产生错误，除非也给出 IGNORE。对于INSERT IGNOREand UPDATE IGNORE， '0000-00-00' 是允许的，插入会产生警告。

从 MySQL 5.7.4 开始，NO_ZERO_DATE已弃用。在 MySQL 5.7.4 到 5.7.7 中，NO_ZERO_DATE显式命名时不执行任何操作。相反，它的效果包含在严格 SQL 模式的效果中。在 MySQL 5.7.8 及更高版本中，NO_ZERO_DATE当显式命名并且不是严格模式的一部分时确实有效，就像 MySQL 5.7.4 之前一样。但是，它应该与严格模式结合使用，并且默认启用。如果NO_ZERO_DATE在未启用严格模式的情况下启用，则会出现警告，反之亦然。有关其他讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。

因为NO_ZERO_DATE已弃用，它将在未来的 MySQL 版本中作为单独的模式名称删除，其效果包含在严格 SQL 模式的效果中。

来自http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

回答by JedtheMarine

Ok, so I was having this same error. What I did to fix it was use these lines of code to query the database I was having issues with:

好的，所以我遇到了同样的错误。我所做的修复它是使用这些代码行来查询我遇到问题的数据库：

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
SET sql_mode = '';
SET GLOBAL sql_mode = '';

The first line of code (SELECT) is to see what the current setting are for both 'SESSION' and 'GLOBAL'. Once you set them both to empty strings and run the select again, they should return nothing (be empty).

第一行代码 (SELECT) 是查看“SESSION”和“GLOBAL”的当前设置。一旦您将它们都设置为空字符串并再次运行选择，它们应该不返回任何内容（为空）。

You may also need to use SET SESSION sql_mode = '';but this resolved the issue for me. Basically one of the settings in there was Hymaning up the way the date was coming into the database (I was getting it in a 'YYYY-MM-DD HH:MM:SS AM/PM' format). Deleting NO_ZERO_IN_DATEand the other date option didn't help me.

您可能还需要使用，SET SESSION sql_mode = '';但这为我解决了问题。基本上，其中的一项设置是提升日期进入数据库的方式（我以“YYYY-MM-DD HH:MM:SS AM/PM”格式获取它）。删除NO_ZERO_IN_DATE和其他日期选项对我没有帮助。

My site is working like it's supposed to now. Hopefully this helps.

我的网站像它现在应该的那样工作。希望这会有所帮助。

回答by Ragdata

Short answer - NOW()in your query should work perfectly well with a MySQL DATETIMEcolumn.

简短回答 -NOW()在您的查询中应该与 MySQLDATETIME列完美配合。

Longer answer - I'm not sure how you ever saw +0000working. The DATETIMEcolumn is formatted as 'YYYY-MM-DD HH:MM:SS'. When it comes to timezone differences, it's generally something you need to handle programmatically. MySQL does convert local times to UTC and back again when storing and retrieving TIMESTAMPdata - but it doesn't do this with DATETIMEor other Date / Time columns.

更长的答案 - 我不确定你是如何看待+0000工作的。该DATETIME列的格式为'YYYY-MM-DD HH:MM:SS'. 当涉及时区差异时，通常需要以编程方式处理。MySQL 在存储和检索TIMESTAMP数据时确实将本地时间转换为 UTC 并再次转换回来- 但它不会对DATETIME或其他日期/时间列执行此操作。

回答by jmail

Incorrect datetime value Database Error Number: 1292

不正确的日期时间值数据库错误号：1292

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

该TIMESTAMP数据类型被用于同时包含日期和时间部分的值。TIMESTAMP范围为'1970-01-01 00:00:01' UTCto '2038-01-19 03:14:07' UTC。

The DATETIMEtype is used for values that contain both date and time parts. MySQL retrieves and displays DATETIMEvalues in 'YYYY-MM-DD HH:MM:SS'format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

该DATETIME类型用于同时包含日期和时间部分的值。MySQLDATETIME以'YYYY-MM-DD HH:MM:SS'格式检索和显示值。支持的范围是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

you should use this type in: DateTime format

您应该在以下类型中使用此类型：DateTime 格式

INSERT INTO `activitylog` 
(`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) 
VALUES 
('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43')

https://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

https://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

https://dev.mysql.com/doc/refman/5.0/en/datetime.html

https://dev.mysql.com/doc/refman/5.0/en/datetime.html

http://bugs.mysql.com/bug.php?id=70188

http://bugs.mysql.com/bug.php?id=70188

update: 1

更新：1

you should remove the spacelike your code '2014-04-02 08:49:43 +0000'and change the code like '2014-04-02 08:49:43+0000'as full query is following as:

您应该删除space像您的代码'2014-04-02 08:49:43 +0000'并更改代码，如'2014-04-02 08:49:43+0000'完整查询如下：

INSERT INTO `activitylog` 
(`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) 
VALUES ('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43+0000')

look here : http://sqlfiddle.com/#!2/a2581/23099

看这里：http: //sqlfiddle.com/#!2/a2581/23099

回答by RDVS

Original my.cnf had sql_model set as follows:

原来 my.cnf 的 sql_model 设置如下：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I tried removing NO_ZERO_IN_DATE and NO_ZERO_DATE but it had no effect. But if I removed all terms (sql_mode empty) the error went away.

我尝试删除 NO_ZERO_IN_DATE 和 NO_ZERO_DATE，但没有效果。但是如果我删除了所有术语（sql_mode 为空），错误就消失了。

I went back to original sql_mode and thought I would remove terms 1-by-1 to see which one was cause. First attempt was to remove STRICT_TRANS_TABLES:

我回到原来的 sql_mode 并认为我会删除术语 1-by-1 以查看哪个是原因。第一次尝试是删除 STRICT_TRANS_TABLES：

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

No error. So it appears that STRICT_TRANS_TABLES was the cause.

没有错误。所以看起来 STRICT_TRANS_TABLES 是原因。

回答by Omkar

I was facing same issue.

我面临同样的问题。

I run below command and it worked for me.

我在命令下运行，它对我有用。

SET SESSION SQL_MODE='ALLOW_INVALID_DATES'