Mysql Invalid datetime format: 1292 Incorrect datetime value when creating some dummy data for testing

Question

When inserting some rows into the table I get this error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-03-26 02:00:00' for column 'created _at' at row 1

I am inserting in correct format: Y-m-d H:i:s so what is causing the problem?

Update:

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;

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.

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

Ike Walker Ike Walker · Accepted Answer · 2017-03-15T12:52:04

What timezone is your data stored in?

I think the problem is not the format of the datetime value, but the value itself.

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.

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 Invalid datetime format: 1292 Incorrect datetime value when creating some dummy data for testing

1 Answers