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 807fdf0feb0 Refactor datelike types docs (#2737)
807fdf0feb0 is described below
commit 807fdf0feb02a6bcec327b18d5b5f319e09b4104
Author: zclllyybb <[email protected]>
AuthorDate: Tue Aug 12 23:00:57 2025 +0800
Refactor datelike types docs (#2737)
## Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
---
.../basic-element/sql-data-types/date-time/DATE.md | 47 ++++++++-----
.../sql-data-types/date-time/DATETIME.md | 77 ++++++++++++----------
.../basic-element/sql-data-types/date-time/TIME.md | 53 +++++++--------
.../basic-element/sql-data-types/date-time/DATE.md | 43 ++++++++----
.../sql-data-types/date-time/DATETIME.md | 77 +++++++++++-----------
.../basic-element/sql-data-types/date-time/TIME.md | 51 +++++++-------
6 files changed, 194 insertions(+), 154 deletions(-)
diff --git a/docs/sql-manual/basic-element/sql-data-types/date-time/DATE.md
b/docs/sql-manual/basic-element/sql-data-types/date-time/DATE.md
index 3b15f0b32a8..c55240bd302 100644
--- a/docs/sql-manual/basic-element/sql-data-types/date-time/DATE.md
+++ b/docs/sql-manual/basic-element/sql-data-types/date-time/DATE.md
@@ -5,25 +5,42 @@
}
---
-## DATE
+## Description
-DATE
+DATE type stores dates, with a value range of `[0000-01-01, 9999-12-31]`, and
the default output format is 'yyyy-MM-dd'.
-### Description
-#### Syntax
-date
-Date type, the current range of values is ['0000-01-01','9999-12-31'], and the
default print form is 'yyyy-MM-dd'.
+Doris uses the Gregorian calendar date format, and the dates existing in the
Gregorian calendar correspond one by one to the dates existing in Doris, where
`0000` represents 1 BC (BCE 1).
-### example
+DATE type can be used as a primary key, partition column or bucket column. A
DATE type field actually occupies 4 bytes in Doris. DATE is stored separately
by year、month、day in runtime, so executing `months_add` operation on DATE
column is more efficient than `unix_timestamp`.
+
+How to convert other types to DATE, and the input accepted during conversion,
see [Cast to DATE](../conversion/date-conversion.md).
+
+Date and time types do not support direct use of mathematical operators for
arithmetic operations. The essence of performing mathematical operations is to
first implicitly convert the date and time types to numeric types, and then
perform the operation. If you need to perform addition, subtraction, or
rounding on time types, consider using functions like
[DATE_ADD](../../../sql-functions/scalar-functions/date-time-functions/date-add.md),
[DATE_SUB](../../../sql-functions/scalar-functions [...]
+
+TIME type does not store time zone, that is, changes in the session variable
`time_zone` do not affect the stored values of TIME type.
+
+## Examples
+
+```sql
+select cast('2020-01-02' as date);
+```
+
+```text
++----------------------------+
+| cast('2020-01-02' as date) |
++----------------------------+
+| 2020-01-02 |
++----------------------------+
+```
```sql
-SELECT DATE('2003-12-31 01:02:03');
-+-----------------------------+
-| DATE('2003-12-31 01:02:03') |
-+-----------------------------+
-| 2003-12-31 |
-+-----------------------------+
+select cast('0120-02-29' as date);
```
-### keywords
-DATE
+```text
++----------------------------+
+| cast('0120-02-29' as date) |
++----------------------------+
+| 0120-02-29 |
++----------------------------+
+```
diff --git a/docs/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
b/docs/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
index 5af73efba6c..642b2ae104b 100644
--- a/docs/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
+++ b/docs/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
@@ -5,51 +5,56 @@
}
---
-## DATETIME
+## Description
-DATETIME
+The DATETIME(p) type stores date and time, where p is the precision, with the
range of values for p being `[0, 6]`, and the default value is 0. That is,
DATETIME is equivalent to DATETIME(0).
-### Description
+The range is `[0000-01-01 00:00:00.000..., 9999-12-31 23:59:59.999...]`, and
the default output format is 'yyyy-MM-dd HH:mm:ss.SSS...'. There are a total of
p digits after the decimal point. For example, the range of DATETIME(6) is
`[0000-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]`.
-DATETIME([P])
-Date and time type.
-The optional parameter P indicates the time precision and the value range is
[0, 6], that is, it supports up to 6 decimal places (microseconds). 0 when not
set.
-Value range is ['0000-01-01 00:00:00[.000000]','9999-12-31 23:59:59[.999999]'].
-The form of printing is 'yyyy-MM-dd HH:mm:ss.SSSSSS'
+Doris uses the Gregorian calendar date format, and the dates existing in the
Gregorian calendar correspond one by one to the dates existing in Doris, where
`0000` represents 1 BC (BCE 1). No matter which day the date is on, the time
range is always `['00:00:00.000...', '23:59:59.999...']`, and there are no
duplicate times, i.e., no leap seconds.
-### note
+DATETIME type can be used as a primary key, partition column or bucket column.
A DATETIME type field actually occupies 8 bytes in Doris. DATETIME is stored
separately by year, month, day, hour, minute, second and microsecond in
runtime, so executing `months_add` operation on DATETIME column is more
efficient than `unix_timestamp`.
+
+How to convert other types to DATETIME, and the input accepted during
conversion, see [Cast to DATETIME](../conversion/datetime-conversion.md).
+
+Date and time types do not support direct use of mathematical operators for
arithmetic operations. The essence of performing mathematical operations is to
first implicitly convert the date and time types to numeric types, and then
perform the operation. If you need to perform addition, subtraction, or
rounding on time types, consider using functions like
[DATE_ADD](../../../sql-functions/scalar-functions/date-time-functions/date-add.md),
[DATE_SUB](../../../sql-functions/scalar-functions [...]
+
+DATETIME type does not store time zone, that is, changes in the session
variable `time_zone` do not affect the stored values of DATETIME type.
+
+## Examples
-DATETIME supports temporal precision up to microseconds. When parsing imported
DATETIME type data using the BE side (e.g. using Stream load, Spark load,
etc.), or using the FE side with the [Nereids](/docs/query/nereids/nereids-new)
on, decimals exceeding the current precision will be **rounded**.
-Inserting a DATETIME value with a fractional seconds part into a column of the
same type but having fewer fractional digits results in **rounded**.
-DATETIME reads support resolving the time zone in the format of the original
DATETIME literal followed by the time zone:
```sql
-<date> <time>[<timezone>]
+select cast('2020-01-02' as datetime);
```
-For the specific supported formats for `<timezone>`, see
[timezone](../../../../admin-manual/cluster-management/time-zone). Note that
the `DATE`, `DATEV2`, `DATETIME`, and `DATETIMEV2` types **don't** contain time
zone information. For example, if an input time string "2012-12-12
08:00:00+08:00" is parsed and converted to the current time zone "+02:00", and
the actual value "2012-12-12 02:00:00" is stored in the DATETIME column, the
value itself will not change, no matter how much the cl [...]
+```text
++--------------------------------+
+| cast('2020-01-02' as datetime) |
++--------------------------------+
+| 2020-01-02 00:00:00 |
++--------------------------------+
+```
-### example
+```sql
+select cast('2020-01-02' as datetime(6));
+```
+
+```text
++-----------------------------------+
+| cast('2020-01-02' as datetime(6)) |
++-----------------------------------+
+| 2020-01-02 00:00:00.000000 |
++-----------------------------------+
+```
```sql
-mysql> select @@time_zone;
-+----------------+
-| @@time_zone |
-+----------------+
-| Asia/Hong_Kong |
-+----------------+
-1 row in set (0.11 sec)
-
-mysql> insert into dtv23 values ("2020-12-12 12:12:12Z"), ("2020-12-12
12:12:12GMT"), ("2020-12-12 12:12:12+02:00"), ("2020-12-12
12:12:12America/Los_Angeles");
-Query OK, 4 rows affected (0.17 sec)
-
-mysql> select * from dtv23;
-+-------------------------+
-| k0 |
-+-------------------------+
-| 2020-12-12 20:12:12.000 |
-| 2020-12-12 20:12:12.000 |
-| 2020-12-13 04:12:12.000 |
-| 2020-12-12 18:12:12.000 |
-+-------------------------+
-4 rows in set (0.15 sec)
+select cast('0000-12-31 22:21:20.123456' as datetime(4));
+```
+
+```text
++---------------------------------------------------+
+| cast('0000-12-31 22:21:20.123456' as datetime(4)) |
++---------------------------------------------------+
+| 0000-12-31 22:21:20.1235 |
++---------------------------------------------------+
```
diff --git a/docs/sql-manual/basic-element/sql-data-types/date-time/TIME.md
b/docs/sql-manual/basic-element/sql-data-types/date-time/TIME.md
index 160046ac7cd..4c3e2f4c7e3 100644
--- a/docs/sql-manual/basic-element/sql-data-types/date-time/TIME.md
+++ b/docs/sql-manual/basic-element/sql-data-types/date-time/TIME.md
@@ -5,39 +5,40 @@
}
---
-## TIME
+## Description
-### name
+The TIME(p) type stores time, where p is the precision, with the range of
values for p being `[0, 6]`, and the default value is 0. That is, TIME is
equivalent to TIME(0).
-TIME
+The range is `[-838:59:59.999..., 838:59:59.999...]`, and the default output
format is 'HH:mm:ss.SSS...' There are a total of p digits after the decimal
point. For example, the range of TIME(6) is `[-838:59:59.999999,
838:59:59.999999]`。
-### description
+The TIME type only appears as an intermediate calculation value, can be input
and output, but **does not** support being stored as a column in OLAP tables.
-TIME type
- Time type, can appear as a query result, does not support table storage
for the time being. The storage range is `[-838:59:59, 838:59:59]`.
- Currently in Doris, the correctness of TIME as a result of calculations is
guaranteed (e.g., functions such as `timediff`), but **manual CAST generation
of the TIME type is not recommended**.
- The calculation of TIME type in constant folding is prohibited.
+How to convert other types to TIME, and the input accepted during conversion,
see [Cast to TIME](../conversion/time-conversion.md).
-### example
+Date and time types do not support direct use of mathematical operators for
arithmetic operations. The essence of performing mathematical operations is to
first implicitly convert the date and time types to numeric types, and then
perform the operation.
+
+## Examples
```sql
-mysql> select timediff('2020-01-01 12:05:03', '2020-01-01 08:02:15');
-+------------------------------------------------------------------------------------------------------+
-| timediff(cast('2020-01-01 12:05:03' as DATETIMEV2(0)), cast('2020-01-01
08:02:15' as DATETIMEV2(0))) |
-+------------------------------------------------------------------------------------------------------+
-| 04:02:48
|
-+------------------------------------------------------------------------------------------------------+
-1 row in set (0.12 sec)
-
-mysql> select timediff('2020-01-01', '2000-01-01');
-+------------------------------------------------------------------------------------+
-| timediff(cast('2020-01-01' as DATETIMEV2(0)), cast('2000-01-01' as
DATETIMEV2(0))) |
-+------------------------------------------------------------------------------------+
-| 838:59:59
|
-+------------------------------------------------------------------------------------+
-1 row in set (0.11 sec)
+select cast('-123:00:02.9' as time);
+```
+
+```text
++------------------------------+
+| cast('-123:00:02.9' as time) |
++------------------------------+
+| -123:00:03 |
++------------------------------+
```
-### keywords
+```sql
+select cast('838:59:59.999999' as time(6));
+```
- TIME
+```text
++-------------------------------------+
+| cast('838:59:59.999999' as time(6)) |
++-------------------------------------+
+| 838:59:59.999999 |
++-------------------------------------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATE.md
index 4bc3b5082bc..b639e5d9c85 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATE.md
@@ -5,27 +5,42 @@
}
---
-## DATE
+## 描述
-### name
+DATE 类型存储日期，取值范围是 `[0000-01-01, 9999-12-31]`, 默认的输出格式为 'yyyy-MM-dd'。
-DATE
+Doris 中使用公历日期规范，公历中存在的日期与 Doris 中存在的日期一一对应，其中 `0000` 年表示 1BC（公元前 1 年）。
-## 描述
- DATE类型
- 日期类型，目前的取值范围是['0000-01-01', '9999-12-31'], 默认的打印形式是'yyyy-MM-dd'
+DATE 类型可以作为主键、分区列、分桶列。一个 DATE 类型字段在 Doris 中实际占用 4 字节。DATE
在运行中实际按照年、月、日分别存储，因此在 DATE 列上执行 `months_add` 运算实际比 `unix_timestamp` 更加高效。
+
+如何将其他类型转换为 DATE，及转换时接受的输入，请见 [转换为 DATE](../conversion/date-conversion.md)。
+
+日期时间类型均不支持直接使用数学运算符进行四则运算，执行数学运算的实质是首先将日期时间类型隐式转换为数字类型，再行运算。如需对时间类型进行加减、取整，请考虑使用
[DATE_ADD](../../../sql-functions/scalar-functions/date-time-functions/date-add.md),
[DATE_SUB](../../../sql-functions/scalar-functions/date-time-functions/date-sub.md),
[TIMESTAMPDIFF](../../../sql-functions/scalar-functions/date-time-functions/timestampdiff.md),
[DATE_TRUNC](../../../sql-functions/scalar-functions/date-time-functions/date-trunc.md)
等函数。
+
+DATE 类型不存储时区，即会话变量 `time_zone` 的变化不影响存储的 DATE 类型的值。
## 举例
```sql
-SELECT DATE('2003-12-31 01:02:03');
-+-----------------------------+
-| DATE('2003-12-31 01:02:03') |
-+-----------------------------+
-| 2003-12-31 |
-+-----------------------------+
+select cast('2020-01-02' as date);
```
-### keywords
+```text
++----------------------------+
+| cast('2020-01-02' as date) |
++----------------------------+
+| 2020-01-02 |
++----------------------------+
+```
- DATE
+```sql
+select cast('0120-02-29' as date);
+```
+
+```text
++----------------------------+
+| cast('0120-02-29' as date) |
++----------------------------+
+| 0120-02-29 |
++----------------------------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
index 5dae7756287..7c05b3a0bbd 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/DATETIME.md
@@ -5,55 +5,56 @@
}
---
-## DATETIME
+## 描述
-DATETIMEV2
+DATETIME(p) 类型存储日期时间，其中 p 为精度，p 的取值范围为 `[0, 6]`，缺省值为 0。即 DATETIME 等同于
DATETIME(0)。
-## 描述
+取值范围是 `[0000-01-01 00:00:00.000..., 9999-12-31 23:59:59.999...]`, 默认的输出格式为
'yyyy-MM-dd HH:mm:ss.SSS...'。其中小数点后共 p 位。例如，DATETIME(6) 的 取值范围为 `[0000-01-01
00:00:00.000000, 9999-12-31 23:59:59.999999]`。
-DATETIME([P])
-日期时间类型，可选参数 P 表示时间精度，取值范围是[0, 6]，即最多支持 6 位小数（微秒）。不设置时为 0。
-取值范围是['0000-01-01 00:00:00[.000000]', '9999-12-31 23:59:59[.999999]'].
-打印的形式是'yyyy-MM-dd HH:mm:ss.SSSSSS'
+Doris 中使用公历日期规范，公历中存在的日期与 Doris 中存在的日期一一对应，其中 0000 年表示 1BC（公元前 1
年）。无论日期位于哪一天，时间部分的范围总是 `['00:00:00.000...', '23:59:59.999...']`，且不存在重复的时间，即没有闰秒。
-### note
+DATETIME 类型可以作为主键、分区列、分桶列。一个 DATETIME 类型字段在 Doris 中实际占用 8 字节。DATETIME
在运行中实际按照年、月、日、时、分、秒、毫秒分别存储，因此在 DATETIME 列上执行 `months_add` 运算实际比
`unix_timestamp` 更加高效。
-DATETIME 支持了最多到微秒的时间精度。在使用 BE 端解析导入的 DATETIME 类型数据时（如使用 Stream load、Spark load
等），或开启[新优化器](../../../../query-acceleration/optimization-technology-principle/query-optimizer.md)后在
FE 端解析 DATETIME 类型数据时，将会对超出当前精度的小数进行**四舍五入**。
-将带有小数秒部分的 DATETIME 值插入到具有较少小数位的相同类型的列中会导致**四舍五入**。
+如何将其他类型转换为 DATETIME，及转换时接受的输入，请见 [转换为
DATETIME](../conversion/datetime-conversion.md)。
-DATETIME 读入时支持解析时区，格式为原本 DATETIME 字面量后紧贴时区：
-```sql
-<date> <time>[<timezone>]
-```
+日期时间类型均不支持直接使用数学运算符进行四则运算，执行数学运算的实质是首先将日期时间类型隐式转换为数字类型，再行运算。如需对时间类型进行加减、取整，请考虑使用
[DATE_ADD](../../../sql-functions/scalar-functions/date-time-functions/date-add.md),
[DATE_SUB](../../../sql-functions/scalar-functions/date-time-functions/date-sub.md),
[TIMESTAMPDIFF](../../../sql-functions/scalar-functions/date-time-functions/timestampdiff.md),
[DATE_TRUNC](../../../sql-functions/scalar-functions/date-time-functions/date-trunc.md)
等函数。
-关于`<timezone>`的具体支持格式，请见[时区](../../../../admin-manual/cluster-management/time-zone)。需要注意的是，`DATE`,
`DATEV2`, `DATETIME`, `DATETIMEV2` 类型均**不**包含时区信息。例如，一个输入的时间字符串 "2012-12-12
08:00:00+08:00" 经解析并转换至当前时区 "+02:00"，得到实际值 "2012-12-12 02:00:00" 后存储于 DATETIME
列中，则之后无论本集群环境变量如何改变，该值本身都不会发生变化。
+DATETIME 类型不存储时区，即会话变量 `time_zone` 的变化不影响存储的 DATETIME 类型的值。
## 举例
```sql
-mysql> select @@time_zone;
-+----------------+
-| @@time_zone |
-+----------------+
-| Asia/Hong_Kong |
-+----------------+
-1 row in set (0.11 sec)
-
-mysql> insert into dtv23 values ("2020-12-12 12:12:12Z"), ("2020-12-12
12:12:12GMT"), ("2020-12-12 12:12:12+02:00"), ("2020-12-12
12:12:12America/Los_Angeles");
-Query OK, 4 rows affected (0.17 sec)
-
-mysql> select * from dtv23;
-+-------------------------+
-| k0 |
-+-------------------------+
-| 2020-12-12 20:12:12.000 |
-| 2020-12-12 20:12:12.000 |
-| 2020-12-13 04:12:12.000 |
-| 2020-12-12 18:12:12.000 |
-+-------------------------+
-4 rows in set (0.15 sec)
+select cast('2020-01-02' as datetime);
+```
+
+```text
++--------------------------------+
+| cast('2020-01-02' as datetime) |
++--------------------------------+
+| 2020-01-02 00:00:00 |
++--------------------------------+
+```
+
+```sql
+select cast('2020-01-02' as datetime(6));
```
-### keywords
+```text
++-----------------------------------+
+| cast('2020-01-02' as datetime(6)) |
++-----------------------------------+
+| 2020-01-02 00:00:00.000000 |
++-----------------------------------+
+```
- DATETIME
+```sql
+select cast('0000-12-31 22:21:20.123456' as datetime(4));
+```
+
+```text
++---------------------------------------------------+
+| cast('0000-12-31 22:21:20.123456' as datetime(4)) |
++---------------------------------------------------+
+| 0000-12-31 22:21:20.1235 |
++---------------------------------------------------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/TIME.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/TIME.md
index f66a8ad5ea9..9802f3cdd1a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/TIME.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/basic-element/sql-data-types/date-time/TIME.md
@@ -5,39 +5,40 @@
}
---
-## TIME
+## 描述
-### name
+TIME(p) 类型存储时间，其中 p 为精度，p 的取值范围为 `[0, 6]`，缺省值为 0。即 TIME 等同于 TIME(0)。
-TIME
+取值范围是 `[-838:59:59.999..., 838:59:59.999...]`, 默认的输出格式为
'HH:mm:ss.SSS...'。其中小数点后共 p 位。例如，TIME(6) 的 取值范围为 `[-838:59:59.999999,
838:59:59.999999]`。
-## 描述
+TIME 类型仅作为计算中间值出现，可以输入、输出，但**不支持**作为列存储到 OLAP 表中。
-TIME 类型
- 时间类型，可以作为查询结果出现，暂时不支持建表存储。表示范围为 `[-838:59:59, 838:59:59]`。
- 当前 Doris 中，TIME 作为计算结果的正确性是有保证的（如 `timediff` 等函数），但**不推荐手动 CAST 产生 TIME
类型**。
- TIME 类型不会在常量折叠中进行计算。
+如何将其他类型转换为 TIME，及转换时接受的输入，请见 [转换为 TIME](../conversion/time-conversion.md)。
+
+日期时间类型均不支持直接使用数学运算符进行四则运算，执行数学运算的实质是首先将日期时间类型隐式转换为数字类型，再行运算。
## 举例
```sql
-mysql> select timediff('2020-01-01 12:05:03', '2020-01-01 08:02:15');
-+------------------------------------------------------------------------------------------------------+
-| timediff(cast('2020-01-01 12:05:03' as DATETIMEV2(0)), cast('2020-01-01
08:02:15' as DATETIMEV2(0))) |
-+------------------------------------------------------------------------------------------------------+
-| 04:02:48
|
-+------------------------------------------------------------------------------------------------------+
-1 row in set (0.12 sec)
-
-mysql> select timediff('2020-01-01', '2000-01-01');
-+------------------------------------------------------------------------------------+
-| timediff(cast('2020-01-01' as DATETIMEV2(0)), cast('2000-01-01' as
DATETIMEV2(0))) |
-+------------------------------------------------------------------------------------+
-| 838:59:59
|
-+------------------------------------------------------------------------------------+
-1 row in set (0.11 sec)
+select cast('-123:00:02.9' as time);
+```
+
+```text
++------------------------------+
+| cast('-123:00:02.9' as time) |
++------------------------------+
+| -123:00:03 |
++------------------------------+
```
-### keywords
+```sql
+select cast('838:59:59.999999' as time(6));
+```
- TIME
+```text
++-------------------------------------+
+| cast('838:59:59.999999' as time(6)) |
++-------------------------------------+
+| 838:59:59.999999 |
++-------------------------------------+
+```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
