Laravel × MySQL Timezone Hell: Days Spent Battling a 9-Hour DB Offset
One day, I tried to manipulate dates using Carbon, and...
Recently, while refactoring some Laravel code, I wrote a simple conditional search for a specific datetime column.
“I can handle this smartly with Carbon,” I thought, and started using Carbon::now() and addDays() without a second thought.
...but when I actually ran the SQL,for some reason, it was off by 9 hours.
The server is Asia/Tokyo, and PHP is also JST. Yet, it doesn't match. Why?
Digging deeper revealed the “sin of the initial build”
As I investigated, the cause was simple, yet deep-rooted.
During the initial build,MySQL's time_zone was left as UTC *This is not a problem
However, the app was storing JST strings directly into DATETIME columns. This is terrible.
It seems they have been operating in that state for years.
As a result, the date and time stored in the DB is a tricky time bomb: a “JST string that is 9 hours ahead of reality (but treated as UTC).”
What happens when things are like this (a quietly fatal wound)
For example, an event that originally took place at “2025-08-08 15:00:00” in JST
is recorded in the DB as “2025-08-08 15:00:00 (UTC).”
When you convert the DB value to JST, it jumps 9 hours into the future to “2025-08-09 00:00:00”.
Carbon is properly timezone-aware, but
when you face data that is “actually a JST string but interpreted as UTC,” the calculation results immediately fall apart.
It feels too late to fix it
You might think, “Then why not just change the DB's time_zone to JST?”
But if you do that, a hell awaits where all existing records will be “doubly offset.”
Also, there are a massive number of columns holding date and time data.
Since several years' worth of data has piled up in this state,
the only option is to absorb it on the application side.
Response Strategy
Handle new saves correctly
Modify it to INSERT using UTC time while keeping MySQL's time_zone as UTC. This is ideal, but impossible because existing data cannot be changed.Correct when reading existing data
Create a wrapper that corrects the offset using ->subHours(9) when handling with Carbon.Be careful with time comparison SQL
Since the values in the DB are “JST strings (treated as UTC),” align the comparison conditions in the WHERE clause to UTC, or fetch all records and filter them in the app.
Honestly, it's quite a hassle.
Every time I compare dates and times, I have to stop and think, "Do I need to adjust for this?"
Furthermore, if every member of the development team doesn't understand this background, more bugs will inevitably be born.
This is entirely a "sin of the initial setup," but
I felt firsthand how the cost of fixing it grows exponentially the longer the system is in operation.
Conclusion: Be conscious of and decide on your timezone from the start!
This is what I learned the hard way from this experience.
Align the timezones of the database and the application perfectly from the start
Trying to change it after operations have begun is a living hell
The "it just needs to work for now" mindset will come back to haunt you twofold later
Timezone settings are often taken lightly, but
they are the lifeline of any system that handles date and time data.
To keep your future self from crying, you want to make sure you don't make a mistake with your first move.
...Though, since I wasn't involved in the initial launch, all I can do is complain.