[MDEV-20192] Change timestamp to 64-bit integer for number of seconds to extend its life with EPOCH defined as '1970-01-01 00:00:01 UTC' Created: 2019-07-27 Updated: 2023-09-18 Resolved: 2023-09-08 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Temporal Types |
| Fix Version/s: | N/A |
| Type: | Task | Priority: | Critical |
| Reporter: | Kevin Harrison | Assignee: | Unassigned |
| Resolution: | Duplicate | Votes: | 3 |
| Labels: | innodb | ||
| Issue Links: |
|
||||||||
| Description |
|
I understand the current limits of the TIMESTAMP data type using a 32-bit integer and its basis on the number of seconds since EPOCH '1970-01-01 00:00:01' (UTC) and causing the maximum value supported to be '2038-01-19 05:14:07' (UTC). The problem I have is needing to ensure the longevity of my use of MariaDB for our applications that need time zone support for future date/times and the guarantee of a maintainable and supportable solution for historical data into the future. If we have to resort to our our own design and support functions on the database for handling strings with ISO 8601 values, datetime columns with a secondary time zone column for support, or using a BIGINT UNSIGNED value for storage of our own EPOCH offset value, the time and effort involved in that for the 100s of tables and 1000s of columns that use timestamp may push us to find a different platform to solve this as we continue to resist internal pressures to move to other platforms. It seems that EPOCH is not something that can move once used. Or all the historical timestamps will be invalidated. One logical solution is to handle a larger number of seconds value and increase the longevity and improve the support for the use of TIMESTAMP in MariaDB. I would suggest keeping TIMESTAMP (32-bit unsigned) for backward compatibility and maintaining expected data storage requirements for table spaces. And introduce something like TIMESTAMP2 (64-bit unsigned) that we can move to with support from the same or similar functions that support a parameter value with the timestamp data type today. |
| Comments |
| Comment by Jonathan Shapiro [ 2020-06-15 ] | ||||||
|
Converting the internal storage unit to 64 bits seems straightforward, and it is largely independent of what to do for external purposes. The real question is what external text format should be used to express times in SELECT, INSERT, UPDATE and friends when their values are returned by a query. My recommendation would be to save 64-bit time values internally.The internal representation isn't the interesting part of the problem. For external representation purposes, I recommend that ISO8601 be adopted as follows:
Given the need for backwards compatibility, I see no reason to abandon the current input or output time format, but it would eliminate various kinds of unforced application errors if ISO8601 values could be presented as input. | ||||||
| Comment by johannes kingma [ 2021-03-03 ] | ||||||
|
Nothing is stopping from storing TIMESTAMPas unsigned 64bit integers Only when conversion is required with the timestamp functions it will matter.
SET timestamp_size = "64"; impacting all TIMESTAMP conversion and storage settings.
Both versions will sustain a backward compatibility. |