Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
After pulling my hair and abusing Google for a few days I think its time to come to the Laracast Laravel overlords. I'm having an issue with storing and retrieving date time fields. This probably has something to do with the new Laravel 7 date serialislization. Hopefully one of you can point me to the right direction.
Let's say I have a User model with an expires_at attribute which is cast to datetime. When fetching a user model the Api will return something like:
{
"id":1,
"name":"Maurine Littel III",
"email":"[email protected]",
"email_verified_at":"2020-01-01T12:00:00.000000Z",
"expires_at":"2020-10-21T20:00:00.000000Z"
"updated_at":"2020-10-22T18:25:29.000000Z",
"created_at":"2020-10-22T18:25:29.000000Z",
}"
If I then post an update request to my api (ie. POST /users/1) with all the values I received from my API, the expires_at attribute suddenly changed -2 hours. The -2 probably comes from the app timezone: Europe/Amsterdam.
But the weird thing is, that the above timestamp which is received and sent is a Z (=UTC) timezone. So if I store the same timezone as I received, it should not change.
To test what is going wrong I made the simplest failing test I could:
public function testTimezoneIssue() {
$user = User::factory()->create();
$startOfDay = now()->startOfDay();
$user->update(['expires_at' => $startOfDay->toJson()]);
$this->assertEquals($user->expires_at, $startOfDay);
}
Indeed, this test fails:
There was 1 failure:
1) Tests\Feature\ExampleTest::testTimezoneIssue
Failed asserting that two DateTime objects are equal.
--- Expected
+++ Actual
@@ @@
-2020-10-21T22:00:00.000000+0200
+2020-10-22T00:00:00.000000+0200
/Users/michael/Code/timestamp-test/tests/Feature/ExampleTest.php:26
FAILURES!
Tests: 1, Assertions: 1, Failures: 1.
But why? Is $startOfDay->toJson() generating a wrong timezone? Or is the datetime mutator messing with the timezone?
Please help me stop pulling my hair. Thanks!
@michael@xonaymedia.nl
Check the following
config/app.php, what is the timezone?/etc/mysql/my.cnf
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
SELECT @@session.time_zone;
SELECT @@global.time_zone;
#1 and #3 have to be the same timezones.
Hi Laracoft,
Thanks for you help. These are the values I get:
Europe/Amsterdam
01:00:00
SYSTEM
SYSTEM
This seems to be ok, right?
The weird thing is:
If I use tinker to check the value of a model:
$user->expires_at I see the following:
Illuminate\Support\Carbon @1603159200 {#2192
date: 2020-10-20 04:00:00.0 Europe/Amsterdam (+02:00),
}
But when I use $user->expires_at->toJson(); it shows:
"2020-10-20T02:00:00.000000Z"
Which has a time difference of 2 hours. The time difference between Zulu and ty timezone (Europe/Amsterdam) is 1 hour. Not 2.
@michael@xonaymedia.nl
I re-read your question. All datetime strings are treated as session.time_zone prior to MySQL 8.0.19. See https://dev.mysql.com/doc/refman/8.0/en/datetime.html
My timezone is +8, but it's the same issue, let me explain:
$now = now(); // line 1, "2020-10-27 18:30:14", tz = +08:00
$startOfDay = $now->startOfDay(); // line 2, "2020-10-27 00:00:00", tz = +08:00
$user->expires_at = $startOfDay->toJson(); // line 3, "2020-10-26T16:00:00.000000Z", stored as "2020-10-26T16:00:00+08:00"
That's how we get 2020-10-26T16:00:00+08:00 (line 3) comparing against 2020-10-27 00:00:00 (line 2).
The issue is in line 3, where I sent in a UTC time string, but it was treated as a +8 prior to being saved.
In your case, that would be sending in UTC time string, but it was treated as a +1 prior to being saved.
Try just saving $startOfDay, it should fix your issue.
The thing is that a date is serialized to json if it is represented in an API. When the same data is is sent back, and stored it then uses the wrong time due to incorrect conversion.
The $user->expires_at = $startOfDay->toJson(); example I gave was just to demonstrate the conversion issue that happens when using an API.
The solution now, is to implement the serializeDate method for all my models, but this feels like a work-around .
I'm not sure what your code needs to do, but I think there is an simpler description of the issue:
MySQL does not accept a date string with timezone, it assumes all date strings to be in
session.time_zone
So, the issue is because the JSON date string contains a timezone that does not get parsed by MySQL. And because they are of different timezones, you are facing an issue.
The issue can be addressed by sending MySQL a date string converted to the MySQL's current timezone, or use MySQL 8.0.19 which accepts date strings with timezone.
True, but shouldn't that be something that is handled within the Laravel framework?
I don't see the use of JSON dates that often. usually it is ISO 8601 date('c').
I think another approach is to have an mutator in your model to parse JSON dates into date strings of Laravel and MySQL's timezone. See https://github.com/laravel/ideas/issues/1940#issuecomment-558723063
Yes, that mutator is my current workaround. But as said, that feels like a hack to solve something that looks like a bug.
I don't see the use of JSON dates that often. usually it is ISO 8601 date('c').
It's the current default implementation of Laravel. That's why I think it's odd that it doesn't work out of the box.
Well I hope we have at least figured out why you are getting the "problem" results.
toJson() used to produce 2019-11-14 21:08:12, which was "wrong".
Now it produces 2019-11-14T21:08:12.004740Z, which is what is meant by default.
I think what you are looking for is a fromJson() in Carbon::
Ah that might be true. But do you agree this is something the framework should take care of? In other words: with opening an issue?
I would think this falls under carbon https://github.com/briannesbitt/Carbon where you have $user->update(['expires_at' => Carbon::fromJson($date)]);
Ah, that might be true. Thanks!
Do mark a best answer any post helped explained why your test was failing. Thank you.
Please or to participate in this conversation.