You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2022/01/16 02:39:40 UTC
[incubator-doris] branch master updated: [feature](mysql-compatibility)(function) add `WEEKDAY` function (#7673)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 5b0f11b [feature](mysql-compatibility)(function) add `WEEKDAY` function (#7673)
5b0f11b is described below
commit 5b0f11b665b1e15b6bb6c6bf2502fa1b7d5ec915
Author: Universe <23...@qq.com>
AuthorDate: Sun Jan 16 10:39:21 2022 +0800
[feature](mysql-compatibility)(function) add `WEEKDAY` function (#7673)
`WEEKDAY` in MySQL: returns an index from 0 to 6 for Monday to Sunday.
`DAYOFWEEK` in MySQL: returns an index from 1 to 7 for Sunday to Saturday.
Doris only have `DAYOFWEEK` function, so I add `WEEKDAY` function.
Thanks for the following materials:
- https://github.com/apache/incubator-doris/pull/6982/files
- https://www.bilibili.com/video/BV1V44y1Y7Ro
---
be/src/exprs/timestamp_functions.cpp | 11 ++++
be/src/exprs/timestamp_functions.h | 2 +
be/test/exprs/timestamp_functions_test.cpp | 27 +++++++++
docs/.vuepress/sidebar/en.js | 1 +
docs/.vuepress/sidebar/zh-CN.js | 1 +
.../sql-functions/date-time-functions/dayofweek.md | 2 +-
.../sql-functions/date-time-functions/weekday.md | 66 +++++++++++++++++++++
.../sql-functions/date-time-functions/dayofweek.md | 2 +-
.../sql-functions/date-time-functions/weekday.md | 68 ++++++++++++++++++++++
gensrc/script/doris_builtins_functions.py | 3 +
10 files changed, 181 insertions(+), 2 deletions(-)
diff --git a/be/src/exprs/timestamp_functions.cpp b/be/src/exprs/timestamp_functions.cpp
index 34eeecf..44bc34b 100644
--- a/be/src/exprs/timestamp_functions.cpp
+++ b/be/src/exprs/timestamp_functions.cpp
@@ -152,6 +152,17 @@ IntVal TimestampFunctions::day_of_week(FunctionContext* context, const DateTimeV
return IntVal::null();
}
+IntVal TimestampFunctions::week_day(FunctionContext* context, const DateTimeVal& ts_val) {
+ if (ts_val.is_null) {
+ return IntVal::null();
+ }
+ const DateTimeValue& ts_value = DateTimeValue::from_datetime_val(ts_val);
+ if (ts_value.is_valid_date()) {
+ return IntVal(ts_value.weekday());
+ }
+ return IntVal::null();
+}
+
IntVal TimestampFunctions::day_of_month(FunctionContext* context, const DateTimeVal& ts_val) {
if (ts_val.is_null) {
return IntVal::null();
diff --git a/be/src/exprs/timestamp_functions.h b/be/src/exprs/timestamp_functions.h
index ff1112b..e558c80 100644
--- a/be/src/exprs/timestamp_functions.h
+++ b/be/src/exprs/timestamp_functions.h
@@ -59,6 +59,8 @@ public:
const doris_udf::DateTimeVal& ts_val);
static doris_udf::IntVal day_of_week(doris_udf::FunctionContext* context,
const doris_udf::DateTimeVal& ts_val);
+ static doris_udf::IntVal week_day(doris_udf::FunctionContext* context,
+ const doris_udf::DateTimeVal& ts_val);
static doris_udf::IntVal day_of_month(doris_udf::FunctionContext* context,
const doris_udf::DateTimeVal& ts_val);
static doris_udf::IntVal day_of_year(doris_udf::FunctionContext* context,
diff --git a/be/test/exprs/timestamp_functions_test.cpp b/be/test/exprs/timestamp_functions_test.cpp
index 766365a..4662727 100644
--- a/be/test/exprs/timestamp_functions_test.cpp
+++ b/be/test/exprs/timestamp_functions_test.cpp
@@ -88,6 +88,33 @@ TEST_F(TimestampFunctionsTest, day_of_week_test) {
delete context;
}
+TEST_F(TimestampFunctionsTest, week_day_test) {
+ doris_udf::FunctionContext* context = new doris_udf::FunctionContext();
+
+ doris_udf::DateTimeVal tv;
+ // 2001-02-03 12:34:56
+ tv.packed_time = 1830650338932162560L;
+ tv.type = TIME_DATETIME;
+
+ ASSERT_EQ(5, TimestampFunctions::week_day(context, tv).val);
+
+ // 2020-00-01 00:00:00
+ DateTimeValue dtv2(20200001000000);
+ dtv2.set_type(TIME_DATETIME);
+ doris_udf::DateTimeVal tv2;
+ dtv2.to_datetime_val(&tv2);
+ ASSERT_EQ(true, TimestampFunctions::week_day(context, tv2).is_null);
+
+ // 2020-01-00 00:00:00
+ DateTimeValue dtv3(20200100000000);
+ dtv3.set_type(TIME_DATETIME);
+ doris_udf::DateTimeVal tv3;
+ dtv3.to_datetime_val(&tv3);
+ ASSERT_EQ(true, TimestampFunctions::week_day(context, tv3).is_null);
+
+ delete context;
+}
+
TEST_F(TimestampFunctionsTest, day_of_month_test) {
doris_udf::FunctionContext* context = new doris_udf::FunctionContext();
diff --git a/docs/.vuepress/sidebar/en.js b/docs/.vuepress/sidebar/en.js
index 67fb94b..cc33eac 100644
--- a/docs/.vuepress/sidebar/en.js
+++ b/docs/.vuepress/sidebar/en.js
@@ -300,6 +300,7 @@ module.exports = [
"unix_timestamp",
"utc_timestamp",
"week",
+ "weekday",
"weekofyear",
"year",
"yearweek",
diff --git a/docs/.vuepress/sidebar/zh-CN.js b/docs/.vuepress/sidebar/zh-CN.js
index cad1f6b..a701c74 100644
--- a/docs/.vuepress/sidebar/zh-CN.js
+++ b/docs/.vuepress/sidebar/zh-CN.js
@@ -304,6 +304,7 @@ module.exports = [
"unix_timestamp",
"utc_timestamp",
"week",
+ "weekday",
"weekofyear",
"year",
"yearweek",
diff --git a/docs/en/sql-reference/sql-functions/date-time-functions/dayofweek.md b/docs/en/sql-reference/sql-functions/date-time-functions/dayofweek.md
index 9a0bfe7..331f8c3 100644
--- a/docs/en/sql-reference/sql-functions/date-time-functions/dayofweek.md
+++ b/docs/en/sql-reference/sql-functions/date-time-functions/dayofweek.md
@@ -28,7 +28,7 @@ under the License.
## Description
### Syntax
-`INT DayOfWeek (DATETIME date)`
+`INT DAYOFWEEK (DATETIME date)`
The DAYOFWEEK function returns the index value of the working day of the date, that is, 1 on Sunday, 2 on Monday, and 7 on Saturday.
diff --git a/docs/en/sql-reference/sql-functions/date-time-functions/weekday.md b/docs/en/sql-reference/sql-functions/date-time-functions/weekday.md
new file mode 100644
index 0000000..0626600
--- /dev/null
+++ b/docs/en/sql-reference/sql-functions/date-time-functions/weekday.md
@@ -0,0 +1,66 @@
+---
+{
+ "title": "weekday",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# weekday
+## Description
+### Syntax
+
+`INT WEEKDAY (DATETIME date)`
+
+
+The WEEKDAY function returns the index value of the working day of the date, that is, 0 on Monday, 1 on Tuesday, and 6 on Sunday.
+
+The parameter is Date or Datetime type
+
+Notice the difference between WEEKDAY and DAYOFWEEK:
+```
+ +-----+-----+-----+-----+-----+-----+-----+
+ | Sun | Mon | Tues| Wed | Thur| Fri | Sat |
+ +-----+-----+-----+-----+-----+-----+-----+
+ weekday | 6 | 0 | 1 | 2 | 3 | 4 | 5 |
+ +-----+-----+-----+-----+-----+-----+-----+
+dayofweek | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
+ +-----+-----+-----+-----+-----+-----+-----+
+```
+
+## example
+```
+mysql> select weekday('2019-06-25');
++--------------------------------+
+| weekday('2019-06-25 00:00:00') |
++--------------------------------+
+| 1 |
++--------------------------------+
+
+mysql> select weekday(cast(20190625 as date));
++---------------------------------+
+| weekday(CAST(20190625 AS DATE)) |
++---------------------------------+
+| 1 |
++---------------------------------+
+```
+## keyword
+WEEKDAY
\ No newline at end of file
diff --git a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/dayofweek.md b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/dayofweek.md
index 03f326a..3cddb13 100644
--- a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/dayofweek.md
+++ b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/dayofweek.md
@@ -28,7 +28,7 @@ under the License.
## description
### Syntax
-`INT dayofweek(DATETIME date)`
+`INT DAYOFWEEK(DATETIME date)`
DAYOFWEEK函数返回日期的工作日索引值，即星期日为1，星期一为2，星期六为7
diff --git a/docs/zh-CN/sql-reference/sql-functions/date-time-functions/weekday.md b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/weekday.md
new file mode 100644
index 0000000..e4ec178
--- /dev/null
+++ b/docs/zh-CN/sql-reference/sql-functions/date-time-functions/weekday.md
@@ -0,0 +1,68 @@
+---
+{
+ "title": "weekday",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# weekday
+## Description
+### Syntax
+
+`INT WEEKDAY (DATETIME date)`
+
+
+WEEKDAY函数返回日期的工作日索引值，即星期一为0，星期二为1，星期日为6
+
+参数为Date或者Datetime类型或者可以cast为Date或者Datetime类型的数字
+
+注意WEEKDAY和DAYOFWEEK的区别：
+```
+ +-----+-----+-----+-----+-----+-----+-----+
+ | Sun | Mon | Tues| Wed | Thur| Fri | Sat |
+ +-----+-----+-----+-----+-----+-----+-----+
+ weekday | 6 | 0 | 1 | 2 | 3 | 4 | 5 |
+ +-----+-----+-----+-----+-----+-----+-----+
+dayofweek | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
+ +-----+-----+-----+-----+-----+-----+-----+
+```
+
+## example
+
+```
+mysql> select weekday('2019-06-25');
++--------------------------------+
+| weekday('2019-06-25 00:00:00') |
++--------------------------------+
+| 1 |
++--------------------------------+
+
+mysql> select weekday(cast(20190625 as date));
++---------------------------------+
+| weekday(CAST(20190625 AS DATE)) |
++---------------------------------+
+| 1 |
++---------------------------------+
+```
+
+## keyword
+WEEKDAY
\ No newline at end of file
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index 8202793..f9d3fbe 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -189,6 +189,9 @@ visible_functions = [
[['dayofweek'], 'INT', ['DATETIME'],
'_ZN5doris18TimestampFunctions11day_of_weekEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
'', '', 'vec', ''],
+ [['weekday'], 'INT', ['DATETIME'],
+ '_ZN5doris18TimestampFunctions8week_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
+ '', '', 'vec', ''],
[['day', 'dayofmonth'], 'INT', ['DATETIME'],
'_ZN5doris18TimestampFunctions12day_of_monthEPN9doris_udf'
'15FunctionContextERKNS1_11DateTimeValE', '', '', 'vec', ''],
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org