This task is now the main show-stopper for MDEV-4912. So changing priority to Critical.
I create a table with a TIMESTAMP column and populate it with special TIMESTAMP values near summer/winter time change:
Notice, the first record has a smaller UNIX_TIMESTAMP value, however the "YYYY-MM-DD hh:mm:ss" representation looks smaller for the second value.
This corner case happens because the first timestamp corresponds to summer time, while the second timestamp corresponds to winter time in the given time zone, when the clock went one hour backward.
Now I want to find the maximum value in the table t1 and store it into another table t2:
Notice, it erroneously took the record with the bigger "YYYY-MM-DD hh:mm:ss" representation, but with the smaller UNIX_TIMESTAMP value.
This is wrong. The expected behavior would be to take the record with 1288481125 timestamp value, which is later in time.
The server code should be changed to handle TIMESTAMP values using my_time_t representation, without conversion to datetime format "YYYY-MM-DD hh:mm:ss".