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

          Activity

            Juan Juan Telleria added a comment -

            In R, there is a package called "lubridate" which also offers a "Time Interval" Class:

            R Package Lubridate (GPL v2)

            I attach a vignette snapshot which contains some of the valuable functions this "Time Interval" Class offers.

            Juan Juan Telleria added a comment - In R, there is a package called "lubridate" which also offers a "Time Interval" Class: R Package Lubridate (GPL v2) I attach a vignette snapshot which contains some of the valuable functions this "Time Interval" Class offers.
            Juan Juan Telleria added a comment - - edited

            Interval Data Type could be really valuable for performing JOINs between tables by checking a temporal overlap between Events in a fast way.

            R lubridate package offers a function called int_overlaps() wich is valuable for performing in-memory joins between data frames.

            Also %within% function is of great interest.

            Thank you!

            Juan Juan Telleria added a comment - - edited Interval Data Type could be really valuable for performing JOINs between tables by checking a temporal overlap between Events in a fast way. R lubridate package offers a function called int_overlaps() wich is valuable for performing in-memory joins between data frames. Also %within% function is of great interest. Thank you!
            Juan Juan Telleria added a comment - - edited

            What would be amazing would be to be able to do the following SQL query in few seconds, having "Column_Interval_Data_Type" indexed:

            SELECT
              *
            FROM
              t_Table_Name1 AS A
              LEFT JOIN t_Table_Name2 AS B
                ON
                  A.Column_Interval_Data_Type INT_OVERLAPS (B.Column_Interval_Data_Type);
            

            At the moment I have to take the following steps in order to perform similar queries in MariaDB:

            1. 1. Both Table UNION, ORDER BY DateTime Type.
            2. 2. CURSOR: Check overlapping condition and assign IDs in MEMORY table, old vs new.
            3. 3. GROUP BY, Create the new table with time overlaps.

            Which also performs pretty fast, in a couple of minutes. Other option might be making use of Window Functions.

            Thank you.

            Juan Juan Telleria added a comment - - edited What would be amazing would be to be able to do the following SQL query in few seconds, having "Column_Interval_Data_Type" indexed: SELECT * FROM t_Table_Name1 AS A LEFT JOIN t_Table_Name2 AS B ON A.Column_Interval_Data_Type INT_OVERLAPS (B.Column_Interval_Data_Type); At the moment I have to take the following steps in order to perform similar queries in MariaDB: 1. Both Table UNION, ORDER BY DateTime Type. 2. CURSOR: Check overlapping condition and assign IDs in MEMORY table, old vs new. 3. GROUP BY, Create the new table with time overlaps. Which also performs pretty fast, in a couple of minutes. Other option might be making use of Window Functions. Thank you.
            Juan Juan Telleria added a comment -

            R Code for Intervals in "lubridate" package is as follows:

            github lubridate

            Juan Juan Telleria added a comment - R Code for Intervals in "lubridate" package is as follows: github lubridate
            danblack Daniel Black added a comment - - edited

            Django exposes the Postgres interval and Oracle's interval types as a DurationField which can simplify implementation logic (e.g. StackOverflow question)

            danblack Daniel Black added a comment - - edited Django exposes the Postgres interval and Oracle's interval types as a DurationField which can simplify implementation logic (e.g. StackOverflow question )

            People

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