You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by lz...@apache.org on 2021/11/03 07:55:41 UTC
[flink] branch master updated: [FLINK-24648][table] CEIL and FLOOR
support DECADE, CENTURY, MILLENNIUM
This is an automated email from the ASF dual-hosted git repository.
lzljs3620320 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 ac12dc2 [FLINK-24648][table] CEIL and FLOOR support DECADE, CENTURY, MILLENNIUM
ac12dc2 is described below
commit ac12dc2b02aaaf6cdd1354d72a17391fb137ae0a
Author: Sergey Nuyanzin <sn...@gmail.com>
AuthorDate: Wed Nov 3 08:55:15 2021 +0100
[FLINK-24648][table] CEIL and FLOOR support DECADE, CENTURY, MILLENNIUM
This closes #17569
---
.../apache/flink/table/utils/DateTimeUtils.java | 18 +++++++++++++
.../planner/codegen/calls/FloorCeilCallGen.scala | 4 +--
.../planner/expressions/TemporalTypesTest.scala | 30 +++++++++++++++++++++-
3 files changed, 49 insertions(+), 3 deletions(-)
diff --git a/flink-table/flink-table-common/src/main/java/org/apache/flink/table/utils/DateTimeUtils.java b/flink-table/flink-table-common/src/main/java/org/apache/flink/table/utils/DateTimeUtils.java
index 09c393b..fdd715b 100644
--- a/flink-table/flink-table-common/src/main/java/org/apache/flink/table/utils/DateTimeUtils.java
+++ b/flink-table/flink-table-common/src/main/java/org/apache/flink/table/utils/DateTimeUtils.java
@@ -1123,6 +1123,9 @@ public class DateTimeUtils {
return floor(utcTs, MILLIS_PER_HOUR) - offset;
case DAY:
return floor(utcTs, MILLIS_PER_DAY) - offset;
+ case MILLENNIUM:
+ case CENTURY:
+ case DECADE:
case MONTH:
case YEAR:
case QUARTER:
@@ -1154,6 +1157,9 @@ public class DateTimeUtils {
return ceil(utcTs, MILLIS_PER_HOUR) - offset;
case DAY:
return ceil(utcTs, MILLIS_PER_DAY) - offset;
+ case MILLENNIUM:
+ case CENTURY:
+ case DECADE:
case MONTH:
case YEAR:
case QUARTER:
@@ -1205,6 +1211,18 @@ public class DateTimeUtils {
int quarter = (month + 2) / 3;
int year = b * 100 + d - 4800 + (m / 10);
switch (range) {
+ case MILLENNIUM:
+ return floor
+ ? ymdToUnixDate(1000 * ((year + 999) / 1000) - 999, 1, 1)
+ : ymdToUnixDate(1000 * ((year + 999) / 1000) + 1, 1, 1);
+ case CENTURY:
+ return floor
+ ? ymdToUnixDate(100 * ((year + 99) / 100) - 99, 1, 1)
+ : ymdToUnixDate(100 * ((year + 99) / 100) + 1, 1, 1);
+ case DECADE:
+ return floor
+ ? ymdToUnixDate(10 * (year / 10), 1, 1)
+ : ymdToUnixDate(10 * (1 + year / 10), 1, 1);
case YEAR:
if (!floor && (month > 1 || day > 1)) {
year += 1;
diff --git a/flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala b/flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
index 7ba55c5..e9e9df8 100644
--- a/flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/codegen/calls/FloorCeilCallGen.scala
+++ b/flink-table/flink-table-planner/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 | WEEK | DAY | HOUR
+ case MILLENNIUM | CENTURY | DECADE | 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 | WEEK =>
+ case MILLENNIUM | CENTURY | DECADE | YEAR | QUARTER | 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/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala b/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
index 70bd5ca..d9ae2d5 100644
--- a/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
+++ b/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/TemporalTypesTest.scala
@@ -890,7 +890,12 @@ class TemporalTypesTest extends ExpressionTestBase {
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("CEIL(DATE '2018-01-02' TO DECADE)", "2020-01-01")
+ testSqlApi("CEIL(DATE '2018-03-27' TO CENTURY)", "2101-01-01")
+ testSqlApi("CEIL(DATE '2018-01-02' TO MILLENNIUM)", "3001-01-01")
+ testSqlApi("FLOOR(DATE '2018-07-02' TO DECADE)", "2010-01-01")
+ testSqlApi("FLOOR(DATE '2018-04-02' TO CENTURY)", "2001-01-01")
+ testSqlApi("FLOOR(DATE '2018-01-09' TO MILLENNIUM)", "2001-01-01")
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")
@@ -898,6 +903,11 @@ class TemporalTypesTest extends ExpressionTestBase {
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("FLOOR(TIMESTAMP '2021-03-21 00:00:00' TO QUARTER)", "2021-01-01 00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2018-01-02 21:00:01' TO DECADE)", "2010-01-01 00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2018-01-01 00:00:00' TO DECADE)", "2010-01-01 00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2018-01-02 21:00:01' TO CENTURY)", "2001-01-01 00:00:00")
+ testSqlApi("FLOOR(TIMESTAMP '2018-01-02 21:00:01' TO MILLENNIUM)", "2001-01-01 00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-20 06:44:31' TO HOUR)", "2018-03-20 07:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-20 06:00:00' TO HOUR)", "2018-03-20 06:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-03-20 06:44:31' TO DAY)", "2018-03-21 00:00:00")
@@ -910,6 +920,13 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi("CEIL(TIMESTAMP '2018-12-02 00:00:00' TO MONTH)", "2019-01-01 00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-01-01 21:00:01' TO YEAR)", "2018-01-01 00:00:00")
testSqlApi("CEIL(TIMESTAMP '2018-01-02 21:00:01' TO YEAR)", "2019-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2021-03-21 00:00:00' TO QUARTER)", "2021-04-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2018-01-02 21:00:01' TO DECADE)", "2020-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '1999-01-01 00:00:00' TO DECADE)", "2000-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2018-01-02 21:00:01' TO CENTURY)", "2101-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '1999-01-01 00:00:00' TO CENTURY)", "2001-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '2018-01-02 21:00:01' TO MILLENNIUM)", "3001-01-01 00:00:00")
+ testSqlApi("CEIL(TIMESTAMP '1999-01-01 00:00:00' TO MILLENNIUM)", "2001-01-01 00:00:00")
testSqlApi(s"FLOOR(${timestampLtz("2018-03-20 06:44:31")} TO HOUR)", "2018-03-20 06:00:00")
testSqlApi(s"FLOOR(${timestampLtz("2018-03-20 06:44:31")} TO DAY)", "2018-03-20 00:00:00")
@@ -918,6 +935,12 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(s"FLOOR(${timestampLtz("2021-03-01 00:00:00")} TO WEEK)", "2021-02-28 00:00:00")
testSqlApi(s"FLOOR(${timestampLtz("2018-04-01 06:44:31")} TO MONTH)", "2018-04-01 00:00:00")
testSqlApi(s"FLOOR(${timestampLtz("2018-01-01 06:44:31")} TO MONTH)", "2018-01-01 00:00:00")
+ testSqlApi(s"FLOOR(${timestampLtz("2018-01-02 21:00:01")} TO QUARTER)", "2018-01-01 00:00:00")
+ testSqlApi(s"FLOOR(${timestampLtz("2018-05-02 21:00:01")} TO QUARTER)", "2018-04-01 00:00:00")
+ testSqlApi(s"FLOOR(${timestampLtz("2018-01-02 21:00:01")} TO DECADE)", "2010-01-01 00:00:00")
+ testSqlApi(s"FLOOR(${timestampLtz("2018-01-02 21:00:01")} TO CENTURY)", "2001-01-01 00:00:00")
+ testSqlApi(
+ s"FLOOR(${timestampLtz("2018-01-02 21:00:01")} TO MILLENNIUM)", "2001-01-01 00:00:00")
testSqlApi(s"CEIL(${timestampLtz("2018-03-20 06:44:31")} TO HOUR)", "2018-03-20 07:00:00")
testSqlApi(s"CEIL(${timestampLtz("2018-03-20 06:00:00")} TO HOUR)", "2018-03-20 06:00:00")
testSqlApi(s"CEIL(${timestampLtz("2018-03-20 06:44:31")} TO DAY)", "2018-03-21 00:00:00")
@@ -930,6 +953,11 @@ class TemporalTypesTest extends ExpressionTestBase {
testSqlApi(s"CEIL(${timestampLtz("2018-12-02 00:00:00")} TO MONTH)", "2019-01-01 00:00:00")
testSqlApi(s"CEIL(${timestampLtz("2018-01-01 21:00:01")} TO YEAR)", "2018-01-01 00:00:00")
testSqlApi(s"CEIL(${timestampLtz("2018-01-02 21:00:01")} TO YEAR)", "2019-01-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampLtz("2018-01-02 21:00:01")} TO QUARTER)", "2018-04-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampLtz("2018-04-02 21:00:01")} TO QUARTER)", "2018-07-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampLtz("2018-01-02 21:00:01")} TO DECADE)", "2020-01-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampLtz("2018-01-02 21:00:01")} TO CENTURY)", "2101-01-01 00:00:00")
+ testSqlApi(s"CEIL(${timestampLtz("2018-01-02 21:00:01")} TO MILLENNIUM)", "3001-01-01 00:00:00")
// others
testSqlApi("QUARTER(DATE '2016-04-12')", "2")