Dealing with timezones in PHP and MySQL

Question

I am trying to understand what exactly is responsible for changing and converting timezones and time respectively in my application stack and what is the best way to go about converting time to the required timezone in application code.

I am currently using:

Javascript/HTML font end

Laravel PHP framework server side

MySQL storage (Defaults to local system time for timestamps etc.)

I am of the opinion that:

MySQL can't store timezones for DateTime columns and just stores YYY:MM:DD hh:mm:ss and it's up to the developer to store the timezone in a separate column etc. and convert the stored time to user local in application code if user timezone is different.

A PHP, Laravel application should work in and convert any DateTime instances to the timezone set with date_default_timezone_set() function.

The behaviour I am currently observing:

I post back json with object properties set with javascript date time format. This format looks like so for me: e.g. 'Thu Jun 16 2016 18:00:00 GMT+1000 (AEST)'

When this json data hits my server, the application framework or PHP automatically converts it to UTC, even if I put date_default_timezone_set('Australia/Sydney') in controller class or change application configuration, replacing the laravel UTC default. I suspect something is not registering ? If I was able to save time as it came from the client (without being automatically converted to UTC), I would not have to later convert it to the user local time zone if the user is in Australia/Sydney (which is most of them).

My application stores this UTC adjusted time in the database, without actually having any record that it is UTC. Not really important of course as I can presume default of UTC.

When records are retrieved with eloquent or DB:query, there's no automatic conversion to user timezone and it simply returns the time as it was stored (converted to UTC), requiring application code to convert it to correct timezone, else user (in Australia in my case) will be looking at UTC time as opposed to local AEST Etc.

Converting time:

Is there a hassle free way of automatically converting all retrieved UTC time from the database to the user local timezone or some specified default time zone if all users are presumed from the same timezone ?

Ideally I want to pull out records from the database and have all the time properties adjusted to specified timezone, taking daylight savings into account. Would I have to run a for loop and convert/set each property with Carbon methods etc. ?

If the date_default_timezone_set('Australia/Sydney') is set and working properly, should PHP be automatically converting the time property on all those objects as they are retrieved from the database ? As well as not converting time data to UTC when it hits the server ?

Answer 1

timezones are annoying, there's no doubt about that. If I'm understanding you correctly, you want your PHP to return times to the view that are in the correct zone for the user, right?

What I do is within the 'master view' or some sort or blade.php file that is guaranteed to be loaded at least once, I check whether or not this user's timezone is stored in a session variable. If it is not, I send an AJAX request to the server to store the name of the timezone.

{{-- store timezone in session variables --}}
@if (!Session::has('timezone'))
    <script>
        $(function () {
            var tz = jstz.determine();
            var data = {};
            if (typeof (tz) !== 'undefined') {
                data.timezone = tz.name();
            }
            if (!$.isEmptyObject(data)) {
                $.ajax({
                    type: "POST",
                    url: "{{ url('/api/v1/settings') }}",
                    beforeSend: function (request) {
                        request.setRequestHeader("X-CSRF-TOKEN", "{{ csrf_token() }}");
                    },
                    data: $.param(data),
                });
            }
        });
    </script>
@endif

Note that this approach utilizies the jstz package, which you can download here and include in your <head> section.

Of course you will need to set up the route for this request, for my case, it looks like this:

Route::post('api/v1/settings', function () {
    // Save the user's timezone
    if (Request::has('timezone')) {
        Session::put('timezone', Request::get('timezone'));
    }
});

Now, when you want to convert the given database datetime strings to the correct timezone, you can get the timezone by saying $tz = $request->session()->get('timezone') and then parse out the dates with Carbon\Carbon::parse($date, $tz);

In general, I would recommmend you stay with storing all dates in UTC format, as that is the standard and it is imperitive that the database remain timezone agnostic. But if you want to change the default, you can edit the line 'timezone' => 'UTC' in config/app.php. That will overwrite the zone that Laravel defaults its timestamps to, so your created_at, updated_at will be changed to reflect that new timezone.

Answer 2

Agh - the good old "what to do with timezones" issue. Personally, on one of my L5.2 projects that heavily requires user timezone formatting for displaying data. I allow the user to select/modify their timezone on an account settings page. I then store this in a database and set up relationships for retrieving. For instance, I have many users who belong to one company. So in my "companies" table, I have a timezone field here. With proper relationships, I can access it like so Auth::user()->company->timezone

Then I parse all dates relevant to the platform using Carbon. For example, $current_date = Carbon:parse($date, Auth::user()->company->timezone);.

In hindsight, if I wasn't lazy (have other priorities) and wouldn't need to update a bunch of code, I'd probably create a trait for my models to convert the date attributes automatically.

Honestly though, there's many ways - and it can be a tricky thing to figure out if your platform is heavily on user timezones.

For example, you could do something like this in each of your models (or create a trait - best option - to store this). Note, I used my timezone relationship as an example. Obviously change "some_date" etc to whatever you table name is. Repeat as needed for each date you need converted:

/**
 * Convert date to user's timezone
 * 
 * @return mixed
 */
public function setSomeDateAttribute($value)
{
    $this->attributes['some_date'] = Carbon::parse($value, Auth::user()->company->timezone);
}

Note, dates should always be stored in database as UTC. Never save a user's timezone formatted date in database.

Answer 3

I'm not going into the discussion of how timestamp works but lemme tell you how efficiently you can store date time in MySQL to use it further. And why should you use a specific pattern to store time zone if you storing it for the very first time

date_default_timezone_set('Asia/Kolkata');
        $live_date = date('Y-m-d H:i:s');

Advantages of using this timestamp are-

1. You can directly specify data-type as DATETIME in the MySQL database while creating the table. It will allow you to sort data with this column as well.

2. Not just in the database but if you'll be using jQuery Data tables in your front-end, you'll get an advantage here too as this also allows sorting with time.

3. While writing a SQL query to fetch data for a particular date/time range, this way of storing time works well and you can simply write

   BETWEEN date1 AND date2