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

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

    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

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

                Dates

                • Created:
                  Updated:
                  Resolved: