2

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is definitely a possible solution. That said I don't necessarily think this would be the best. Reason being is this timezone would need to be checked/updated each time the user begins a session. This user may not stay in one locale. Additionally I would have to get the timezone information from the client because the server would be in another locale. So with all of that I will not accept or reject this answer quite yet.
No problem. I think this is the least complicated route; updating the time will always be an issue when you have multiple users in different timezones (and users moving around). Every system I've worked on saves in UTC and the APIs use UTC time; we convert as needed where being used. For web, this can be easily accomplished with JavaScript (Intl.DateTimeFormat().resolvedOptions().timeZone) . Some ORMs can also filter dates or format them in a specific way (at which point, you could further auto-adjust the time on the fly)
I think I like your solution other than the thought of storing a users timezone. I suppose that could be avoided entirely while still storing all DateTimes as UTC and just using JS to convert the time. Which if I understand the second part of your comment, that is what you typically do. If you modify your answer I will accept it.
sure - answer updated. I think there are pros/cons to both ways. It depends on how you expect it to work. For example, when I travel and open Gmail, it keeps the timestamps relative to my "home" timezone. It 'knows' when I am traveling and that my browser's time doesn't match that - so it asks you if you want to update your timezone.
To automatically set the timezone for your users without prompting them, you could send an AJAX request periodically to your server which contains Intl.DateTimeFormat().resolvedOptions().timeZone, and set that value into a $_SESSION variable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.