Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32496

TIMESTAMP variant to support YEAR range -4713 and +9999

Details

    Description

      MariaDB TIMESTAMP supports year values in the range 1970..2038, and after MDEV-32188 it's going to support year values in the range 1970..2106.

      This range is still narrower than most other databases support:

      Data type Min Max
      Oracle TIMESTAMP -4713 9999
      DB2 TIMESTAP 0001 9999
      PostgreSQL TIMESTAMP -4713 294276
      SQL Server DATETIME2 0001 9999
      SQL Server DATETIME 1753 9999

      For migration purposes, we need a data type which will cover at least the range -4713..9999.

      It will need 5 bytes (instead of 4 bytes) to store the "number of seconds since 1970-01-01 00:00:00 UTC" value.

      Also, it will need a new C++ data type (instead of struct timeval) to transfer values inside the server.

      struct {
      		 
        longlong tv_sec; -- signed, to store negative years
      		 
        uint32 tv_usec; 
      };

      This task is also a dependency for

      • MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic

      Using TIMESTAMP arithmetic for the current TIMESTAMP implementation is not very meaningful because of the too small TIMESTAMP range.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-10764 PL/SQL parser - Phase 2

          • Major - Major loss of function.
          • Open

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-11070 Providing compatibility to other databases - Phase 2

          • Major - Major loss of function.
          • Open
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-341 64-bit support in FROM_UNIXTIME()

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17389 sql_mode=ORACLE: Incompatibility in datetime arithmetic

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-10018 Timestamp with time zone

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            wlad Vladislav Vaintroub added a comment -

            bar, if possible, do not use ulong. It is a very badly portable type. for usec, int32_t is more than enough.

            wlad Vladislav Vaintroub added a comment - bar , if possible, do not use ulong. It is a very badly portable type. for usec, int32_t is more than enough.
            bar Alexander Barkov added a comment -

            wlad, thanks for your note. I agree. Changed to uint32.

            bar Alexander Barkov added a comment - wlad , thanks for your note. I agree. Changed to uint32.
            monty Michael Widenius added a comment -

            Why extend timestamp instead of increasing year range for DATE and DATETIME?

            monty Michael Widenius added a comment - Why extend timestamp instead of increasing year range for DATE and DATETIME?
            bar Alexander Barkov added a comment -

            monty, this task is a pre-requisite for MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics.

            The problem is that all date/time arithmetic functions currently implement only DATETIME input and DATETIME output.
            When called with a TIMESTAMP input, these functions involve TIMESTAMP->DATETIME conversion on input and optionally DATETIME->TIMESTAMP conversion on output.
            These both conversions are lossy because of DST changes and leap seconds.

            But implementing native TIMESTAMP arithmetic is rather meaningless because of a very narrow value range.

            bar Alexander Barkov added a comment - monty , this task is a pre-requisite for MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics. The problem is that all date/time arithmetic functions currently implement only DATETIME input and DATETIME output. When called with a TIMESTAMP input, these functions involve TIMESTAMP->DATETIME conversion on input and optionally DATETIME->TIMESTAMP conversion on output. These both conversions are lossy because of DST changes and leap seconds. But implementing native TIMESTAMP arithmetic is rather meaningless because of a very narrow value range.
            serg Sergei Golubchik added a comment -

            I don't think it is meaningless.

            Currently, if arithmetic works by doing TIMESTAMP->DATETIME->TIMESTAMP conversion. It makes total sense to remove conversions and make arithmetic works on TIMESTAMP values. This alone doesn't need any wider value range, what was out of range in TIMESTAMP->DATETIME->TIMESTAMP will stay out of range, but DST bugs will disappear.

            Widening the TIMESTAMP range is a separate task, mostly unrelated to how arithmetic is done.

            serg Sergei Golubchik added a comment - I don't think it is meaningless. Currently, if arithmetic works by doing TIMESTAMP->DATETIME->TIMESTAMP conversion. It makes total sense to remove conversions and make arithmetic works on TIMESTAMP values. This alone doesn't need any wider value range, what was out of range in TIMESTAMP->DATETIME->TIMESTAMP will stay out of range, but DST bugs will disappear. Widening the TIMESTAMP range is a separate task, mostly unrelated to how arithmetic is done.
            bar Alexander Barkov added a comment -

            It's not clear what to do with queries like this:

            SELECT DATE_ADD(timestamp_field, INTERVAL 200 YEAR);

            Now it returns a DATETIME out of the TIMESTAMP supported range. It works, but imprecisely.
            If we change the returned type to TIMESTAMP, it will return an out-of-range error.

            bar Alexander Barkov added a comment - It's not clear what to do with queries like this: SELECT DATE_ADD(timestamp_field, INTERVAL 200 YEAR ); Now it returns a DATETIME out of the TIMESTAMP supported range. It works, but imprecisely. If we change the returned type to TIMESTAMP, it will return an out-of-range error.

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.