Mysql无效的日期时间格式:1292创建一些用于测试的虚拟数据时,日期时间值不正确 [英] Mysql Invalid datetime format: 1292 Incorrect datetime value when creating some dummy data for testing
问题描述
在表中插入一些行时,出现以下错误:
When inserting some rows into the table I get this error:
SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值: 2017-03-26 02:00:00列在第1行创建了
_at
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-03-26 02:00:00' for column 'created _at' at row 1
我要以正确的格式插入:Ymd H:i:s那是什么引起了问题?
I am inserting in correct format: Y-m-d H:i:s so what is causing the problem?
更新:
CREATE TABLE `posts` (
`id` bigint(20) UNSIGNED NOT NULL,
`title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`excerpt` text COLLATE utf8mb4_unicode_ci,
`body` text COLLATE utf8mb4_unicode_ci NOT NULL,
`image_path` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`image` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`slug` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`markdown` tinyint(1) NOT NULL DEFAULT '0',
`published` tinyint(1) NOT NULL DEFAULT '0',
`views` int(10) UNSIGNED NOT NULL DEFAULT '0',
`user_id` bigint(20) UNSIGNED NOT NULL,
`published_at` datetime DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
我正在使用500次重复的循环将虚拟数据插入到此表中,它设法插入了472个发布,然后由于上述错误而失败。
I am using a loop of 500 repetitions to insert dummy data into this table, it managed to insert 472 posts and then failed with above error.
这是生成的sql查询的样子,我不是自己写的,但是当我使用php对象和创建方法的方法时,会使用生成的框架
Here is how generated sql query looks like, I am not writting it my self, but framework I use generates it when I use php objects and methods for create etc
"query" => "insert into `posts` (`user_id`, `title`, `excerpt`, `body`, `s
lug`, `image`, `created_at`, `updated_at`, `published_at`, `published`, `views`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)3
8;5;208m"
"bindings" => array:11 [
0 => 1
1 => "Enim autem et quis consequatur vel deleniti et."
2 => "Voluptatibus nobis et et quaerat at inventore incidunt et qui ea co"
3 => """
Soluta distinctio dolorem ut officiis dolores quisquam esse sint. Repellendus est atque enim
quia.\n
"""
4 => "ab-itaque-facilis-culpa-suscipit-et-ratione-doloremque"
5 => "post_img_1.jpg"
6 => "2017-03-06 10:00:00"
7 => "2017-03-06 10:00:00"
8 => "2017-03-06 17:00:00"
9 => true
10 => 49
]
"time" => 29.97
推荐答案
您的数据存储在哪个时区?
What timezone is your data stored in?
我认为问题不是datetime值的格式,而是值本身。
I think the problem is not the format of the datetime value, but the value itself.
我注意到您的数据使用意大利语,导致错误的值是 2017-03-26 02:00:00
,在意大利这实际上是无效的日期时间,因为这是夏令时时间开始。
I notice some of your data is in Italian, and the value causing the error is 2017-03-26 02:00:00
, which is actually an invalid datetime in Italy because that's when daylight saving time starts.
意大利的时钟将从 2017-03-26 01:59:59
跳到 2017-03-26 03:00:00
,因此您要插入的值无效。
The clocks in Italy will jump from 2017-03-26 01:59:59
to 2017-03-26 03:00:00
, therefore the value you are trying to insert is invalid.
这篇关于Mysql无效的日期时间格式:1292创建一些用于测试的虚拟数据时,日期时间值不正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!