Question 15260 in Laravel Question posted in
The official Laravel Documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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 AppHttpMiddleware;
use Closure;
use IlluminateSupportFacadesAuth;

class ChangeTimezoneForUser
{
    /**
     * Handle an incoming request.
     *
     * @param  IlluminateHttpRequest  $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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search