Solving the time zone problem in a web application

When launching our project in a region where the time zone was different from Moscow, we faced the problem of the difference between local time and server time (Moscow time zone). I must say that the logic of the project’s work is strongly tied to dates and times and it was impossible to leave a date in Moscow time. Almost all the dates were stored in MySQL database in DATETIME format, which, as it turned out later, is not the best way to organize the work of the application in several time zones.

The data type DATETIME is used to store the date and time in the range 1000-01-01 00:00:00 - 9999-12-31 23:59:59. When sampling, the date is extracted exactly the same that was recorded, regardless of the temporary settings of the database. You can convert the date to a specific time zone using the CONVERT_TZ function or adjust it manually in other ways.
')
Another type for storing dates, TIMESTAMP , is the only type for storing a date in MySQL, depending on the time zone. This type of data converts the time from local to UTC while saving, and when retrieving it back, taking into account the zone. Importantly, all operations and output are similar to the DATETIME type (starting with MySQL 5.0).
Also this type has very convenient features - it allows you to set NOW () as a default value, as well as a value when updating a record.
The disadvantage of TIMESTAMP is the limited date range (1970 - 2038). For this reason, it is not suitable for storing historical events or events of a distant future, but here and time zones are not critical.

So we needed to find a solution that does not require large-scale rewriting of code and SQL queries, so the options to adjust the time in queries or using PHP tools did not look good.
As a result, the following was done:
- All dates in the database were converted to TIMESTAMP
- When initializing a user session, the following code was added that sets the locale for MySQL and PHP:

<?php
date_default_timezone_set($user->timezone);
db::q("SET `time_zone`='".date('P')."'");
?>

The date_default_timezone_set () function accepts a string identifier of the time zone, for example “Europe / Moscow”, and sets it for all date and time functions.
The SQL query sets the time zone for all queries within the current connection (for more details, see the official documentation ).
As a result, this solution works wonderfully in different time zones and is a working way to solve the problem.

The conclusions of this post are simple: for projects with a wide geographic coverage, you should always use the TIMESTAMP type for storing time stamps, which will allow you to avoid headaches in the future.

UPD: As homm rightly pointed out , for the base it is more correct to set the time not by offset (as in the code above), but by the time zone identifier, otherwise daylight saving time transitions and other historical changes for this zone are not taken into account. In this case, you need to load the corresponding data into the database using mysql_tzinfo_to_sql and update them in a timely manner.