PHP & MySQL DateTime – Timezone Handling

I want to store a DateTime value in my database (MySQL 5.7). I am trying to figure out the best way to handle time zones. From a glance it seems that MySQL only takes DateTime in the format of Y-m-d H:i:s which leaves out any Timezone info. There is however the Timestamp data type that allows you to give a UTC string. Unfortunately, this is only accepted until the date is greater than the year 2038.

The situation I have in mind is something like the following. Lets say our application server and MySQL server are in one timezone. A user in that timezone creates a file that is then indexed in the database. A different user in a different timezone wants to view this file and some meta information within the application. I want to show the correct time that the file was created relative to this users timezone.

How would I properly go about storing this date and time? I understand there may need to be some manipulation with JavaScript’s toLocaleString().

Edit: It looks like this has been addressed (for the most part) in MySQL 8 but I am looking for a 5.7 compatible solution.

As of MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table. The offset is appended to the date part of a datetime literal, with no intravening spaces, and uses the same format used for setting the time_zone system variable, with the following exceptions:

For hour values less than than 10, a leading zero is required.

The value ‘-00:00’ is rejected.

Time zone names such as ‘EET’ and ‘Asia/Shanghai’ cannot be used; ‘SYSTEM’ also cannot be used in this context.

Answer

Store all your data in UTC time. It gives you the greatest flexibility when dealing with users in multi-regions and/or multi-servers/databases.

• If you are using JavaScript, you can pull a user’s timezone by using

Intl.DateTimeFormat().resolvedOptions().timeZone

From here, you would reformat the datetime string using the user’s timezone into account.

• If you are using an ORM like Eloquent in Laravel (PHP), you can adjust the a datetime field on the fly by declaring a method in your model. For example, to change the ‘created_at’ field:

    public function getCreatedAtAttribute($value)
    {
        return Carbon::createFromTimestamp(strtotime($value))
            ->timezone('America/Los_Angeles')
            ->toDateTimeString();
    }

This option assumes you store the user’s timezone in another field in your database.