0

So I'm using faker to seed the database with data for testing. Right now, when I use 'created_at' => $this->faker->dateTimeBetween('-5 years', '-3 days') it breaks and says

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-03-25 02:45:11' for column

I've looked it up and as mentioned here,

This is most likely a timezone conversion error on the day of standard-time to daylight-time switchover. The hour from 0200 to just before 0300 does not exist.

Is there a way to prevent that from happening? For example, specifying that the dateTimeBetween can only select hours between 6 and 18?

Edit #1: This mostly happens when I try to seed with 10k+ data (for testing speed, cache, and other stuff).

ADHDisDEV
  • 414
  • 4
  • 14

1 Answers1

1

You'll probably have to add in an extra step to convert it to a DateTime or Carbon object:

'created_at' => Carbon::parse($this->faker->dateTimeBetween('-5 years', '-3 days'))->toDateTimeString()

This will force the faker date into a Carbon instance, which should convert the date properly. For instance, if I do

DateTime::createFromFormat('Y-m-d H:i:s', '2018-03-11 02:45:11');

while in a US Eastern timezone, the date becomes 2018-03-11 03:45:11.0 America/New_York (-04:00)

Also make sure that Laravel and MySQL are on the same timezone (or use Carbon to convert the timezone), or use UTC.

aynber
  • 22,380
  • 8
  • 50
  • 63
  • I've tried it like that just now and it still runs into the same problem, since it can get a non-existing date (because the hour given does not exist as I mentioned): **The hour from 0200 to just before 0300 does not exist.** `SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-03-25 02:36:38' for column 'laravel'.'posts'.'created_at'` – ADHDisDEV Jan 24 '23 at 17:58
  • 1
    Double-check the timezones for Laravel and for your database. I wonder if they aren't the same, so Laravel thinks it's okay, but MySQL doesnt – aynber Jan 24 '23 at 17:59
  • Oh true, I forgot it's set to UTC by default. Changing the timezone fixes the problem, even without the Carbon or other reformats. Thank you! (submit the timezone-checking comment as an answer so I can accept it) – ADHDisDEV Jan 24 '23 at 18:02
  • 1
    I've added the comment – aynber Jan 24 '23 at 18:36