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
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