You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by li...@apache.org on 2024/01/11 05:59:48 UTC
(doris) branch master updated: [enhance](function) support two special format for str_to_date (#29823)
This is an automated email from the ASF dual-hosted git repository.
lihaopeng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new cfd68d8f37a [enhance](function) support two special format for str_to_date (#29823)
cfd68d8f37a is described below
commit cfd68d8f37af3722583bf4815358db644a17d229
Author: zclllyybb <zh...@selectdb.com>
AuthorDate: Thu Jan 11 13:59:41 2024 +0800
[enhance](function) support two special format for str_to_date (#29823)
---
be/src/vec/runtime/vdatetime_value.cpp | 30 +++++++++++++++++++---
.../date-time-functions/str-to-date.md | 10 ++++++--
.../date-time-functions/str-to-date.md | 10 ++++++--
.../org/apache/doris/analysis/DateLiteral.java | 8 ++++++
.../data/correctness/test_str_to_date.out | 18 +++++++++++++
.../suites/correctness/test_str_to_date.groovy | 21 ++++++---------
6 files changed, 77 insertions(+), 20 deletions(-)
diff --git a/be/src/vec/runtime/vdatetime_value.cpp b/be/src/vec/runtime/vdatetime_value.cpp
index 7755cbea7a5..58c6c25f9ba 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -30,6 +30,7 @@
#include <chrono> // IWYU pragma: keep
// IWYU pragma: no_include <bits/std_abs.h>
#include <cmath>
+#include <cstring>
#include <exception>
#include <string>
#include <string_view>
@@ -1588,11 +1589,24 @@ bool VecDateTimeValue::from_date_format_str(const char* format, int format_len,
// so we only need to set date part
// 3. if both are true, means all part of date_time be set, no need check_range_and_set_time
bool already_set_date_part = yearday > 0 || (week_num >= 0 && weekday > 0);
- if (already_set_date_part && already_set_time_part) return true;
- if (already_set_date_part)
+ if (already_set_date_part && already_set_time_part) {
+ return true;
+ }
+ // for two special date cases, complete default month/day
+ if (!time_part_used && year > 0) {
+ if (std::string_view {format, end} == "%Y") {
+ month = day = 1;
+ } else if (std::string_view {format, end} == "%Y-%m") {
+ day = 1;
+ }
+ }
+
+ if (already_set_date_part) {
return check_range_and_set_time(_year, _month, _day, hour, minute, second, _type);
- if (already_set_time_part)
+ }
+ if (already_set_time_part) {
return check_range_and_set_time(year, month, day, _hour, _minute, _second, _type);
+ }
return check_range_and_set_time(year, month, day, hour, minute, second, _type);
}
@@ -2578,6 +2592,16 @@ bool DateV2Value<T>::from_date_format_str(const char* format, int format_len, co
date_v2_value_.day_, 0, 0, 0, 0);
}
}
+
+ // for two special date cases, complete default month/day
+ if (!time_part_used && year > 0) {
+ if (std::string_view {format, end} == "%Y") {
+ month = day = 1;
+ } else if (std::string_view {format, end} == "%Y-%m") {
+ day = 1;
+ }
+ }
+
if (already_set_time_part) {
if constexpr (is_datetime) {
return check_range_and_set_time(year, month, day, date_v2_value_.hour_,
diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
index 1e2a7942cd0..4b46dc25543 100644
--- a/docs/en/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
@@ -33,7 +33,7 @@ under the License.
Convert STR to DATE type by format specified, if the conversion result does not return NULL. Note that the 'format' parameter specifies the format of the first parameter.
-The `format` supported is consistent with [date_format](date_format.md)
+All formats in [date_format](./date-format) are supported. In addition, support auto completing the remainder of date part for '%Y' and '%Y-%m'.
### example
@@ -65,7 +65,13 @@ mysql> select str_to_date("2020-09-01", "%Y-%m-%d %H:%i:%s");
+------------------------------------------------+
| 2020-09-01 00:00:00 |
+------------------------------------------------+
-1 row in set (0.01 sec)
+
+mysql> select str_to_date('2023','%Y');
++---------------------------+
+| str_to_date('2023', '%Y') |
++---------------------------+
+| 2023-01-01 |
++---------------------------+
```
### keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
index fc15fc31a68..3c219a29fa9 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/str-to-date.md
@@ -32,7 +32,7 @@ under the License.
通过format指定的方式将str转化为DATE类型，如果转化结果不对返回NULL。注意format指定的是第一个参数的格式。
-支持的format格式与[date_format](date_format.md)一致
+支持[date_format](./date-format)中的所有 format 格式，此外对于 '%Y' 和 '%Y-%m'，支持补齐日期剩余部分。
### example
@@ -64,7 +64,13 @@ mysql> select str_to_date("2020-09-01", "%Y-%m-%d %H:%i:%s");
+------------------------------------------------+
| 2020-09-01 00:00:00 |
+------------------------------------------------+
-1 row in set (0.01 sec)
+
+mysql> select str_to_date('2023','%Y');
++---------------------------+
+| str_to_date('2023', '%Y') |
++---------------------------+
+| 2023-01-01 |
++---------------------------+
```
### keywords
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
index be72f5b0cff..8b0921f9b1a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
@@ -1536,6 +1536,14 @@ public class DateLiteral extends LiteralExpr {
getDateFromDaynr(days);
}
+ if (!timePartUsed && year > 0) {
+ if (format.equals("%Y")) {
+ month = day = 1;
+ } else if (format.equals("%Y-%m")) {
+ day = 1;
+ }
+ }
+
// Compute timestamp type
// TODO(Gabriel): we still use old version datetime/date and change this to new version when
// we think it's stable enough
diff --git a/regression-test/data/correctness/test_str_to_date.out b/regression-test/data/correctness/test_str_to_date.out
index af6342ecf66..5aa1f75a2cc 100644
--- a/regression-test/data/correctness/test_str_to_date.out
+++ b/regression-test/data/correctness/test_str_to_date.out
@@ -13,6 +13,15 @@
-- !select4 --
2020-12-03T11:45:14
+-- !add_1 --
+2023-01-01
+
+-- !add_2 --
+2023-12-01
+
+-- !add_3 --
+2023-01-01
+
-- !select5 --
2019-12-01 yyyy-MM-dd 2019-12-01T00:00
20201203 yyyyMMdd 2020-12-03T00:00
@@ -27,3 +36,12 @@
-- !select8 --
2020-12-03T11:45:14
+-- !add_4 --
+2023-01-01
+
+-- !add_5 --
+2023-12-01
+
+-- !add_6 --
+2023-01-01
+
diff --git a/regression-test/suites/correctness/test_str_to_date.groovy b/regression-test/suites/correctness/test_str_to_date.groovy
index 6c26a8db24b..43c80d4f4ba 100644
--- a/regression-test/suites/correctness/test_str_to_date.groovy
+++ b/regression-test/suites/correctness/test_str_to_date.groovy
@@ -36,43 +36,38 @@ suite("test_str_to_date") {
sql """ INSERT INTO test_str_to_date_db VALUES(2,'20201203', 'yyyyMMdd');"""
sql """ INSERT INTO test_str_to_date_db VALUES(3,'2020-12-03 11:45:14', 'yyyy-MM-dd HH:mm:ss');"""
- sql """ set enable_nereids_planner=true , enable_fallback_to_original_planner=false;"""
-
-
+sql """ set enable_nereids_planner=true , enable_fallback_to_original_planner=false;"""
qt_select1 """
select s1,s2,STR_TO_DATE(s1,s2) from test_str_to_date_db order by id;
"""
-
qt_select2 """
SELECT STR_TO_DATE('2019-12-01', 'yyyy-MM-dd');
"""
-
qt_select3 """
SELECT STR_TO_DATE('20201203', 'yyyyMMdd');
"""
-
qt_select4 """
SELECT STR_TO_DATE('2020-12-03 11:45:14', 'yyyy-MM-dd HH:mm:ss');
"""
+ qt_add_1 " select STR_TO_DATE('2023', '%Y') "
+ qt_add_2 " select STR_TO_DATE('2023-12', '%Y-%m') "
+ qt_add_3 " select STR_TO_DATE('2023-12', '%Y')"
- sql """ set enable_nereids_planner=false;"""
-
+sql """ set enable_nereids_planner=false;"""
qt_select5 """
select s1,s2,STR_TO_DATE(s1,s2) from test_str_to_date_db order by id;
"""
-
qt_select6 """
SELECT STR_TO_DATE('2019-12-01', 'yyyy-MM-dd');
"""
-
qt_select7 """
SELECT STR_TO_DATE('20201203', 'yyyyMMdd');
"""
-
qt_select8 """
SELECT STR_TO_DATE('2020-12-03 11:45:14', 'yyyy-MM-dd HH:mm:ss');
"""
-
-
+ qt_add_4 " select STR_TO_DATE('2023', '%Y') "
+ qt_add_5 " select STR_TO_DATE('2023-12', '%Y-%m') "
+ qt_add_6 " select STR_TO_DATE('2023-12', '%Y')"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org