Bug #102157 UNIX_TIMESTAMP() and FROM_UNIXTIME() should support unix timestamps
Submitted: 6 Jan 2021 3:36 Modified: 3 Jan 2022 20:14
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0, all of them OS:Any (any modern OS)
Assigned to: CPU Architecture:Any (any 64 bit)

[6 Jan 2021 3:36] Domas Mituzas
Description:
In most of modern unix systems timestamp is stored in time_t, which is usually 64-bit integer counting seconds since 1970 (and as it is signed, it allows to specify time that happened before 1970).

Manuals in operating systems like Linux say:

"Applications intended to run after 2038 should use ABIs with time_t wider than 32 bits."

Generally, on 64-bit architectures (so, most of modern computing) Unix timestamp is 64-bit variable, and therefore any reference to it should probably not have the time boundary at 2038. Alas....

This is documented limitation:
mysql> select unix_timestamp("2040-01-01");
+------------------------------+
| unix_timestamp("2040-01-01") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

Sad, but documented. 

This one is not documented limitation:

mysql> select from_unixtime(5000000000);
+---------------------------+
| from_unixtime(5000000000) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

Considering that most of timezone conversion bugs are not going to be fixed in MySQL (see "won't fix" at bug#38455), the only viable type for storing time in MySQL is BIGINT - and unfortunately it fails to convert to a valid (64bit) Unix Timestamp.

Although you can surely provide guidance that MySQL should not be used in 17 years (I actually used it 17 years ago), a better fix would be supporting time values that will happen soon (in 17 years!). 

How to repeat:
Try using 64 bit inputs (or outputs) for unix time functions.

Suggested fix:
Support 64-bit timestamps
[6 Jan 2021 5:26] MySQL Verification Team
Hello Domas,

Thank you for the report and feedback.

regards,
Umesh
[6 Jan 2021 7:10] MySQL Verification Team
looks like:

https://bugs.mysql.com/bug.php?id=12654
(64-bit unix timestamp is not supported in MySQL functions)
[3 Jan 2022 20:14] Dag Wanvik
Posted by developer:
 
Fixed by WL#14630 in 8.0.28.