Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
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.
Attachments
Issue Links
- duplicates
-
MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range
-
- Closed
-
Activity
Nothing is stopping from storing TIMESTAMPas unsigned 64bit integers
What is returned by a query would just be a UNSIGNED BIGINT number. comparison operators will work fine on BIGINT numbers.
Only when conversion is required with the timestamp functions it will matter.
I see two avenues:
- A system variable that sets the storage size of a TIMESTAMP to either 64 or 32
SET timestamp_size = "64"; impacting all TIMESTAMP conversion and storage settings.
- Specific functions names for handling 64bit timestamps
> SELECT unix_timestamp64('2038-01-19 03:14:08'); |
+-----------------------------------------+ |
| unix_timestamp64('2038-01-19 03:14:08') | |
+-----------------------------------------+ |
| 2147480048 |
|
+-----------------------------------------+ |
Both versions will sustain a backward compatibility.
As alternativ to an UNSIGNED BIGTINT, we could allow negativ values for TIMESTAMP allowing time stamps before 1970-01-01 00:00:00 (UTC).
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.