Support wider range of UNIX_TIMESTAMP, as MySQL 8.0.28

[mjonss]

Enhancement

Increase the range of Timestamp column, to 3001-01-19.

See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp

Prior to MySQL 8.0.28, the valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. This is also the case in MySQL 8.0.28 and later for 32-bit platforms.
For MySQL 8.0.28 and later running on 64-bit platforms, the valid range of argument values for UNIX_TIMESTAMP() is '1970-01-01 00:00:01.000000' UTC to '3001-01-19 03:14:07.999999' UTC (corresponding to 32536771199.999999 seconds).

It should be fairly easy in TiDB, since it is stored in the same format as DateTime, bit shifted YYYY MM DD HH MM SS . FRAC, but there are check to keep it in the same range as signed 32-bit time_t to be compatible with MySQL.

[kennytm]

Interesting. Why the range is only extended to year 3001 (and strangely reusing 01-19 03:14:07), not all the way to 9999-12-31 23:59:59? If it is expanded from 31 bits to 35 bits the upper range can actually reach 3058-10-26 03:46:07. If the return type of UNIX_TIMESTAMP is changed to DECIMAL(10) it should only reach 2286-11-20 17:46:39, and if it is DECIMAL(11) it should reach 5138-11-16 09:46:39. Very strange decisions.

And it seems what is expanded is not the range of TIMESTAMP type, but just the range of the functions UNIX_TIMESTAMP(), CONVERT_TZ() and FROM_UNIXTIME(). https://dev.mysql.com/doc/refman/8.0/en/datetime.html still talks about 2038:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

[mjonss]

Since MySQL 8.0.28 is not yet released, I assume we need to wait until it has been released to check the details. I also assume this update to the manual might been released a bit too early.
The extension is probably practical for users (year 3000 is far away for any practical use), rather than technical (not using the full 64 bit value for epoch)

[kennytm]

Well using the full 63 bits would take us to 292,277,026,596-12-04 15:30:07 which is certainly impractical 🤣.

But I think it is better to support the full DATETIME range (year 1000–9999, occupying 39 bits) rather than slightly relaxing to the awkward middle of year 3001 🙃.