'2018-03-22 00:00:00'怎么了?在MySQL中? [英] What's wrong with '2018-03-22 00:00:00" in MySQL?

问题描述

我想更新日期字段并将其设置为2018-03-22 00:00:00，但出现以下愚蠢错误:

I want to update a date field and set it to 2018-03-22 00:00:00 but I get the following stupid error:

错误代码:1292.错误的日期时间值:第158917行的日期"列为"2018-03-22 00:00:00"

Error Code: 1292. Incorrect datetime value: '2018-03-22 00:00:00' for column 'Date' at row 158917

这是我用于更新的查询:

This is the query I use for updating:

update assets.transactions 
set date = date_add(date, interval 1 hour)
where date between '2018-03-21 23:00:00' and '2018-06-29 23:59:59';

怎么了?我进行了大量搜索，发现MySQL不支持1970-01-01 00:00:01之前的日期，这是可以接受的，但是日期是2018年中?这是我无法消化的.

What is wrong? I searched a lot and found out dates before 1970-01-01 00:00:01 are not supported by MySQL, that is acceptable, but a date in the middle of 2018? That's something I can't digest.

有什么解决方案可以使这项工作正常进行吗?

Is there any solution to make this work right?

推荐答案

我想您正在更新TIMESTAMP列.我还猜想您已将您的MySQL实例设置为带有夏令时切换的时区，时间为2018年3月23日.我猜您所在国家/地区的时区切换规则意味着时钟将从2018年3月21日11:59:59翻转到2018年3月22日01:00:00.

I guess you're updating a TIMESTAMP column. I also guess you have your MySQL instance set to a timezone with a daylight time switchover on 23-March-2018. I guess the rules of your timezone switchover in your country mean that the clock rolls over from 21-March-2018 11:59:59 to 22-March-2018 01:00:00.

所以值2018-03-22 00:00:00根本不存在.

奇怪，不是吗?

尝试发出此MySQL命令，以将时区设置为UTC，然后再进行这些大量的时间戳更新.

Try issuing this MySQL command, to set the time zone to UTC, before doing these sorts of mass timestamp updates.

SET time_zone = 'UTC';

别忘了在执行其他操作之前将其切换回去.或者只是通过其他MySQL连接进行这些操作.

Don't forget to switch it back before doing other operations. Or just do those operations from a different MySQL connection.

这篇关于'2018-03-22 00:00:00'怎么了?在MySQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！