Search code examples
laraveldatetimelaravel-5.3dstphp-carbon

Laravel Carbon Invalid Datetime Format Error during DST

I am using Laravel 5.3 and I insert datetime in mysql columns where the value is generated from the Controller using Carbon.

I have a lot of errors during a particular time in my error logs like this:

Next Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-10-01 02:29:54' for column 'invite_at' at row 1 (SQL: insert into `wedding_invitations` (`name`, `email`, `invite_at`, `updated_at`, `created_at`) values (Donald Trump, [email protected], 2017-10-01 02:29:54, 2017-08-02 02:29:54, 2017-08-02 02:29:54)) in /home/myapp/applications/weddingapp/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770

I see that the issue is not the date format since 2017-10-01 02:29:54 is a valid sql format. After digging into it and reading the other answers here in SO, I found that my server time is set to AEST and I think the error happened since the time between Oct 1 2am - 2.59.59am doesn't exist due to Day light savings. That is probably why I am getting this error.

The invite_at date is created in my controller like this:

$invitationDate = Carbon::now()->addDays(60);

My question is:

  1. How can I fix the above issue? Since I don't want it to enter an invalid date due to the DST? I don't want to be changing the timezone in mysql or server just to resolve this issue.

  2. Another idea I was thinking was, when the addDays is called, maybe set the time to 23.59.59 since that could avoid this error. For me the date is important than the time. So if I want addDays in Carbon to set a fixed time, how can I do that?

Solution

  • In order to have the comment I made as answer for whatever reason:

    Because the you are concerned about date than time you can set the date_time value you get from Carbon to startOfDay() or rather as stated in my comment to endOfDay() i.e:

    $invitationDate = Carbon::now()->addDays(60)->endOfDay();

    One other method is simply use date as your column type for invited_by, then you can avoid the issue with the time (since you said the date is more important).