Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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'

    Details

      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

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              kevin.harrison Kevin Harrison
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: