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.