This is an automated email from the ASF dual-hosted git repository.
zclll pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 3e9b966011c [Feature](function) Support more INTERVAL time-unit (#3315)
3e9b966011c is described below
commit 3e9b966011c45fab3cc0dfd2f76fb6cf979ece44
Author: linrrarity <[email protected]>
AuthorDate: Mon Feb 9 13:06:41 2026 +0800
[Feature](function) Support more INTERVAL time-unit (#3315)
## Versions
- [x] dev
- [x] 4.x
- [ ] 3.x
- [ ] 2.1
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
---
.../date-time-functions/date-add.md | 68 +++++++++++++++++-
.../date-time-functions/date-sub.md | 76 +++++++++++++++++---
.../date-time-functions/extract.md | 51 +++++++++++---
.../date-time-functions/date-add.md | 68 +++++++++++++++++-
.../date-time-functions/date-sub.md | 74 ++++++++++++++++---
.../date-time-functions/extract.md | 51 +++++++++++---
.../date-time-functions/date-add.md | 74 +++++++++++++++++--
.../date-time-functions/date-sub.md | 80 ++++++++++++++++++---
.../date-time-functions/extract.md | 53 +++++++++++---
.../date-time-functions/date-add.md | 74 +++++++++++++++++--
.../date-time-functions/date-sub.md | 82 +++++++++++++++++++---
.../date-time-functions/extract.md | 53 +++++++++++---
12 files changed, 706 insertions(+), 98 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
index ec0e8127026..8f580d89c67 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
@@ -13,6 +13,8 @@ The DATE_ADD function is used to add a specified time
interval to a specified da
- Supported input date types include DATE, DATETIME, TIMESTAMPTZ (such as
'2023-12-31', '2023-12-31 23:59:59', '2023-12-31 23:59:59+08:00').
- The time interval is specified by both a numeric value (`expr`) and a unit
(`time_unit`). When `expr` is positive, it means "add", and when it is
negative, it is equivalent to "subtract" the corresponding interval.
+This function behaves consistently with the [date_add
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)
in MySQL.
+
## Aliases
- days_add
@@ -21,7 +23,7 @@ The DATE_ADD function is used to add a specified time
interval to a specified da
## Syntax
```sql
-DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
+DATE_ADD(<date_or_time_expr>, INTERVAL <expr> <time_unit>)
```
## Parameters
@@ -29,8 +31,31 @@ DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
| Parameter | Description |
| -- | -- |
| `<date_or_time_expr>` | The date/time value to be processed. Supported
types: datetime or date type, with a maximum precision of six decimal places
for seconds (e.g., 2022-12-28 23:59:59.999999). For specific formats, please
refer to [timestamptz
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date conversion](../../../../sql [...]
-| `<expr>` | The time interval to be added, of `INT` type |
-| `<time_unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, DAY_SECOND, DAY_HOUR, MINUTE_SECOND, SECOND_MICROSECOND |
+| `<expr>` | The time interval to be added, for independent units (such as
`YEAR`) are of `INT` type; for compound units (such as `YEAR_MONTH`) are of
`STRING` type, and accept all non-numeric characters as separators. Therefore,
for example, `INTERVAL 6/4 HOUR_MINUTE` will be recognized as 6 hours 4 minutes
by Doris, rather than 1 hour 30 minutes (6/4 == 1.5). For compound units, if
the input interval value is too short, the value of the larger unit will be set
to 0. The sign of this va [...]
+| `<time_unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND |
+
+| time_unit | Expected format (accepts all non-numeric characters as
separators) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## Return Value
@@ -43,6 +68,7 @@ Returns a result with the same type as <date_or_time_expr>:
Special cases:
- When any parameter is NULL, returns NULL;
- When illegal unit or non-numeric expr, returns an error;
+- For composite units, if the input parts are excessive or any part exceeds
the allowed maximum value 922337203685477579, returns an error.
- When the calculation result exceeds the date type range (such as before
'0000-00-00 23:59:59' or after '9999-12-31 23:59:59'), returns an error.
- If the next month does not have enough days for the input date, it will
automatically be set to the last day of the next month.
@@ -114,6 +140,14 @@ mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1
2' DAY_HOUR);
| 2025-10-24 12:10:10 |
+----------------------------------------------------------+
+-- For compound units, accept all non-numeric characters as separators.
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-24 12:10:10 |
++----------------------------------------------------------------+
+
-- Add MINUTE_SECOND
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
@@ -130,6 +164,25 @@ mysql> select date_add("2025-10-10 10:10:10.123456",
INTERVAL "1.1" SECOND_MICR
| 2025-10-10 10:10:11.223456 |
+---------------------------------------------------------------------------+
+-- For composite units, the sign of the time interval is determined only by
whether the first non-digit character is `-`
+-- All subsequent `-` are considered part of the delimiter
+select
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:11:11 | 2025-10-23 10:09:09 |
++---------------------+---------------------+
+
+-- For composite units, if the input time interval is too short, the value of
the larger unit will be set to 0.
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:11 |
++-------------------------+
+
-- Example of TimestampTz type, SET time_zone = '+08:00'
select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1 DAY);
+-------------------------------------------------------+
@@ -138,6 +191,15 @@ select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1
DAY);
| 2023-01-03 04:22:33+08:00 |
+-------------------------------------------------------+
+-- If the number of time intervals input is excessive, return an error
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of 1:2:3.4 is
invalid
+
+-- For composite units, if the value of any part exceeds the maximum value of
922337203685477580
+-- return an error
+select DATE_ADD('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
922337203685477580 out of range
+
-- Illegal unit
select DATE_ADD('2023-12-31 23:00:00', INTERVAL 2 sa);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
index 4f7ecae625e..0a082611c22 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
@@ -10,13 +10,7 @@
The DATE_SUB function is used to subtract a specified time interval from a
given date or time value and return the calculated date or time result. It
supports operations on DATE (date only), DATETIME (date and time) and
TIMESTAMPTZ(date, time, and timezone offset) types, where the time interval is
defined by both a numerical value and a unit.
-This function behaves generally consistently with the [date_sub
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
in MySQL, but the difference is that MySQL supports compound unit additions
and subtractions, such as:
-
-```sql
-SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
- -> '2024-12-30 22:58:59'
-```
-Doris does not support this type of input.
+This function behaves consistently with the [date_sub
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
in MySQL.
## Aliases
@@ -26,7 +20,7 @@ Doris does not support this type of input.
## Syntax
```sql
-DATE_SUB(<date_or_time_part>, <expr> <time_unit>)
+DATE_SUB(<date_or_time_part>, INTERVAL <expr> <time_unit>)
```
## Parameters
@@ -34,8 +28,31 @@ DATE_SUB(<date_or_time_part>, <expr> <time_unit>)
| Parameter | Description |
| -- | -- |
| `<date_or_time_part>` | A valid date value, supporting datetime or date
type. For specificformats, please refer to [timestamptz
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-| `<expr>` | The time interval to be subtracted, of type `INT` |
-| `<time_unit>` | Enumerated values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND |
+| `<expr>` | The time interval to be subtracted, for independent units (such
as `YEAR`) are of `INT` type; for compound units (such as `YEAR_MONTH`) are of
`STRING` type, and accept all non-numeric characters as separators. Therefore,
for example, `INTERVAL 6/4 HOUR_MINUTE` will be recognized as 6 hours 4 minutes
by Doris, rather than 1 hour 30 minutes (6/4 == 1.5). For compound units, if
the input interval value is too short, the value of the larger unit will be set
to 0. The sign of th [...]
+| `<time_unit>` | Enumerated values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND |
+
+| time_unit | Expected format (accepts all non-numeric characters as
separators) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## Return Value
@@ -48,6 +65,7 @@ Returns a calculated result with the same type as date:
Special cases:
- Returns NULL if any parameter is NULL;
- Returns NULL for illegal expr (negative values) or time_unit;
+- For composite units, if the input parts are excessive or any part exceeds
the allowed maximum value 922337203685477579, returns an error.
- Returns an error if the calculated result is earlier than the minimum value
supported by the date type (e.g., before '0000-01-01').
## Examples
@@ -118,6 +136,44 @@ mysql> select date_sub('2023-01-01', INTERVAL NULL DAY);
| NULL |
+-------------------------------------------+
+
+-- For compound units, accept all non-numeric characters as separators.
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-22 08:10:10 |
++----------------------------------------------------------------+
+
+-- For composite units, the sign of the time interval is determined only by
whether the first non-digit character is `-`
+-- All subsequent `-` are considered part of the delimiter
+select
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:09:09 | 2025-10-23 10:11:11 |
++---------------------+---------------------+
+
+-- For composite units, if the input time interval is too short, the value of
the larger unit will be set to 0.
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:09 |
++-------------------------+
+
+-- If the number of time intervals input is excessive, return an error
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of -1:2:3.4 is
invalid
+
+-- For composite units, if the value of any part exceeds the maximum value of
922337203685477580
+-- return an error
+select DATE_SUB('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
-922337203685477580 out of range
+
+
-- Exceeds minimum date
mysql> select date_sub('0000-01-01', INTERVAL 1 DAY);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
day_add of 0000-01-01, -1 out of range
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
index dc0f0b8c457..bd6458a5a75 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
@@ -10,12 +10,7 @@
The `EXTRACT` function is used to extract specific time components from date
or time values, such as year, month, week, day, hour, minute, second, etc. This
function can precisely obtain specific parts of a datetime.
-This function behaves mostly consistently with the [extract
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
in MySQL. The difference is that Doris currently does not support combined
unit inputs, such as:
-
-```sql
-mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
- -> 201907
-```
+This function behaves consistently with the [extract
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
in MySQL.
## Syntax
@@ -25,12 +20,14 @@ mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02
01:02:03');
| Parameter | Description |
| -- | -- |
-| `<unit>` | Extract the value of a specified unit from DATETIME. The unit can
be year, month, week, day, hour, minute, second, or microsecond |
+| `<unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, DAYOFWEEK(DOW), DAYOFYEAR(DOY) |
| `<datetime_or_time_expr>` | A valid date expression that supports
date/datetime types and strings in date-time format. For specific datetime and
date formats, please refer to [datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
## Return Value
-Returns the extracted part of the date or time, of type INT, depending on the
extracted unit.
+Returns the extracted part of the date or time.
+- For independent types like `YEAR` and [DAYOFWEEK(DOW)](./dayofweek.md),
[DAYOFYEAR(DOY)](./dayofyear.md), the return type is INT
+- For composite types like `YEAR_MONTH`, the return type is STRING
The value range for the week unit is 0-53, calculated as follows:
@@ -43,9 +40,23 @@ When the unit is year, month, day, hour, minute, second,
microsecond, it returns
When the unit is quarter, January-March returns 1, April-June returns 2,
July-September returns 3, October-December returns 4.
Special cases:
-
-If <date_or_time_expr> is NULL, returns NULL.
-If <unit> is an unsupported unit, an error is reported.
+- If <date_or_time_expr> is NULL, returns NULL.
+- If <unit> is an unsupported unit, an error is reported.
+
+The format of the composite unit return is as follows:
+| time_unit | return format |
+| ------------------ | ----------------------------------------- |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## Examples
@@ -97,6 +108,24 @@ select extract(week from '2024-12-31') as week;
| 52 |
+------+
+select extract(year_month from '2026-01-01 11:45:14.123456') as year_month,
+ extract(day_hour from '2026-01-01 11:45:14.123456') as day_hour,
+ extract(day_minute from '2026-01-01 11:45:14.123456') as day_minute,
+ extract(day_second from '2026-01-01 11:45:14.123456') as day_second,
+ extract(day_microsecond from '2026-01-01 11:45:14.123456') as
day_microsecond,
+ extract(hour_minute from '2026-01-01 11:45:14.123456') as hour_minute,
+ extract(hour_second from '2026-01-01 11:45:14.123456') as hour_second,
+ extract(hour_microsecond from '2026-01-01 11:45:14.123456') as
hour_microsecond,
+ extract(minute_second from '2026-01-01 11:45:14.123456') as
minute_second,
+ extract(minute_microsecond from '2026-01-01 11:45:14.123456') as
minute_microsecond,
+ extract(second_microsecond from '2026-01-01 11:45:14.123456') as
second_microsecond;
+
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| year_month | day_hour | day_minute | day_second | day_microsecond |
hour_minute | hour_second | hour_microsecond | minute_second|
minute_microsecond | second_microsecond |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| 2026-01 | 1 11 | 1 11:45 | 1 11:45:14 | 1 11:45:14.123456 |
11:45 | 11:45:14 | 11:45:14.123456 | 45:14 | 45:14.123456
| 14.123456 |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+
-- Input unit does not exist, reports error
select extract(uint from '2024-01-07') as week;
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
index a28bfd6173d..2d254e4b092 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
@@ -13,6 +13,8 @@ DATE_ADD 函数用于向指定的日期或时间值添加指定的时间间隔
- 支持的输入日期类型包括 DATE、DATETIME、TIMESTAMPTZ（如 '2023-12-31'、'2023-12-31
23:59:59'、'2023-12-31 23:59:59+08:00'）。
- 时间间隔由数值（`expre`）和单位（`time_unit`）共同指定，`expr` 为正数时表示“添加”，为负数时等效于“减去”对应间隔。
+该函数与 MySQL
的[DATE_ADD函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)行为一致。
+
## 别名
- days_add
@@ -21,7 +23,7 @@ DATE_ADD 函数用于向指定的日期或时间值添加指定的时间间隔
## 语法
```sql
-DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
+DATE_ADD(<date_or_time_expr>, INTERVAL <expr> <time_unit>)
```
## 参数
@@ -29,8 +31,31 @@ DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
| 参数 | 说明 |
| -- | -- |
| `<date_or_time_expr>` | 待处理的日期/时间值。支持类型：为 timestamptz, datetime 或者 date
类型，最高有六位秒数的精度（如 2022-12-28 23:59:59.999999），具体格式请查看
[timestamptz的转换](../../../../sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)|
-| `<expr>` | 希望添加的时间间隔，为 `INT` 类型|
-| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
DAY_SECOND, DAY_HOUR, MINUTE_SECOND, SECOND_MICROSECOND |
+| `<expr>` | 希望添加的时间间隔，对于独立单位(如`YEAR`)为 `INT` 类型; 对于复合单位(如`YEAR_MONT`)为字符串类型,
且接受所有非数字作为分隔符，所以对于例如`INTERVAL 6/4 HOUR_MINUTE`，Doris会将其识别为 6 小时 4
分，而非1小时30分(6/4 == 1.5)。对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为
0。该值的正负性仅由第一个出现的非数字字符是否为`-`决定。|
+| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND。|
+
+| time_unit | 预期格式(接受所有非数字作为分隔符) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## 返回值
@@ -43,6 +68,7 @@ DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
特殊情况：
- 任何参数为 NULL 时，返回 NULL；
- 非法单位或非数值 expr 时，报错；
+- 对于复合单位，如果输入的部分过多或其中某一部分超出允许最大值922337203685477579, 报错。
- 计算后超出日期类型范围（如 '0000-00-00 23:59:59' 之前，'9999-12-31 23:59:59' 之后）时，返回错误。
- 若是下月不足输入日期的天数，会自动设置为下月最后一天
@@ -113,6 +139,14 @@ mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1
2' DAY_HOUR);
| 2025-10-24 12:10:10 |
+----------------------------------------------------------+
+-- 对于复合单位, 接受所有非数字作为分隔符
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-24 12:10:10 |
++----------------------------------------------------------------+
+
-- 添加 MINUTE_SECOND
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
@@ -129,6 +163,25 @@ mysql> select date_add("2025-10-10 10:10:10.123456",
INTERVAL "1.1" SECOND_MICR
| 2025-10-10 10:10:11.223456 |
+---------------------------------------------------------------------------+
+-- 对于复合单位，时间间隔的正负性仅由第一个出现的非数字字符是否为`-`决定
+-- 后续的所有 `-` 会被认为是分隔符的一部分
+select
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:11:11 | 2025-10-23 10:09:09 |
++---------------------+---------------------+
+
+-- 对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为 0
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:11 |
++-------------------------+
+
-- TimestampTz类型示例, SET time_zone = '+08:00'
select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1 DAY);
+-------------------------------------------------------+
@@ -137,6 +190,15 @@ select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1
DAY);
| 2023-01-03 04:22:33+08:00 |
+-------------------------------------------------------+
+-- 对于复合单位，如果输入时间间隔数量过多，报错
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of 1:2:3.4 is
invalid
+
+-- 对于复合单位, 如果其中一部分的值超过最大值922337203685477580，报错
+select DATE_ADD('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
922337203685477580 out of range
+
+
---非法单位
select DATE_ADD('2023-12-31 23:00:00', INTERVAL 2 sa);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
index 762ebc226f4..3fdeae5378e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
@@ -10,13 +10,7 @@
DATE_SUB 函数用于从指定的日期或时间值中减去指定的时间间隔，返回计算后的日期或时间结果。该函数支持对 DATE（仅日期）,
DATETIME（日期和时间）TIMESTAMPTZ(带时区偏移量的日期时间)类型进行操作，时间间隔通过数值和单位共同定义。
-该函数与 mysql 中的 [date_sub
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
行为大致一致，但不同的是，mysql 支持联合单位的增减，如:
-
-```sql
-SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
- -> '2024-12-30 22:58:59'
-```
-doris 不支持这种输入。
+该函数与 mysql 中的 [date_sub
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
行为一致。
## 别名
@@ -26,7 +20,7 @@ doris 不支持这种输入。
## 语法
```sql
-DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
+DATE_SUB(<date_or_time_part>, INTERVAL <expr> <time_unit>)
```
## 参数
@@ -34,8 +28,32 @@ DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
| 参数 | 说明 |
| -- | -- |
| `<date_or_time_part>` | 合法的日期值，支持为 timestamptz, datetime 或者 date 类型，具体格式请查看
[timestamptz的转换](../../../../sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-| `<expr>` | 希望减去的时间间隔，类型为 `INT` |
-| `<time_unit>` | 枚举值：YEAR，QUARTER，MONTH，WEEK，DAY，HOUR，MINUTE，SECOND |
+| `<expr>` | 希望减去的时间间隔，类型为 `INT`, 对于独立单位(如`YEAR`)为 `INT` 类型;
对于复合单位(如`YEAR_MONT`)为字符串类型, 且接受所有非数字作为分隔符，所以对于例如`INTERVAL 6/4
HOUR_MINUTE`，Doris会将其识别为 6 小时 4 分，而非1小时30分(6/4 == 1.5)。对于复合单位,
如果输入的时间间隔值过短，会将空出的大单位的值设为 0。该值的正负性仅由第一个出现的非数字字符是否为`-`决定。|
+| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND。 |
+
+
+| time_unit | 预期格式(接受所有非数字作为分隔符) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## 返回值
@@ -48,6 +66,7 @@ DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
特殊情况：
- 任何参数为 NULL 时，返回 NULL；
- 非法单位，返回错误。
+- 对于复合单位，如果输入的部分过多或其中某一部分超出允许最大值922337203685477579, 报错。
- 计算结果早于日期类型支持的最小值（如 '0000-01-01' 之前），返回错误。
## 举例
@@ -118,6 +137,41 @@ SELECT DATE_SUB('2024-02-05 02:03:04.123+12:00', INTERVAL
1 DAY);
| 2024-02-03 22:03:04.123+08:00 |
+-----------------------------------------------------------+
+-- 对于复合单位, 接受所有非数字作为分隔符
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-22 08:10:10 |
++----------------------------------------------------------------+
+
+-- 对于复合单位，时间间隔的正负性仅由第一个出现的非数字字符是否为`-`决定
+-- 后续的所有 `-` 会被认为是分隔符的一部分
+select
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:09:09 | 2025-10-23 10:11:11 |
++---------------------+---------------------+
+
+-- 对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为 0
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:09 |
++-------------------------+
+
+-- 对于复合单位，如果输入时间间隔数量过多，报错
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of -1:2:3.4 is
invalid
+
+-- 对于复合单位, 如果其中一部分的值超过最大值922337203685477580，报错
+select DATE_SUB('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
-922337203685477580 out of range
+
--非法单位，返回错误
mysql> select date_sub('2022-01-01', INTERVAL 1 Y);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
index 5e943fda8de..72ec083c22e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
@@ -10,12 +10,7 @@
`EXTRACT` 函数用于从日期或时间值中提取指定的时间组件，如年份、月份、周、日、小时、分钟、秒等。该函数可精确获取日期时间中的特定部分。
-该函数与 mysql 中的 [extract
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
行为基本一致，不同的是，doris 目前暂不支持联合单位输入，如：
-
-```sql
-mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
- -> 201907
-```
+该函数与 mysql 中的 [extract
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
行为一致。
## 语法
@@ -25,12 +20,14 @@ mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02
01:02:03');
| 参数 | 说明 |
| -- | -- |
-| `<unit>` | 提取 DATETIME 某个指定单位的值。单位可以为 year, month, week, day, hour, minute,
second 或者 microsecond |
+| `<unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, DAYOFWEEK(DOW), DAYOFYEAR(DOY) |
| `<datetime_or_time_expr>` | 参数是合法的日期表达式，支持输入 date/datetime
类型和符合日期时间格式的字符串，具体 datetime 和 date 格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
## 返回值
-返回的是提取出的日期或时间的某个部分,类型为 INT，具体的部分取决于提取的单位。
+返回的是提取出的日期或时间的某个部分。
+- 对于独立类型如`YEAR`和[DAYOFWEEK(DOW)](./dayofweek.md),
[DAYOFYEAR(DOY)](./dayofyear.md)， 返回类型为 INT
+- 对于复合类型如`YEAR_MONTH`, 返回类型为 STRING
week 单位的取值范围为 0-53，计算规则如下：
@@ -42,9 +39,23 @@ week 单位的取值范围为 0-53，计算规则如下：
单位为 quarter 时，1-3 月返回 1，4-6 月返回2， 7-9 月返回 3，10-12 返回 4.
特殊情况：
-
-若 <date_or_time_expr> 为 NULL，返回 NULL。
-若 <unit> 为不支持单位，报错
+- 若 <date_or_time_expr> 为 NULL，返回 NULL。
+- 若 <unit> 为不支持单位，报错
+
+复合单位返回格式如下：
+| time_unit | 返回格式 |
+| ------------------ | ----------------------------------------- |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## 举例
@@ -96,6 +107,24 @@ select extract(week from '2024-12-31') as week;
| 52 |
+------+
+select extract(year_month from '2026-01-01 11:45:14.123456') as year_month,
+ extract(day_hour from '2026-01-01 11:45:14.123456') as day_hour,
+ extract(day_minute from '2026-01-01 11:45:14.123456') as day_minute,
+ extract(day_second from '2026-01-01 11:45:14.123456') as day_second,
+ extract(day_microsecond from '2026-01-01 11:45:14.123456') as
day_microsecond,
+ extract(hour_minute from '2026-01-01 11:45:14.123456') as hour_minute,
+ extract(hour_second from '2026-01-01 11:45:14.123456') as hour_second,
+ extract(hour_microsecond from '2026-01-01 11:45:14.123456') as
hour_microsecond,
+ extract(minute_second from '2026-01-01 11:45:14.123456') as
minute_second,
+ extract(minute_microsecond from '2026-01-01 11:45:14.123456') as
minute_microsecond,
+ extract(second_microsecond from '2026-01-01 11:45:14.123456') as
second_microsecond;
+
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| year_month | day_hour | day_minute | day_second | day_microsecond |
hour_minute | hour_second | hour_microsecond | minute_second|
minute_microsecond | second_microsecond |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| 2026-01 | 1 11 | 1 11:45 | 1 11:45:14 | 1 11:45:14.123456 |
11:45 | 11:45:14 | 11:45:14.123456 | 45:14 | 45:14.123456
| 14.123456 |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+
-- 输入单位不存在，报错
select extract(uint from '2024-01-07') as week;
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not found function 'uint'
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
index 519482cb11b..0bddffa2672 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
@@ -13,6 +13,8 @@ DATE_ADD 函数用于向指定的日期或时间值添加指定的时间间隔
- 支持的输入日期类型包括 DATE、DATETIME、TIMESTAMPTZ（如 '2023-12-31'、'2023-12-31
23:59:59'、'2023-12-31 23:59:59+08:00'）。
- 时间间隔由数值（`expre`）和单位（`time_unit`）共同指定，`expr` 为正数时表示“添加”，为负数时等效于“减去”对应间隔。
+该函数与 MySQL
的[DATE_ADD函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)行为一致。
+
## 别名
- days_add
@@ -21,7 +23,7 @@ DATE_ADD 函数用于向指定的日期或时间值添加指定的时间间隔
## 语法
```sql
-DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
+DATE_ADD(<date_or_time_expr>, INTERVAL <expr> <time_unit>)
```
## 参数
@@ -29,8 +31,35 @@ DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
| 参数 | 说明 |
| -- | -- |
| `<date_or_time_expr>` | 待处理的日期/时间值。支持类型：为 timestamptz, datetime 或者 date
类型，最高有六位秒数的精度（如 2022-12-28 23:59:59.999999），具体格式请查看
[timestamptz的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)|
-| `<expr>` | 希望添加的时间间隔，为 `INT` 类型|
-| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
DAY_SECOND, DAY_HOUR, MINUTE_SECOND, SECOND_MICROSECOND |
+| `<expr>` | 希望添加的时间间隔，对于独立单位(如`YEAR`)为 `INT` 类型; 对于复合单位(如`YEAR_MONT`)为字符串类型,
且接受所有非数字作为分隔符，所以对于例如`INTERVAL 6/4 HOUR_MINUTE`，Doris会将其识别为 6 小时 4
分，而非1小时30分(6/4 == 1.5)。对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为
0。该值的正负性仅由第一个出现的非数字字符是否为`-`决定。|
+| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND。|
+
+| time_unit | 预期格式(接受所有非数字作为分隔符) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
+
+:::note
+复合单位除`MINUTE`, `SECOND`, `DAY_SECOND`, `DAY_HOUR`, `MINUTE_SECOND`,
`SECOND_MICROSECOND`, 其余从4.0.4开始支持。
+:::
## 返回值
@@ -43,6 +72,7 @@ DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
特殊情况：
- 任何参数为 NULL 时，返回 NULL；
- 非法单位或非数值 expr 时，报错；
+- 对于复合单位，如果输入的部分过多或其中某一部分超出允许最大值922337203685477579, 报错。
- 计算后超出日期类型范围（如 '0000-00-00 23:59:59' 之前，'9999-12-31 23:59:59' 之后）时，返回错误。
- 若是下月不足输入日期的天数，会自动设置为下月最后一天
@@ -113,6 +143,14 @@ mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1
2' DAY_HOUR);
| 2025-10-24 12:10:10 |
+----------------------------------------------------------+
+-- 对于复合单位, 接受所有非数字作为分隔符
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-24 12:10:10 |
++----------------------------------------------------------------+
+
-- 添加 MINUTE_SECOND
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
@@ -129,7 +167,26 @@ mysql> select date_add("2025-10-10 10:10:10.123456",
INTERVAL "1.1" SECOND_MICR
| 2025-10-10 10:10:11.223456 |
+---------------------------------------------------------------------------+
--- TimestampTz类型示例，SET time_zone = '+08:00'
+-- 对于复合单位，时间间隔的正负性仅由第一个出现的非数字字符是否为`-`决定
+-- 后续的所有 `-` 会被认为是分隔符的一部分
+select
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:11:11 | 2025-10-23 10:09:09 |
++---------------------+---------------------+
+
+-- 对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为 0
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:11 |
++-------------------------+
+
+-- TimestampTz类型示例, SET time_zone = '+08:00'
select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1 DAY);
+-------------------------------------------------------+
| DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1 DAY) |
@@ -137,6 +194,15 @@ select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1
DAY);
| 2023-01-03 04:22:33+08:00 |
+-------------------------------------------------------+
+-- 对于复合单位，如果输入时间间隔数量过多，报错
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of 1:2:3.4 is
invalid
+
+-- 对于复合单位, 如果其中一部分的值超过最大值922337203685477580，报错
+select DATE_ADD('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
922337203685477580 out of range
+
+
---非法单位
select DATE_ADD('2023-12-31 23:00:00', INTERVAL 2 sa);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
index 069fc1b88c2..8f3f3b80b1c 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
@@ -10,13 +10,7 @@
DATE_SUB 函数用于从指定的日期或时间值中减去指定的时间间隔，返回计算后的日期或时间结果。该函数支持对 DATE（仅日期）,
DATETIME（日期和时间）TIMESTAMPTZ(带时区偏移量的日期时间)类型进行操作，时间间隔通过数值和单位共同定义。
-该函数与 mysql 中的 [date_sub
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
行为大致一致，但不同的是，mysql 支持联合单位的增减，如:
-
-```sql
-SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
- -> '2024-12-30 22:58:59'
-```
-doris 不支持这种输入。
+该函数与 mysql 中的 [date_sub
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
行为一致。
## 别名
@@ -26,7 +20,7 @@ doris 不支持这种输入。
## 语法
```sql
-DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
+DATE_SUB(<date_or_time_part>, INTERVAL <expr> <time_unit>)
```
## 参数
@@ -34,8 +28,36 @@ DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
| 参数 | 说明 |
| -- | -- |
| `<date_or_time_part>` | 合法的日期值，支持为 timestamptz, datetime 或者 date 类型，具体格式请查看
[timestamptz的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion),
[datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-| `<expr>` | 希望减去的时间间隔，类型为 `INT` |
-| `<time_unit>` | 枚举值：YEAR，QUARTER，MONTH，WEEK，DAY，HOUR，MINUTE，SECOND |
+| `<expr>` | 希望减去的时间间隔，类型为 `INT`, 对于独立单位(如`YEAR`)为 `INT` 类型;
对于复合单位(如`YEAR_MONT`)为字符串类型, 且接受所有非数字作为分隔符，所以对于例如`INTERVAL 6/4
HOUR_MINUTE`，Doris会将其识别为 6 小时 4 分，而非1小时30分(6/4 == 1.5)。对于复合单位,
如果输入的时间间隔值过短，会将空出的大单位的值设为 0。该值的正负性仅由第一个出现的非数字字符是否为`-`决定。|
+| `<time_unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND。 |
+
+
+| time_unit | 预期格式(接受所有非数字作为分隔符) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
+
+:::note
+复合单位除`MINUTE`, `SECOND`, `DAY_SECOND`, `DAY_HOUR`, `MINUTE_SECOND`,
`SECOND_MICROSECOND`, 其余从4.0.4开始支持。
+:::
## 返回值
@@ -48,6 +70,7 @@ DATE_ADD(<date_or_time_part>, <expr> <time_unit>)
特殊情况：
- 任何参数为 NULL 时，返回 NULL；
- 非法单位，返回错误。
+- 对于复合单位，如果输入的部分过多或其中某一部分超出允许最大值922337203685477579, 报错。
- 计算结果早于日期类型支持的最小值（如 '0000-01-01' 之前），返回错误。
## 举例
@@ -110,7 +133,7 @@ mysql> select date_sub('2023-01-01', INTERVAL NULL DAY);
| NULL |
+-------------------------------------------+
--- TimeStampTz 类型测试，SET time_zone = '+08:00'
+-- TimeStampTz 类型示例，SET time_zone = '+08:00'
SELECT DATE_SUB('2024-02-05 02:03:04.123+12:00', INTERVAL 1 DAY);
+-----------------------------------------------------------+
| DATE_SUB('2024-02-05 02:03:04.123+12:00', INTERVAL 1 DAY) |
@@ -118,6 +141,41 @@ SELECT DATE_SUB('2024-02-05 02:03:04.123+12:00', INTERVAL
1 DAY);
| 2024-02-03 22:03:04.123+08:00 |
+-----------------------------------------------------------+
+-- 对于复合单位, 接受所有非数字作为分隔符
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-22 08:10:10 |
++----------------------------------------------------------------+
+
+-- 对于复合单位，时间间隔的正负性仅由第一个出现的非数字字符是否为`-`决定
+-- 后续的所有 `-` 会被认为是分隔符的一部分
+select
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:09:09 | 2025-10-23 10:11:11 |
++---------------------+---------------------+
+
+-- 对于复合单位, 如果输入的时间间隔值过短，会将空出的大单位的值设为 0
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:09 |
++-------------------------+
+
+-- 对于复合单位，如果输入时间间隔数量过多，报错
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of -1:2:3.4 is
invalid
+
+-- 对于复合单位, 如果其中一部分的值超过最大值922337203685477580，报错
+select DATE_SUB('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
-922337203685477580 out of range
+
--非法单位，返回错误
mysql> select date_sub('2022-01-01', INTERVAL 1 Y);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
index 5e943fda8de..6868d1e77fd 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
@@ -10,12 +10,11 @@
`EXTRACT` 函数用于从日期或时间值中提取指定的时间组件，如年份、月份、周、日、小时、分钟、秒等。该函数可精确获取日期时间中的特定部分。
-该函数与 mysql 中的 [extract
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
行为基本一致，不同的是，doris 目前暂不支持联合单位输入，如：
+该函数与 mysql 中的 [extract
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
行为一致。
-```sql
-mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
- -> 201907
-```
+:::note
+复合单位的提取，如`YEAR_MONTH`从4.0.4开始支持。
+:::
## 语法
@@ -25,12 +24,14 @@ mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02
01:02:03');
| 参数 | 说明 |
| -- | -- |
-| `<unit>` | 提取 DATETIME 某个指定单位的值。单位可以为 year, month, week, day, hour, minute,
second 或者 microsecond |
+| `<unit>` | 枚举值：YEAR, QUARTER, MONTH, WEEK,DAY, HOUR, MINUTE, SECOND,
YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, DAYOFWEEK(DOW), DAYOFYEAR(DOY) |
| `<datetime_or_time_expr>` | 参数是合法的日期表达式，支持输入 date/datetime
类型和符合日期时间格式的字符串，具体 datetime 和 date 格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
## 返回值
-返回的是提取出的日期或时间的某个部分,类型为 INT，具体的部分取决于提取的单位。
+返回的是提取出的日期或时间的某个部分。
+- 对于独立类型如`YEAR`和[DAYOFWEEK(DOW)](./dayofweek.md),
[DAYOFYEAR(DOY)](./dayofyear.md)， 返回类型为 INT
+- 对于复合类型如`YEAR_MONTH`, 返回类型为 STRING
week 单位的取值范围为 0-53，计算规则如下：
@@ -42,9 +43,23 @@ week 单位的取值范围为 0-53，计算规则如下：
单位为 quarter 时，1-3 月返回 1，4-6 月返回2， 7-9 月返回 3，10-12 返回 4.
特殊情况：
-
-若 <date_or_time_expr> 为 NULL，返回 NULL。
-若 <unit> 为不支持单位，报错
+- 若 <date_or_time_expr> 为 NULL，返回 NULL。
+- 若 <unit> 为不支持单位，报错
+
+复合单位返回格式如下：
+| time_unit | 返回格式 |
+| ------------------ | ----------------------------------------- |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## 举例
@@ -96,6 +111,24 @@ select extract(week from '2024-12-31') as week;
| 52 |
+------+
+select extract(year_month from '2026-01-01 11:45:14.123456') as year_month,
+ extract(day_hour from '2026-01-01 11:45:14.123456') as day_hour,
+ extract(day_minute from '2026-01-01 11:45:14.123456') as day_minute,
+ extract(day_second from '2026-01-01 11:45:14.123456') as day_second,
+ extract(day_microsecond from '2026-01-01 11:45:14.123456') as
day_microsecond,
+ extract(hour_minute from '2026-01-01 11:45:14.123456') as hour_minute,
+ extract(hour_second from '2026-01-01 11:45:14.123456') as hour_second,
+ extract(hour_microsecond from '2026-01-01 11:45:14.123456') as
hour_microsecond,
+ extract(minute_second from '2026-01-01 11:45:14.123456') as
minute_second,
+ extract(minute_microsecond from '2026-01-01 11:45:14.123456') as
minute_microsecond,
+ extract(second_microsecond from '2026-01-01 11:45:14.123456') as
second_microsecond;
+
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| year_month | day_hour | day_minute | day_second | day_microsecond |
hour_minute | hour_second | hour_microsecond | minute_second|
minute_microsecond | second_microsecond |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| 2026-01 | 1 11 | 1 11:45 | 1 11:45:14 | 1 11:45:14.123456 |
11:45 | 11:45:14 | 11:45:14.123456 | 45:14 | 45:14.123456
| 14.123456 |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+
-- 输入单位不存在，报错
select extract(uint from '2024-01-07') as week;
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not found function 'uint'
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
index faa4921a33a..4b098a8361c 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-add.md
@@ -13,6 +13,8 @@ The DATE_ADD function is used to add a specified time
interval to a specified da
- Supported input date types include DATE, DATETIME, TIMESTAMPTZ (such as
'2023-12-31', '2023-12-31 23:59:59', '2023-12-31 23:59:59+08:00').
- The time interval is specified by both a numeric value (`expr`) and a unit
(`time_unit`). When `expr` is positive, it means "add", and when it is
negative, it is equivalent to "subtract" the corresponding interval.
+This function behaves consistently with the [date_add
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)
in MySQL.
+
## Aliases
- days_add
@@ -21,16 +23,43 @@ The DATE_ADD function is used to add a specified time
interval to a specified da
## Syntax
```sql
-DATE_ADD(<date_or_time_expr>, <expr> <time_unit>)
+DATE_ADD(<date_or_time_expr>, INTERVAL <expr> <time_unit>)
```
## Parameters
| Parameter | Description |
| -- | -- |
-| `<date_or_time_expr>` | The date/time value to be processed. Supported
types: datetime or date type, with a maximum precision of six decimal places
for seconds (e.g., 2022-12-28 23:59:59.999999). For specific formats, please
refer to [timestamptz
conversion](../../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion.md),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date conversion](. [...]
-| `<expr>` | The time interval to be added, of `INT` type |
-| `<time_unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, DAY_SECOND, DAY_HOUR, MINUTE_SECOND, SECOND_MICROSECOND |
+| `<date_or_time_expr>` | The date/time value to be processed. Supported
types: datetime or date type, with a maximum precision of six decimal places
for seconds (e.g., 2022-12-28 23:59:59.999999). For specific formats, please
refer to [timestamptz
conversion](../../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/time-conversion),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date conversion](../../../.. [...]
+| `<expr>` | The time interval to be added, for independent units (such as
`YEAR`) are of `INT` type; for compound units (such as `YEAR_MONTH`) are of
`STRING` type, and accept all non-numeric characters as separators. Therefore,
for example, `INTERVAL 6/4 HOUR_MINUTE` will be recognized as 6 hours 4 minutes
by Doris, rather than 1 hour 30 minutes (6/4 == 1.5). For compound units, if
the input interval value is too short, the value of the larger unit will be set
to 0. The sign of this va [...]
+| `<time_unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND |
+
+| time_unit | Expected format (accepts all non-numeric characters as
separators) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
+
+:::note
+Composite units except `MINUTE`, `SECOND`, `DAY_SECOND`, `DAY_HOUR`,
`MINUTE_SECOND`, and `SECOND_MICROSECOND` are supported from version 4.0.4.
+:::
## Return Value
@@ -43,6 +72,7 @@ Returns a result with the same type as <date_or_time_expr>:
Special cases:
- When any parameter is NULL, returns NULL;
- When illegal unit or non-numeric expr, returns an error;
+- For composite units, if the input parts are excessive or any part exceeds
the allowed maximum value 922337203685477579, returns an error.
- When the calculation result exceeds the date type range (such as before
'0000-00-00 23:59:59' or after '9999-12-31 23:59:59'), returns an error.
- If the next month does not have enough days for the input date, it will
automatically be set to the last day of the next month.
@@ -114,6 +144,14 @@ mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1
2' DAY_HOUR);
| 2025-10-24 12:10:10 |
+----------------------------------------------------------+
+-- For compound units, accept all non-numeric characters as separators.
+mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_ADD('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-24 12:10:10 |
++----------------------------------------------------------------+
+
-- Add MINUTE_SECOND
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
@@ -130,6 +168,25 @@ mysql> select date_add("2025-10-10 10:10:10.123456",
INTERVAL "1.1" SECOND_MICR
| 2025-10-10 10:10:11.223456 |
+---------------------------------------------------------------------------+
+-- For composite units, the sign of the time interval is determined only by
whether the first non-digit character is `-`
+-- All subsequent `-` are considered part of the delimiter
+mysql> select
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_ADD('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:11:11 | 2025-10-23 10:09:09 |
++---------------------+---------------------+
+
+-- For composite units, if the input time interval is too short, the value of
the larger unit will be set to 0.
+mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:11 |
++-------------------------+
+
-- Example of TimestampTz type, SET time_zone = '+08:00'
select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1 DAY);
+-------------------------------------------------------+
@@ -138,6 +195,15 @@ select DATE_ADD('2023-01-01 23:22:33+03:00', INTERVAL 1
DAY);
| 2023-01-03 04:22:33+08:00 |
+-------------------------------------------------------+
+-- If the number of time intervals input is excessive, return an error
+select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of 1:2:3.4 is
invalid
+
+-- For composite units, if the value of any part exceeds the maximum value of
922337203685477580
+-- return an error
+select DATE_ADD('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
922337203685477580 out of range
+
-- Illegal unit
select DATE_ADD('2023-12-31 23:00:00', INTERVAL 2 sa);
ERROR 1105 (HY000): errCode = 2, detailMessage =
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
index adc570f9740..05aacea082d 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/date-sub.md
@@ -10,13 +10,7 @@
The DATE_SUB function is used to subtract a specified time interval from a
given date or time value and return the calculated date or time result. It
supports operations on DATE (date only), DATETIME (date and time) and
TIMESTAMPTZ(date, time, and timezone offset) types, where the time interval is
defined by both a numerical value and a unit.
-This function behaves generally consistently with the [date_sub
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
in MySQL, but the difference is that MySQL supports compound unit additions
and subtractions, such as:
-
-```sql
-SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
- -> '2024-12-30 22:58:59'
-```
-Doris does not support this type of input.
+This function behaves consistently with the [date_sub
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
in MySQL.
## Aliases
@@ -26,16 +20,43 @@ Doris does not support this type of input.
## Syntax
```sql
-DATE_SUB(<date_or_time_part>, <expr> <time_unit>)
+DATE_SUB(<date_or_time_part>, INTERVAL <expr> <time_unit>)
```
## Parameters
| Parameter | Description |
| -- | -- |
-| `<date_or_time_part>` | A valid date value, supporting datetime or date
type. For specificformats, please refer to [timestamptz
conversion](../../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/timestamptz-conversion.md),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-| `<expr>` | The time interval to be subtracted, of type `INT` |
-| `<time_unit>` | Enumerated values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND |
+| `<date_or_time_part>` | A valid date value, supporting datetime or date
type. For specificformats, please refer to [timestamptz
conversion](../../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/time-conversion),
[datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<expr>` | The time interval to be subtracted, for independent units (such
as `YEAR`) are of `INT` type; for compound units (such as `YEAR_MONTH`) are of
`STRING` type, and accept all non-numeric characters as separators. Therefore,
for example, `INTERVAL 6/4 HOUR_MINUTE` will be recognized as 6 hours 4 minutes
by Doris, rather than 1 hour 30 minutes (6/4 == 1.5). For compound units, if
the input interval value is too short, the value of the larger unit will be set
to 0. The sign of th [...]
+| `<time_unit>` | Enumerated values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND |
+
+| time_unit | Expected format (accepts all non-numeric characters as
separators) |
+| ------------------ | ----------------------------------------- |
+| YEAR | 'YEARS' |
+| QUARTER | 'QUARTERS' |
+| MONTH | 'MONTHS' |
+| WEEK | 'WEEKS' |
+| DAY | 'DAYS' |
+| HOUR | 'HOURS' |
+| MINUTE | 'MINUTES' |
+| SECOND | 'SECONDS' |
+| MICROSECOND | 'MICROSECONDS' |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
+
+:::note
+Composite units except `MINUTE`, `SECOND`, `DAY_SECOND`, `DAY_HOUR`,
`MINUTE_SECOND`, and `SECOND_MICROSECOND` are supported from version 4.0.4.
+:::
## Return Value
@@ -48,6 +69,7 @@ Returns a calculated result with the same type as date:
Special cases:
- Returns NULL if any parameter is NULL;
- Returns NULL for illegal expr (negative values) or time_unit;
+- For composite units, if the input parts are excessive or any part exceeds
the allowed maximum value 922337203685477579, returns an error.
- Returns an error if the calculated result is earlier than the minimum value
supported by the date type (e.g., before '0000-01-01').
## Examples
@@ -118,6 +140,44 @@ mysql> select date_sub('2023-01-01', INTERVAL NULL DAY);
| NULL |
+-------------------------------------------+
+
+-- For compound units, accept all non-numeric characters as separators.
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR);
++----------------------------------------------------------------+
+| DATE_SUB('2025-10-23 10:10:10', INTERVAL ' *1@#$2' DAY_HOUR) |
++----------------------------------------------------------------+
+| 2025-10-22 08:10:10 |
++----------------------------------------------------------------+
+
+-- For composite units, the sign of the time interval is determined only by
whether the first non-digit character is `-`
+-- All subsequent `-` are considered part of the delimiter
+select
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL '#-1:-1' MINUTE_SECOND) AS
first_not_sub,
+ DATE_SUB('2025-10-23 10:10:10', INTERVAL ' -1:1' MINUTE_SECOND) AS
first_sub;
++---------------------+---------------------+
+| first_not_sub | first_sub |
++---------------------+---------------------+
+| 2025-10-23 10:09:09 | 2025-10-23 10:11:11 |
++---------------------+---------------------+
+
+-- For composite units, if the input time interval is too short, the value of
the larger unit will be set to 0.
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1' MINUTE_SECOND) AS
minute_interval_is_zero
++-------------------------+
+| minute_interval_is_zero |
++-------------------------+
+| 2025-10-23 10:10:09 |
++-------------------------+
+
+-- If the number of time intervals input is excessive, return an error
+select DATE_SUB('2025-10-23 10:10:10', INTERVAL '1:2:3.4' SECOND_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation second_microsecond_add of -1:2:3.4 is
invalid
+
+-- For composite units, if the value of any part exceeds the maximum value of
922337203685477580
+-- return an error
+select DATE_SUB('2025-10-10 1:2:3', INTERVAL '922337203685477580'
DAY_MICROSECOND);
+-- ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[E-218]Operation day_microsecond_add of 2025-10-10 01:02:03,
-922337203685477580 out of range
+
+
-- Exceeds minimum date
mysql> select date_sub('0000-01-01', INTERVAL 1 DAY);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
day_add of 0000-01-01, -1 out of range
diff --git
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
index dc0f0b8c457..1200bba9908 100644
---
a/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
+++
b/versioned_docs/version-4.x/sql-manual/sql-functions/scalar-functions/date-time-functions/extract.md
@@ -10,12 +10,11 @@
The `EXTRACT` function is used to extract specific time components from date
or time values, such as year, month, week, day, hour, minute, second, etc. This
function can precisely obtain specific parts of a datetime.
-This function behaves mostly consistently with the [extract
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
in MySQL. The difference is that Doris currently does not support combined
unit inputs, such as:
+This function behaves consistently with the [extract
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_extract)
in MySQL.
-```sql
-mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
- -> 201907
-```
+:::note
+Extraction of compound units, such as `YEAR_MONTH`, is supported since version
4.0.4.
+:::
## Syntax
@@ -25,12 +24,14 @@ mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02
01:02:03');
| Parameter | Description |
| -- | -- |
-| `<unit>` | Extract the value of a specified unit from DATETIME. The unit can
be year, month, week, day, hour, minute, second, or microsecond |
+| `<unit>` | Enumeration values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,
MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, DAYOFWEEK(DOW), DAYOFYEAR(DOY) |
| `<datetime_or_time_expr>` | A valid date expression that supports
date/datetime types and strings in date-time format. For specific datetime and
date formats, please refer to [datetime
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
## Return Value
-Returns the extracted part of the date or time, of type INT, depending on the
extracted unit.
+Returns the extracted part of the date or time.
+- For independent types like `YEAR` and [DAYOFWEEK(DOW)](./dayofweek.md),
[DAYOFYEAR(DOY)](./dayofyear.md), the return type is INT
+- For composite types like `YEAR_MONTH`, the return type is STRING
The value range for the week unit is 0-53, calculated as follows:
@@ -43,9 +44,23 @@ When the unit is year, month, day, hour, minute, second,
microsecond, it returns
When the unit is quarter, January-March returns 1, April-June returns 2,
July-September returns 3, October-December returns 4.
Special cases:
-
-If <date_or_time_expr> is NULL, returns NULL.
-If <unit> is an unsupported unit, an error is reported.
+- If <date_or_time_expr> is NULL, returns NULL.
+- If <unit> is an unsupported unit, an error is reported.
+
+The format of the composite unit return is as follows:
+| time_unit | return format |
+| ------------------ | ----------------------------------------- |
+| YEAR_MONTH | 'YEARS-MONTHS' |
+| DAY_HOUR | 'DAYS HOURS' |
+| DAY_MINUTE | 'DAYS HOURS:MINUTES' |
+| DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
+| DAY_MICROSECOND | 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| HOUR_MINUTE | 'HOURS:MINUTES' |
+| HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
+| HOUR_MICROSECOND | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
+| MINUTE_SECOND | 'MINUTES:SECONDS' |
+| MINUTE_MICROSECOND | 'MINUTES:SECONDS.MICROSECONDS' |
+| SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
## Examples
@@ -97,6 +112,24 @@ select extract(week from '2024-12-31') as week;
| 52 |
+------+
+select extract(year_month from '2026-01-01 11:45:14.123456') as year_month,
+ extract(day_hour from '2026-01-01 11:45:14.123456') as day_hour,
+ extract(day_minute from '2026-01-01 11:45:14.123456') as day_minute,
+ extract(day_second from '2026-01-01 11:45:14.123456') as day_second,
+ extract(day_microsecond from '2026-01-01 11:45:14.123456') as
day_microsecond,
+ extract(hour_minute from '2026-01-01 11:45:14.123456') as hour_minute,
+ extract(hour_second from '2026-01-01 11:45:14.123456') as hour_second,
+ extract(hour_microsecond from '2026-01-01 11:45:14.123456') as
hour_microsecond,
+ extract(minute_second from '2026-01-01 11:45:14.123456') as
minute_second,
+ extract(minute_microsecond from '2026-01-01 11:45:14.123456') as
minute_microsecond,
+ extract(second_microsecond from '2026-01-01 11:45:14.123456') as
second_microsecond;
+
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| year_month | day_hour | day_minute | day_second | day_microsecond |
hour_minute | hour_second | hour_microsecond | minute_second|
minute_microsecond | second_microsecond |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+| 2026-01 | 1 11 | 1 11:45 | 1 11:45:14 | 1 11:45:14.123456 |
11:45 | 11:45:14 | 11:45:14.123456 | 45:14 | 45:14.123456
| 14.123456 |
++------------+----------+------------+-------------+-----------------------+-------------+-------------+-----------------------+--------------+----------------------+-------------------+
+
-- Input unit does not exist, reports error
select extract(uint from '2024-01-07') as week;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
