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 were faced with the problem of the difference in local time and server time (Moscow time zone). I must say that the logic of the project is strongly tied to dates and times and it was impossible to leave the date in Moscow time. Almost all dates were stored in the MySQL database in the DATETIME format, which, as it turned out later, is not the best way to organize the application in several time zones. DATETIME

Data Typeserves 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 as it was recorded, regardless of the temporary settings of the database. You can convert the date to a specific time zone with the CONVERT_TZ function or manually adjust it in other ways.

Another type for storing dates, TIMESTAMP , is the only type for storing dates in MySQL, depending on the time zone. This data type, when saved, converts the time from local to UTC, and when retrieving it back, it takes into account the zone. Importantly, all operations and output are similar to the DATETIME type (since MySQL 5.0).
This type also has very convenient features - it allows you to set NOW () as the default value, as well as when updating the record.
The disadvantage of TIMESTAMP is its limited date range (1970 - 2038). For this reason, it is not suitable for storing historical events or events of the distant future, but here time zones are not critical.

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

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

date_default_timezone_set () takes a parameter identifying a 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 (more in the official documentation ).
As a result, this solution works great 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 geographical coverage, the TIMESTAMP type should always be used for storing time stamps, which will help to avoid a headache in the future.

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