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

INTERVAL data type

Details

    Description

      Currently INTERVAL literals can appear in context of the DATE_ADD function and the operator +.
      For example:

      SELECT DATE_ADD(OrderDate,INTERVAL 30 DAY) AS OrderPayDate FROM Orders
      		 
      SELECT OrderDate + INTERVAL 30 DAY AS OrderParDate FROM Orders;
      		 
      SELECT INTERVAL 30 DAY + OrderDate AS OrderParDate FROM Orders;

      We will change INTERVAL from a DATE_ADD and + specific syntax to a full-functional data type, so intervals can appear in all context where a regular data type can, including:

      • INTERVAL table fields
      • INTERVAL stored routine variables, parameters, function return values
      • Hybrid built-in functions and operators, such as COALESCE, IF, CASE, LEAST, GREATEST.
      • MIN and MAX aggregate functions

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-13088 SUGGESTION: New Data Type: Time Intervals

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-19144 sql_mode="oracle" does not support interval data type

          • Major - Major loss of function.
          • Closed
          is blocked by

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-12514 Split Item_temporal_func::fix_length_and_dec()

          • Major - Major loss of function.
          • Closed
          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-21086 New data types

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-35972 Oracle: Function TO_DSINTERVAL()

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            View 10 older comments
            HazemRawi Hazem Alrawi added a comment -

            I’ve already drafted a proposal for implementing the INTERVAL,Does this conversation mean that it will not included as a GSoC project idea, and instead be developed internally by the MariaDB community?

            HazemRawi Hazem Alrawi added a comment - I’ve already drafted a proposal for implementing the INTERVAL,Does this conversation mean that it will not included as a GSoC project idea, and instead be developed internally by the MariaDB community?
            ParadoxV5 Jimmy Hú added a comment -

            Does this conversation mean that it will not included as a GSoC project idea, and instead be developed internally by the MariaDB community?

            Which do you refer to by ‘it’, “INTERVAL SQL datatype” (this MDEV) or “interval options type” (my comment)?

            In theory, it doesn’t matter:

            we accept any project ideas, not only from our "ideas" page.

            That means we accept both ideas, and you could add both to your GSoC proposal, but keep in mind

            • Alex is on point stating that doing the “interval options type” as well is a scope creep for this MDEV.
            • Like those before me said, “INTERVAL SQL datatype” is already a large task.

            I too recommend leaving the options to a separate task; you could also pick it up post-GSoC if nobody beats you to it.

            ParadoxV5 Jimmy Hú added a comment - Does this conversation mean that it will not included as a GSoC project idea, and instead be developed internally by the MariaDB community? Which do you refer to by ‘it’, “INTERVAL SQL datatype” (this MDEV) or “interval options type” (my comment)? In theory, it doesn’t matter: we accept any project ideas, not only from our "ideas" page. That means we accept both ideas, and you could add both to your GSoC proposal, but keep in mind Alex is on point stating that doing the “interval options type” as well is a scope creep for this MDEV. Like those before me said, “INTERVAL SQL datatype” is already a large task. I too recommend leaving the options to a separate task; you could also pick it up post-GSoC if nobody beats you to it.
            HazemRawi Hazem Alrawi added a comment - - edited

            Which do you refer to by ‘it’, “INTERVAL SQL datatype” (this MDEV) or “interval options type” (my comment)?

            I am referring to interval datatype

            I noticed recent activity in the MDEV ticket, which makes me wonder if the community is already working on it and might exclude it from GSoC that's why I asked:

            and instead be developed internally by the MariaDB community?

            Now I have a clear understanding, I will keep working on INTERVAL SQL datatype.
            Thank you!

            HazemRawi Hazem Alrawi added a comment - - edited Which do you refer to by ‘it’, “INTERVAL SQL datatype” (this MDEV) or “interval options type” (my comment)? I am referring to interval datatype I noticed recent activity in the MDEV ticket, which makes me wonder if the community is already working on it and might exclude it from GSoC that's why I asked: and instead be developed internally by the MariaDB community? Now I have a clear understanding, I will keep working on INTERVAL SQL datatype. Thank you!
            bar Alexander Barkov added a comment - - edited

            HazemRawi, don't worry, nobody is working on this ticket. There is activity in all SQL-standard-conformance and other-database-compatibility tasks lately.

            The INTERVAL type is overdue for a long time. It existed in SQL-1992 already:

            <data type>    ::=
            		 
                     <character string type> [ CHARACTER SET <character set specification> ]
            		 
                 |     <national character string type>
            		 
                 |     <bit string type>
            		 
                 |     <numeric type>
            		 
                 |     <datetime type>
            		 
                 |     <interval type>

            MariaDB supports all other core SQL data types. INTERVAL is the only one not supported yet.

            MySQL/MariaDB DATETIME and TIME data types were originally implemented in the way to support both <datetime type> and <interval type> (with help of NO_ZERO_IN_DATE and NO_ZERO_DATE sql_mode flags and extended TIME range). So this task has always been postponed. But we do need to implement a dedicated data type for INTERVAL. Thanks for volunteering! It'll be a very good addition to conformance and compatibility.

            bar Alexander Barkov added a comment - - edited HazemRawi , don't worry, nobody is working on this ticket. There is activity in all SQL-standard-conformance and other-database-compatibility tasks lately. The INTERVAL type is overdue for a long time. It existed in SQL-1992 already: <data type> ::= <character string type> [ CHARACTER SET <character set specification> ] | <national character string type> | <bit string type> | <numeric type> | <datetime type> | <interval type> MariaDB supports all other core SQL data types. INTERVAL is the only one not supported yet. MySQL/MariaDB DATETIME and TIME data types were originally implemented in the way to support both <datetime type> and <interval type> (with help of NO_ZERO_IN_DATE and NO_ZERO_DATE sql_mode flags and extended TIME range). So this task has always been postponed. But we do need to implement a dedicated data type for INTERVAL. Thanks for volunteering! It'll be a very good addition to conformance and compatibility.
            HazemRawi Hazem Alrawi added a comment -

            That’s great!

            I propose implementing the INTERVAL data type with a 16-byte binary storage layout, structured as follows:

            -4 bytes: Encodes years and months as a single integer.
            -4 bytes: Encodes days as a single integer.
            -8 bytes: Encodes hours, minutes, seconds, and fractional seconds as total microseconds.

            What are your thoughts on its suitability for MariaDB?

            HazemRawi Hazem Alrawi added a comment - That’s great! I propose implementing the INTERVAL data type with a 16-byte binary storage layout, structured as follows: -4 bytes: Encodes years and months as a single integer. -4 bytes: Encodes days as a single integer. -8 bytes: Encodes hours, minutes, seconds, and fractional seconds as total microseconds. What are your thoughts on its suitability for MariaDB?

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.