That would be pretty difficult to change, because internal timestamp calculations and the TIMESTAMP type itself are all 32-bit. The latter cannot be really changed without causing huge compatibility issues.

Come 2038, the compatibility issues ain't going to look so huge, in comparison to what happens if the TIMESTAMP data type suddenly stops working properly!

Seriously? You might be able to put this off for a while, but it will HAVE to be done eventually.
The only alternative is to deprecate TIMESTAMP entirely, and hope that every single database instance in the world has a developer to update it accordingly, before the 2038 bug bites.

I'd suggest introducing a TIMESTAMP64 or something like that, so that future-proof software can at least start getting written now.
(With the idea being that it'd become an alias for TIMESTAMP once that goes to 64-bit).

This issue must be reopened. Attempting to insert/update a DATETIME column with FROM_UNIXTIME(any value > 2147483647) causes NULL to be inserted/updated. The function FROM_UNIXTIME() is documented to receive a Unix timestamp as produced by UNIX_TIMESTAMP(). The function UNIX_TIMESTAMP() is documented to return an unsigned integer. The fact that the TIMESTAMP column type is 32-bits wide is irrelevant.

If it is such a problem to fix UNIX_TIMESTAMP() and FROM_UNIXTIME(), then at least add UNIX_TIMESTAMP64() and FROM_UNIXTIME64() equivalents so users can properly insert / select / update numeric datetime values.

Right now the only solution users have is to do stupid math (subtract 100 years or so), use BIGINT instead of DATETIME (and be unable to use any date/time functions) or torture themselves with strftime()/ strptime() equivalents.

reopened

This is a pretty high priority issue for me. Thank you for re-opening the issue. I look forward to a simple solution.

I'd think that UNIX_TIMESTAMP() should work fine without a need for UNIX_TIMESTAMP64().

But note that you generally can only use if for the UTC time zone, as we cannot predict what timezone tables will look like 17 years in the future

Its not so much about getting accurate timestamps 17 years from now for non-UTC timezones but more about having confidence that existing software continues to work for the foreseeable future without requiring thousands of projects to make considerable changes.

If anything, fixing this issue sooner rather than later would be the preferred option so that projects that depend on MariaDB have time to test and make fixes as needed.

Possible fix that seems to work, probably need some refinement.

The general problem seems to be that a lot of time conversion routines still assume that timeval and friends are 32-bits. Removing some of these restrictions allows FROM_UNIXTIME() to break past INT_MAX.

Internally, UNIX_TIMESTAMP() is converting all inputs to Timestamp, losing precision in the process. Converting inputs to Datetime and then to Epoch solves the problem.

select unix_timestamp('2050-01-01 12:00:00');

+---------------------------------------+

| unix_timestamp('2050-01-01 12:00:00') |

+---------------------------------------+

|                            2524651200 |

+---------------------------------------+

select from_unixtime(2524651200);

+---------------------------+

| from_unixtime(2524651200) |

+---------------------------+

| 2050-01-01 12:00:00       |

+---------------------------+

mdev-341.patch

Wanted to note that MySQL has 64-bit support for UNIX_TIMESTAMP() and FROM_UNIXTIME() since MySQL 8.0.28.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

> On 32-bit platforms, the maximum useful value for unix_timestamp is 2147483647.999999, which returns '2038-01-19 03:14:07.999999' UTC. On 64-bit platforms running MySQL 8.0.28 or later, the effective maximum is 32536771199.999999, which returns '3001-01-18 23:59:59.999999' UTC. Regardless of platform or version, a greater value for unix_timestamp than the effective maximum returns 0.

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

Are there plans to port mysql's fix https://github.com/mysql/mysql-server/commit/bddc5bcda376bbf6c8b9f854d63d33462859b19c ?
It is only 15 years to 2038 and there are people who use future timestamps, so things will break for them sooner.

Currently timestamp is stored as a 4 byte signed integer.
MariaDB does not support negative signed integer values for timestamp (ie, date before 1970)
The simplest choice, to keep things compatible with what we have now and also to not increase the storage of
timestamp is to threat timestamps as unsigned.
This will expand the timestamp range with 68 years, to 2106.
I plan to fix this in 11.3 shortly.

The other alternative would be to make the timestamp 5 bytes. The problems with doing it this way are:

• Having to add a syntax to allow the user to define if they should use 4 or 5 byte timestamp.
• Users that has not converted to 5 byte timestamp will get a problem at 2038
• Comparing 4 and 5 byte timestamps would not be able to use indexes (without additional coding).

> Currently timestamp is stored as a 4 byte signed integer.
This isn't about changing how timestamps are stored or changing the format of TIMESTAMP.

This is about being able to:

• Use FROM_UNIXTIME to set values > 2038 on a DATETIME.
• Use UNIX_TIMESTAMP on a DATETIME and get values > INT_MAX.

Using FROM_UNIXTIME and UNIX_TIMESTAMP on a TIMESTAMP remains unchanged.

If you observe MySQL documentation, you will see they have not changed the storage size of TIMESTAMP (it is still 4 bytes). But FROM_UNIXTIME and UNIX_TIMESTAMP will work on dates > 2038.

The patch I wrote changes FROM_UNIXTIME to return DATETIME instead of TIMESTAMP and changes UNIX_TIMESTAMP to return int64 instead of in32. Normal conversions take care of the rest. I have not reviewed MySQL changes but very likely this is exactly what they did because it requires minimal changes.

unsigned timestamp is now MDEV-32188. we'll keep this issue open, though, because it's not just about going beyond 2038, so unsigned timestamp will not solve it