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

Extend EXTRACT(x AS DAY*) to understand long time intervals

    XMLWordPrintable

Details

    Description

      Earlier MDEV-17351 made functions ADDTIME(t,time_interval) and TIMESTAMP(t,time_interval) understand long time intervals in the second argument.

      This task is to make EXTRACT(x AS *) understand long time intervals as well, for consistency.

      We'll change EXTRACT(x AS *) to parse some input formats as time interval, instead of DATE or TIME.

      Note, this change will affect only units starting with DAY*, HOUR*, MINUTE*, SECOND*, MICROSECOND*.
      Behavior of extraction units starting with YEAR* and MONTH* will not change.

      Examples of literal that will be parsed as time interval rather than DATE or TIME:

      • '2024:02:03'
      • '100000:02:03'
      • '24:02:03'
      • '01:02:03'
      • '01:02:03:'
      • '01:02:03-'
      • '01:02:03;'
      • '01:02:03/'
      • '20 10:20:30'
      • '2024:01:03 garbage /////'
      • '24:01:03 garbage /////'
      • '01:01:03 garbage /////'

      For example, in this script:

      CREATE OR REPLACE TABLE t1 (a VARCHAR(32));
      INSERT INTO t1 VALUES ('1000:02:03');
      INSERT INTO t1 VALUES ('10000:02:03');
      SELECT
        a,
        EXTRACT(DAY_SECOND FROM a) AS `day_second`,
        EXTRACT(DAY FROM a) AS day,
        EXTRACT(HOUR FROM a) AS hour
      FROM t1;
      DROP TABLE t1;
      

      the new EXTRACT() will return these results:

      +-------------+------------+------+------+
      | a           | day_second | day  | hour |
      +-------------+------------+------+------+
      | 1000:02:03  |   41160203 |   41 |   16 | -- 1000 is 41 days plus 16 hours
      | 10000:02:03 |  416160203 |  416 |   16 | -- 10000 is 416 days plus 16 hours
      +-------------+------------+------+------+
      

      Note, the current behavior returns this result:

      +-------------+------------+------+------+
      | a           | day_second | day  | hour |
      +-------------+------------+------+------+
      | 1000:02:03  |   34225959 |    3 |   22 |
      | 10000:02:03 |   34225959 | NULL |   22 |
      +-------------+------------+------+------+
      

      Note, behavior of these functions won't change:

      • YEAR()
      • MONTH()
      • DAY()
      • HOUR()
      • MINUTE()
      • SECOND()
      • MICROSECOND()

      They are MySQL compatibility functions and return different results comparing to the corresponding EXTRACT(unit FROM x). We won't touch them in this MDEV.

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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