CODEWITHSUNDEEP
phplaraveltimezonephp-carbontimezone-offset
hyphen
hyphen

Reputation: 3440

Proper Timezone Handling Laravel / Carbon

Background

My app uses UTC as the timezone for the database, as it should.

Right now all my users are local, but I will have users in many different time zones.

Most things in my app are just dates, so I've just stored them as dates and left it alone.

HOWEVER, I read this advice in the carbon docs and figured I'd switch my dates to timestamps and store the appropriate precision regardless of whether I need it nor it.

Most of my dates don't really have a concept of time associated with them, even conceptually, although I understand and theoretically agree with the premise of the link above.

My Question

I'll use my own timezone as an example for the question. It's currently daylight saving time, and we're in Central Daylight Time, which is 5 hours behind UTC.

Several weeks back we were in CST, which was 6 hours behind UTC.

My question is this. Let's say I stored a date in the database 2 months ago which would've been a 6 hour difference from UTC. Now I'm reading that date from the database, but now it's CDT and only 5 hours difference. Does Carbon know that and handle that accordingly, or am I always going to run into timezone issues related to daylight saving time.

My concern is that a lot of my dates are just dates, they don't really have a time component, so they'll end up being something like 2023-04-07 05:00:00 or 2023-02-01 06:00:00. In that case with it being that close to midnight, calculating it wrong can change the date entirely, whereas dates in the middle of the day converted incorrectly might go unnoticed.

So what's the solution?

Is it to store the offset in the date field so it's always stored with the date? What is the generally accepted method to handle this, or the most logical way?

If the above is the correct way to do it, it looks like Laravel has $table->timestampsTz() method, so I'd need to convert all my dates and timestamps to timestampTz columns...and then I'm assuming there must be some way to tell laravel to include the offset when it writes to the created_at and updated_at columns, or maybe it just automagically knows.

Trying to be sure this is the right method before I waste any more time on this.

Also, it looks like MySql doesn't store timezone information, so I really don't know how to get around this problem.

This started out as something I thought would be fairly simple to just change my date fields to timestamps, but the more I read the more complicated this gets.

Upvotes: 2

Views: 1748

Answers (2)

O. Jones
O. Jones

Reputation: 108651

That advice from the carbon docs is usually correct. But there's a kind of date that isn't a moment in time. There are plenty of examples: The date of birth on your driver's license is one. You still have the same date of birth whether you travel to Australia or Iceland. Sure, the moment of your birth was different in local time in those places, and may in fact may have been on different calendar days. But that's not the way dates of birth work. The same is true for the dates of holidays.

The DATE data type (in MySql) is intended for those kinds of dates. It isn't timezone dependent. It's just a calendar date.

That may be what you want. It depends on the meaning of the dates in your app.

On the other hand if you want to store moments in time like "The video call starts at 9am in the America/New_York timezone and 6am in America/Los_Angeles, then the TIMESTAMP data type is what you want.

Many apps have both kinds of dates in them. That's why the database and programming environment offer both data types. Just pick the right one for the purpose.

Upvotes: 1

Eyad Bereh
Eyad Bereh

Reputation: 1726

If you're using MySQL TIMESTAMP datatype for your created_at and updated_at columns, you should have no issues with the daylight saving because the TIMESTAMP will automatically save the date and the time according to the UTC timezone

When you retrieve a timestamp column, MySQL will automatically take care of converting this timestamp from UTC to equivalent timestamp using the timezone on your device, or it will read it from the default-time-zone setting in MySQL configuration if you have defined it explicitly there

There's no need to store any offsets to handle this situation, you just have to deal with timezones, and not worry about other things because when a switch to daylight time happens it will be handled automatically

However, if your application is available to users in other timezones, then it might be a good idea to store user timezones in your database, and then you can retrieve them and change the timezone entry of your config/app.php file at runtime using a middleware, for example:

<?php

namespace App\Http\Middleware;
use Closure;
use Illuminate\Support\Facades\Auth;

class ChangeTimezoneForUser
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        if(!Auth::check()){
            return redirect(route('login'));
        }
        $user = $request->user(); // retrieve the user
        $timezone = $user->timezone; // retrieve the timezone
        config(["app.timezone" => $timezone]); // adjust the timezone according to user preference
        return $next($request);
    }
}

This way, your users will always get the date in their correct timezone

Upvotes: 0

Related Questions