Storing dates in mysql based on timezone

An article on how to avoid confusion with dates stored in mysql.
These confusions arise for two reasons:
1. Different territories of our planet have a different time shift.
2. Some countries switch to daylight saving time and back ( map with countries switching to daylight saving time ).

Many solve these problems in different ways. Someone does a date shift in SQL queries, someone in php. Someone stores dates in TIMESTAMP, someone in DATETIME. I searched many sources, but I haven’t found the right solution to this problem anywhere in Russian. I found information in my native mysql documentation on how to achieve the correct automatic conversion of TIMESTAMP to local time, but this also has its pitfalls.

If setting up a time zone is simple in php, difficulties arise in mysql, especially if you have limited access to the mysql server and some tables are not already installed there.

How TIMESTAMP and DATETIME date storage formats differ, I hope you know.
TIMESTAMP is an absolute time value that is independent of local settings. In any country, on any computer, it is one and the same. Therefore, in most cases it is better to store the date in TIMESTAMP.

If you make a request
SELECT `timestamp_field` FROM table
you will receive a date in the form “yyyy-mm-dd hh: mm: ss”.
It would seem that everything is simple. And this simplicity is too seductive and it is because of it that problems can arise, because you need to specify what time zone the user for whom the date is assigned to.
And there is a solution: you can set the zone by asking

SET time_zone='+03:00'

where '+03: 00' is the current date offset from London time zero.
But after this query, mysql begins to incorrectly handle daylight saving time.
If time_zone is set to SYSTEM (the default), daylight saving time is handled correctly.
For example, it is summer and summer time is valid.
We need to translate TIMESTAMP stored in the database into local time. Its value is 946681261 (this is '2000-01-01 01:01:01' in Kiev time)
We make a request: We get the result:

SET time_zone = 'SYSTEM';
SELECT NOW(), FROM_UNIXTIME(946681261);

NOW ()	FROM_UNIXTIME (946681261)
2009-09-14 16:00:40	2000-01-01 01:01:01

Now change the time zone
SET time_zone = '+03:00';
SELECT NOW(), FROM_UNIXTIME(946681261);

NOW ()	FROM_UNIXTIME (946681261)
2009-09-14 16:00:40	2000-01-01 02:01:01

As you can see, instead of the expected '2000-01-01 01:01:01' we got an hour more time. Whereas the current time is displayed correctly.
You can configure mysql as needed (well, if you have full access to the database) and set the time zone in the format.

SET time_zone = 'America/Toronto';

After that, it may seem that all problems disappear. But there may also be surprises.
For example, in 2007, the United States, Mexico, and Canada experienced some changes in daylight saving rules. And since data on time zones is entered manually in mysql, then you should monitor the relevance of the data. Even worse: for example, if you use the PHP language, you must make sure that the time zone data in mysql matches the data in PHP - and this is much more complicated.

Therefore, the best solution, if you use PHP, I see the following:
1. Store the date in mysql in TIMESTAMP
2. Always set the user’s time zone in PHP with function
date_default_timezone_set()
3. Get the TIMESTAMP value from the database and translate it into the desired format in PHP with the
date($format, $timestamp);

Po function at least until 2038, you can be sure that there is no confusion with dates.

Update: moved to MySQL blog