This is an automated email from the ASF dual-hosted git repository.
kurt pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/master by this push:
new 615d5d1 [FLINK-21624][table-planner] Correct FLOOR/CEIL
(TIMESTAMP/DATE TO WEEK) functions
615d5d1 is described below
commit 615d5d150abdeab89d15f6830177eac238bdc2fc
Author: Leonard Xu <xbjt...@163.com>
AuthorDate: Fri Mar 12 12:19:05 2021 +0800
[FLINK-21624][table-planner] Correct FLOOR/CEIL (TIMESTAMP/DATE TO WEEK)
functions
This closes #15125
---
.../table/planner/codegen/calls/FloorCeilCallGen.scala | 4 ++--
.../flink/table/planner/expressions/TemporalTypesTest.scala | 13 +++++++++++++
.../flink/table/runtime/functions/SqlDateTimeUtils.java | 9 +++++++++
3 files changed, 24 insertions(+), 2 deletions(-)
diff --git
a/flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
b/flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
index 35bc9e2..cd3d7b9 100644
---
a/flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
+++
b/flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
@@ -66,7 +66,7 @@ class FloorCeilCallGen(
terms =>
unit match {
// for Timestamp with timezone info
- case YEAR | QUARTER | MONTH | DAY | HOUR
+ case YEAR | QUARTER | MONTH | WEEK | DAY | HOUR
if terms.length + 1 == method.getParameterCount &&
method.getParameterTypes()(terms.length) == classOf[TimeZone]
=>
val timeZone = ctx.addReusableSessionTimeZone()
@@ -79,7 +79,7 @@ class FloorCeilCallGen(
|""".stripMargin
// for Unix Date / Unix Time
- case YEAR | MONTH =>
+ case YEAR | MONTH | WEEK =>
operand.resultType.getTypeRoot match {
case LogicalTypeRoot.TIMESTAMP_WITHOUT_TIME_ZONE =>
val longTerm = s"${terms.head}.getMillisecond()"
diff --git
a/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
b/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
index f09aee1..0222302 100644
---
a/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
+++
b/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
@@ -740,9 +740,16 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi("CEIL(TIME '12:44:31' TO MINUTE)", "12:45:00")
testSqlApi("CEIL(TIME '12:44:31' TO HOUR)", "13:00:00")
+ testSqlApi("FLOOR( DATE '2021-02-27' TO WEEK)", "2021-02-21")
+ testSqlApi("FLOOR( DATE '2021-03-01' TO WEEK)", "2021-02-28")
+ testSqlApi("CEIL( DATE '2021-02-27' TO WEEK)", "2021-02-28")
+ testSqlApi("CEIL( DATE '2021-03-01' TO WEEK)", "2021-03-07")
+
testSqlApi("FLOOR(TIMESTAMP '2018-03-20 06:44:31' TO HOUR)", "2018-03-20
06:00:00")
testSqlApi("FLOOR(TIMESTAMP '2018-03-20 06:44:31' TO DAY)", "2018-03-20
00:00:00")
testSqlApi("FLOOR(TIMESTAMP '2018-03-20 00:00:00' TO DAY)", "2018-03-20
00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2021-02-27 00:00:00' TO WEEK)", "2021-02-21
00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2021-03-01 00:00:00' TO WEEK)", "2021-02-28
00:00:00")
testSqlApi("FLOOR(TIMESTAMP '2018-04-01 06:44:31' TO MONTH)", "2018-04-01
00:00:00")
testSqlApi("FLOOR(TIMESTAMP '2018-01-01 06:44:31' TO MONTH)", "2018-01-01
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-20 06:44:31' TO HOUR)", "2018-03-20
07:00:00")
@@ -750,6 +757,8 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi("CEIL(TIMESTAMP '2018-03-20 06:44:31' TO DAY)", "2018-03-21
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-01 00:00:00' TO DAY)", "2018-03-01
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-31 00:00:01' TO DAY)", "2018-04-01
00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2021-02-27 00:00:00' TO WEEK)", "2021-02-28
00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2021-03-01 00:00:00' TO WEEK)", "2021-03-07
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-01 21:00:01' TO MONTH)", "2018-03-01
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-01 00:00:00' TO MONTH)", "2018-03-01
00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-12-02 00:00:00' TO MONTH)", "2019-01-01
00:00:00")
@@ -759,6 +768,8 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(s"FLOOR(${timestampTz("2018-03-20 06:44:31")} TO HOUR)",
"2018-03-20 06:00:00")
testSqlApi(s"FLOOR(${timestampTz("2018-03-20 06:44:31")} TO DAY)",
"2018-03-20 00:00:00")
testSqlApi(s"FLOOR(${timestampTz("2018-03-20 00:00:00")} TO DAY)",
"2018-03-20 00:00:00")
+ testSqlApi(s"FLOOR(${timestampTz("2021-02-27 00:00:00")} TO WEEK)",
"2021-02-21 00:00:00")
+ testSqlApi(s"FLOOR(${timestampTz("2021-03-01 00:00:00")} TO WEEK)",
"2021-02-28 00:00:00")
testSqlApi(s"FLOOR(${timestampTz("2018-04-01 06:44:31")} TO MONTH)",
"2018-04-01 00:00:00")
testSqlApi(s"FLOOR(${timestampTz("2018-01-01 06:44:31")} TO MONTH)",
"2018-01-01 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-03-20 06:44:31")} TO HOUR)",
"2018-03-20 07:00:00")
@@ -766,6 +777,8 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(s"CEIL(${timestampTz("2018-03-20 06:44:31")} TO DAY)",
"2018-03-21 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-03-1 00:00:00")} TO DAY)",
"2018-03-01 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-03-31 00:00:01")} TO DAY)",
"2018-04-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampTz("2021-02-27 00:00:00")} TO WEEK)",
"2021-02-28 00:00:00")
+ testSqlApi(s"CEIL(${timestampTz("2021-03-01 00:00:00")} TO WEEK)",
"2021-03-07 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-03-01 21:00:01")} TO MONTH)",
"2018-03-01 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-03-01 00:00:00")} TO MONTH)",
"2018-03-01 00:00:00")
testSqlApi(s"CEIL(${timestampTz("2018-12-02 00:00:00")} TO MONTH)",
"2019-01-01 00:00:00")
diff --git
a/flink-table/flink-table-runtime-blink/src/main/java/org/apache/flink/table/runtime/functions/SqlDateTimeUtils.java
b/flink-table/flink-table-runtime-blink/src/main/java/org/apache/flink/table/runtime/functions/SqlDateTimeUtils.java
index 9f88fa0..dc7c7d3 100644
---
a/flink-table/flink-table-runtime-blink/src/main/java/org/apache/flink/table/runtime/functions/SqlDateTimeUtils.java
+++
b/flink-table/flink-table-runtime-blink/src/main/java/org/apache/flink/table/runtime/functions/SqlDateTimeUtils.java
@@ -764,6 +764,7 @@ public class SqlDateTimeUtils {
case MONTH:
case YEAR:
case QUARTER:
+ case WEEK:
int days = (int) (utcTs / MILLIS_PER_DAY + EPOCH_JULIAN);
return julianDateFloor(range, days, true) * MILLIS_PER_DAY -
offset;
default:
@@ -794,6 +795,7 @@ public class SqlDateTimeUtils {
case MONTH:
case YEAR:
case QUARTER:
+ case WEEK:
int days = (int) (utcTs / MILLIS_PER_DAY + EPOCH_JULIAN);
return julianDateFloor(range, days, false) * MILLIS_PER_DAY -
offset;
default:
@@ -856,6 +858,13 @@ public class SqlDateTimeUtils {
quarter += 1;
}
return DateTimeUtils.ymdToUnixDate(year, quarter * 3 - 2, 1);
+ case WEEK:
+ int dow = (int) DateTimeUtils.floorMod(julian + 1, 7); //
sun=0, sat=6
+ int offset = dow;
+ if (!floor && offset > 0) {
+ offset -= 7;
+ }
+ return DateTimeUtils.ymdToUnixDate(year, month, day) - offset;
default:
throw new AssertionError(range);
}
