Storing the date in mysql, taking into account the time zone

An article on how to avoid confusion with the dates stored in mysql.
This confusion arises for two reasons:
1. Different territories of our planet have a different time shift.
2. Some countries are switching to summer time and back ( map with countries switching to summer time ).

Many solve these problems in different ways. Someone makes a date shift in SQL queries, someone in php. Someone stores dates in TIMESTAMP, someone in DATETIME. I searched many sources, but nowhere did I find the right solution to this problem in Russian. In the native documentation on mysql I found information on how to achieve the correct automatic conversion of TIMESTAMP in local time, but here, too, its pitfalls.

If in php, setting up a time zone is easy, then mysql has difficulty, especially if you have limited access to the mysql server and some tables have not yet been installed there.

What is the difference between the storage formats for the TIMESTAMP and DATETIME dates, I hope you know.
The TIMESTAMP value is an absolute time value that is independent of local settings. In any country, on any computer it is 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 get the date in the yyyy-mm-dd hh: mm: ss format.
It would seem that everything is simple. And this simplicity is too tempting, and it is because of it that problems can arise, because you need to specify which time zone the user is given a date for.
And there is a solution: you can set the zone by request

SET time_zone='+03:00'

where '+03: 00' is the current date shift from London zero time.
But after this request, mysql starts to incorrectly handle the daylight saving time.
If the time_zone value is set to SYSTEM (default), daylight saving time is correctly processed.
For example, it is summer now and summertime is in effect.
We need to translate to the local time TIMESTAMP stored in the database. Its value is 946681261 (this is '2000-01-01 01:01:01' Kiev time)
Make a request:

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

We get the result:

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

Now we 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. While 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 will disappear. But there may also be surprises.
For example, in 2007 in the USA, Mexico and Canada there were some changes in the daylight saving rules. And since the data on time zones are entered manually in mysql, you must monitor the relevance of the data. Worse: for example, if you use the PHP language, you must ensure that the data on the time zones in mysql correspond to the data in PHP - and this is much more complicated.

That's why the best solution if you use PHP is as follows:
1. Store the date in mysql in TIMESTAMP
2. Always in PHP to set the time zone of the user function
date_default_timezone_set()
3. Get the TIMESTAMP value from the database and transfer it to the required format in PHP function
date($format, $timestamp);

At least until 2038 you can be sure that there is no confusion with dates.

Update: transferred to the MySQL blog