This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new de28b7ef466 date_bin scalar function
de28b7ef466 is described below
commit de28b7ef46628fb5c050b48a9df39516fa7cc7fe
Author: FearfulTomcat27 <[email protected]>
AuthorDate: Fri Aug 23 20:43:36 2024 +0800
date_bin scalar function
---
.../scalar/IoTDBScalarFunctionTableIT.java | 54 +++++++
.../relational/ColumnTransformerBuilder.java | 12 ++
.../relational/metadata/TableMetadataImpl.java | 18 ++-
.../plan/relational/sql/parser/AstBuilder.java | 30 ++++
.../scalar/DateBinFunctionColumnTransformer.java | 158 +++++++++++++++++++++
.../unary/scalar/TableBuiltinScalarFunction.java | 1 +
.../org/apache/iotdb/db/utils/DateTimeUtils.java | 2 +-
.../column/unary/scalar/DateBinFunctionTest.java | 101 +++++++++++++
.../db/relational/grammar/sql/RelationalSql.g4 | 2 +
9 files changed, 375 insertions(+), 3 deletions(-)
diff --git
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBScalarFunctionTableIT.java
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBScalarFunctionTableIT.java
index 247a0325e26..2a7d157677f 100644
---
a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBScalarFunctionTableIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBScalarFunctionTableIT.java
@@ -322,6 +322,20 @@ public class IoTDBScalarFunctionTableIT {
"INSERT INTO upperTable(Time,device_id,s1) values(3, 'd1', 'Abcdefg')",
"INSERT INTO upperTable(Time,device_id,s9) values(2, 'd1', 'Test')",
"INSERT INTO upperTable(Time,device_id,s9) values(3, 'd1', 'Abcdefg')",
+ // dateBinSQL use s8 to calculate
+ "create table dateBinTable(device_id STRING ID, s1 TEXT MEASUREMENT,
s2 INT32 MEASUREMENT, s3 INT64 MEASUREMENT, s4 FLOAT MEASUREMENT, s5 DOUBLE
MEASUREMENT, s6 BOOLEAN MEASUREMENT, s7 DATE MEASUREMENT, s8 TIMESTAMP
MEASUREMENT, s9 STRING MEASUREMENT, s10 BLOB MEASUREMENT)",
+ // 2024-01-01T00:00:00.000Z
+ "INSERT INTO
dateBinTable(Time,device_id,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10) values(1, 'd1',
'Test', 1, 1, 1, 1, true, '2024-01-01', 1704067200000, 'abcd', X'abcd')",
+ // 2024-01-01T01:00:00.000Z
+ "INSERT INTO dateBinTable(Time,device_id,s1,s8) values(2, 'd1',
'Test', 1704070800000)",
+ // 2024-01-01T01:59:00.000Z
+ "INSERT INTO dateBinTable(Time,device_id,s1,s8) values(3, 'd1',
'Test', 1704074340000)",
+ // 2023-12-31T23:59:00.000Z
+ "INSERT INTO dateBinTable(Time,device_id,s1,s8) values(4, 'd1',
'Test', 1704067140000)",
+ // 1969-12-31T23:59:00.000Z
+ "INSERT INTO dateBinTable(Time,device_id,s1,s8) values(5, 'd1',
'Test', -60000)",
+ // null
+ "INSERT INTO dateBinTable(Time,device_id,s1,s8) values(6, 'd1',
'Test', null)",
"flush"
};
@@ -2666,4 +2680,44 @@ public class IoTDBScalarFunctionTableIT {
+ ": Scalar function upper only accepts one argument and it must
be text or string data type.",
DATABASE_NAME);
}
+
+ @Test
+ public void dateBinTestNormal() {
+ String[] expectedHeader = new String[] {"time", "s1", "s8", "_col3"};
+ String[] expectedAns =
+ new String[] {
+
"1970-01-01T00:00:00.001Z,Test,2024-01-01T00:00:00.000Z,2024-01-01T00:00:00.000Z,",
+
"1970-01-01T00:00:00.002Z,Test,2024-01-01T01:00:00.000Z,2024-01-01T01:00:00.000Z,",
+
"1970-01-01T00:00:00.003Z,Test,2024-01-01T01:59:00.000Z,2024-01-01T01:00:00.000Z,",
+
"1970-01-01T00:00:00.004Z,Test,2023-12-31T23:59:00.000Z,2023-12-31T23:00:00.000Z,",
+
"1970-01-01T00:00:00.005Z,Test,1969-12-31T23:59:00.000Z,1969-12-31T23:00:00.000Z,",
+ "1970-01-01T00:00:00.006Z,Test,null,null,",
+ };
+ tableResultSetEqualTest(
+ "select time,s1,s8,date_bin(1H, s8) from dateBinTable",
+ expectedHeader,
+ expectedAns,
+ DATABASE_NAME);
+ }
+
+ @Test
+ public void dateBinTestFail() {
+ tableAssertTestFail(
+ "select time,s1,s8,date_bin(1H,s8,0,0) from dateBinTable",
+ TSStatusCode.SQL_PARSE_ERROR.getStatusCode()
+ + ": line 1:35: mismatched input ','. Expecting: ')'",
+ DATABASE_NAME);
+
+ tableAssertTestFail(
+ "select time,s1,s8,date_bin(1H,s1) from dateBinTable",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Scalar function date_bin only accepts two or three arguments
and the second and third must be TimeStamp data type.",
+ DATABASE_NAME);
+
+ tableAssertTestFail(
+ "select time,s1,s8,date_bin(1MONTH 1DAY,s8) from dateBinTable",
+ TSStatusCode.SEMANTIC_ERROR.getStatusCode()
+ + ": Simultaneous setting of monthly and non-monthly intervals is
not supported.",
+ DATABASE_NAME);
+ }
}
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/relational/ColumnTransformerBuilder.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/relational/ColumnTransformerBuilder.java
index a1b853ad1b7..19fe9b2edb3 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/relational/ColumnTransformerBuilder.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/execution/relational/ColumnTransformerBuilder.java
@@ -95,6 +95,7 @@ import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.Co
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.ConcatMultiColumnTransformer;
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.CosColumnTransformer;
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.CoshColumnTransformer;
+import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.DateBinFunctionColumnTransformer;
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.DegreesColumnTransformer;
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.DiffColumnTransformer;
import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.DiffFunctionColumnTransformer;
@@ -897,6 +898,17 @@ public class ColumnTransformerBuilder
if (children.size() == 1) {
return new SqrtColumnTransformer(DOUBLE, first);
}
+ } else if (TableBuiltinScalarFunction.DATE_BIN
+ .getFunctionName()
+ .equalsIgnoreCase(functionName)) {
+ ColumnTransformer source = this.process(children.get(2), context);
+ return new DateBinFunctionColumnTransformer(
+ source.getType(),
+ ((LongLiteral) children.get(0)).getParsedValue(),
+ ((LongLiteral) children.get(1)).getParsedValue(),
+ source,
+ ((LongLiteral) children.get(3)).getParsedValue(),
+ context.sessionInfo.getZoneId());
}
throw new IllegalArgumentException(String.format("Unknown function: %s",
functionName));
}
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/metadata/TableMetadataImpl.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/metadata/TableMetadataImpl.java
index 12b88a7948f..6669dcc8a4a 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/metadata/TableMetadataImpl.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/metadata/TableMetadataImpl.java
@@ -46,7 +46,6 @@ import org.apache.iotdb.db.utils.constant.SqlConstant;
import org.apache.tsfile.file.metadata.IDeviceID;
import org.apache.tsfile.read.common.type.BlobType;
import org.apache.tsfile.read.common.type.StringType;
-import org.apache.tsfile.read.common.type.TimestampType;
import org.apache.tsfile.read.common.type.Type;
import org.apache.tsfile.read.common.type.TypeFactory;
@@ -65,6 +64,7 @@ import static
org.apache.tsfile.read.common.type.FloatType.FLOAT;
import static org.apache.tsfile.read.common.type.IntType.INT32;
import static org.apache.tsfile.read.common.type.LongType.INT64;
import static org.apache.tsfile.read.common.type.StringType.STRING;
+import static org.apache.tsfile.read.common.type.TimestampType.TIMESTAMP;
public class TableMetadataImpl implements Metadata {
@@ -455,6 +455,16 @@ public class TableMetadataImpl implements Metadata {
+ " only accepts one argument and it must be Double, Float,
Int32 or Int64 data type.");
}
return DOUBLE;
+ } else if (TableBuiltinScalarFunction.DATE_BIN
+ .getFunctionName()
+ .equalsIgnoreCase(functionName)) {
+ if (!(argumentTypes.size() == 4 &&
isTimestampType(argumentTypes.get(2)))) {
+ throw new SemanticException(
+ "Scalar function "
+ + functionName.toLowerCase(Locale.ENGLISH)
+ + " only accepts two or three arguments and the second and
third must be TimeStamp data type.");
+ }
+ return TIMESTAMP;
}
// builtin aggregation function
@@ -680,7 +690,11 @@ public class TableMetadataImpl implements Metadata {
|| FLOAT.equals(type)
|| INT32.equals(type)
|| INT64.equals(type)
- || TimestampType.TIMESTAMP.equals(type);
+ || TIMESTAMP.equals(type);
+ }
+
+ public static boolean isTimestampType(Type type) {
+ return TIMESTAMP.equals(type);
}
public static boolean isIntegerNumber(Type type) {
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/sql/parser/AstBuilder.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/sql/parser/AstBuilder.java
index 38f08edf593..928529452da 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/sql/parser/AstBuilder.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/sql/parser/AstBuilder.java
@@ -164,6 +164,7 @@ import javax.annotation.Nullable;
import java.time.ZoneId;
import java.util.ArrayDeque;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Collections;
import java.util.Deque;
import java.util.HashMap;
@@ -187,6 +188,7 @@ import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.GroupingSe
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.GroupingSets.Type.EXPLICIT;
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.GroupingSets.Type.ROLLUP;
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.QualifiedName.mapIdentifier;
+import static
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar.TableBuiltinScalarFunction.DATE_BIN;
public class AstBuilder extends RelationalSqlBaseVisitor<Node> {
@@ -1643,6 +1645,34 @@ public class AstBuilder extends
RelationalSqlBaseVisitor<Node> {
return new FunctionCall(getLocation(ctx), name, distinct, arguments);
}
+ public Node visitDateBin(RelationalSqlParser.DateBinContext ctx) {
+ TimeDuration timeDuration =
DateTimeUtils.constructTimeDuration(ctx.timeDuration().getText());
+
+ if (timeDuration.monthDuration != 0 && timeDuration.nonMonthDuration != 0)
{
+ throw new SemanticException(
+ "Simultaneous setting of monthly and non-monthly intervals is not
supported.");
+ }
+
+ LongLiteral monthDuration =
+ new LongLiteral(
+ getLocation(ctx.timeDuration()),
String.valueOf(timeDuration.monthDuration));
+ LongLiteral nonMonthDuration =
+ new LongLiteral(
+ getLocation(ctx.timeDuration()),
String.valueOf(timeDuration.nonMonthDuration));
+ LongLiteral origin =
+ ctx.timeValue() == null
+ ? new LongLiteral("0")
+ : new LongLiteral(
+ getLocation(ctx.timeValue()),
+ String.valueOf(parseTimeValue(ctx.timeValue(),
CommonDateTimeUtils.currentTime())));
+
+ List<Expression> arguments =
+ Arrays.asList(
+ monthDuration, nonMonthDuration, (Expression)
visit(ctx.valueExpression()), origin);
+ return new FunctionCall(
+ getLocation(ctx), QualifiedName.of(DATE_BIN.getFunctionName()),
arguments);
+ }
+
// ************** literals **************
@Override
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionColumnTransformer.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionColumnTransformer.java
new file mode 100644
index 00000000000..768ca7c0105
--- /dev/null
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionColumnTransformer.java
@@ -0,0 +1,158 @@
+/*
+ * Licensed 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.
+ */
+
+package org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar;
+
+import
org.apache.iotdb.db.queryengine.transformation.dag.column.ColumnTransformer;
+import
org.apache.iotdb.db.queryengine.transformation.dag.column.unary.UnaryColumnTransformer;
+
+import org.apache.tsfile.block.column.Column;
+import org.apache.tsfile.block.column.ColumnBuilder;
+import org.apache.tsfile.read.common.type.Type;
+
+import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.ZoneId;
+import java.time.temporal.ChronoUnit;
+import java.util.concurrent.TimeUnit;
+
+import static org.apache.iotdb.db.utils.DateTimeUtils.TIMESTAMP_PRECISION;
+
+public class DateBinFunctionColumnTransformer extends UnaryColumnTransformer {
+
+ private static final long NANOSECONDS_IN_MILLISECOND = 1_000_000;
+ private static final long NANOSECONDS_IN_MICROSECOND = 1_000;
+
+ private static long monthDuration;
+ private static long nonMonthDuration;
+ private static long origin;
+ private static ZoneId zoneId;
+
+ public DateBinFunctionColumnTransformer(
+ Type returnType,
+ long monthDuration,
+ long nonMonthDuration,
+ ColumnTransformer childColumnTransformer,
+ long origin,
+ ZoneId zoneId) {
+ super(returnType, childColumnTransformer);
+ this.monthDuration = monthDuration;
+ this.nonMonthDuration = nonMonthDuration;
+ this.origin = origin;
+ this.zoneId = zoneId;
+ }
+
+ // Harmonized to nanosecond timestamp accuracy
+ public LocalDateTime convertToLocalDateTime(long timestamp) {
+ Instant instant;
+
+ switch (TIMESTAMP_PRECISION) {
+ case "ms":
+ instant = Instant.ofEpochMilli(timestamp);
+ break;
+ case "us":
+ instant =
+ Instant.ofEpochSecond(
+ TimeUnit.MICROSECONDS.toSeconds(timestamp), (timestamp %
1_000_000) * 1000);
+ break;
+ case "ns":
+ instant =
+ Instant.ofEpochSecond(
+ TimeUnit.NANOSECONDS.toSeconds(timestamp), timestamp %
1_000_000_000);
+ break;
+ default:
+ throw new IllegalArgumentException("Unsupported precision: " +
TIMESTAMP_PRECISION);
+ }
+
+ return LocalDateTime.ofInstant(instant, zoneId);
+ }
+
+ public long convertToTimestamp(LocalDateTime dateTime) {
+ // Converting LocalDateTime to Seconds since epoch
+ long epochMilliSecond = dateTime.atZone(zoneId).toInstant().toEpochMilli();
+ // Get the nanoseconds section
+ long nanoAdjustment = dateTime.getNano();
+
+ switch (TIMESTAMP_PRECISION) {
+ case "ms":
+ return epochMilliSecond + nanoAdjustment / NANOSECONDS_IN_MILLISECOND;
+ case "us":
+ return TimeUnit.MILLISECONDS.toMicros(epochMilliSecond)
+ + nanoAdjustment / NANOSECONDS_IN_MICROSECOND;
+ case "ns":
+ return TimeUnit.MILLISECONDS.toNanos(epochMilliSecond) +
nanoAdjustment;
+ default:
+ throw new IllegalArgumentException("Unknown precision: " +
TIMESTAMP_PRECISION);
+ }
+ }
+
+ public long getNanoTimeStamp(long timestamp) {
+ switch (TIMESTAMP_PRECISION) {
+ case "ms":
+ return TimeUnit.MILLISECONDS.toNanos(timestamp);
+ case "us":
+ return TimeUnit.MICROSECONDS.toNanos(timestamp);
+ case "ns":
+ return timestamp;
+ default:
+ throw new IllegalArgumentException("Unknown precision: " +
TIMESTAMP_PRECISION);
+ }
+ }
+
+ public long dateBin(long source) {
+ // return source if interval is 0
+ if (monthDuration == 0 && nonMonthDuration == 0) {
+ return source;
+ }
+ if (monthDuration != 0) {
+ // convert to LocalDateTime
+ LocalDateTime sourceDate = convertToLocalDateTime(source);
+ LocalDateTime originDate = convertToLocalDateTime(origin);
+
+ // calculate the number of months between the origin and source
+ long monthsDiff = ChronoUnit.MONTHS.between(originDate, sourceDate);
+ // calculate the number of month cycles completed
+ long completedMonthCycles = monthsDiff / monthDuration;
+
+ LocalDateTime binStart =
+ originDate
+ .plusNanos(getNanoTimeStamp(nonMonthDuration) *
completedMonthCycles)
+ .plusMonths(completedMonthCycles * monthDuration);
+
+ if (binStart.isAfter(sourceDate)) {
+ binStart =
+
binStart.minusMonths(monthDuration).minusNanos(getNanoTimeStamp(nonMonthDuration));
+ }
+
+ return convertToTimestamp(binStart);
+ }
+
+ long diff = source - origin;
+ long n = diff >= 0 ? diff / nonMonthDuration : (diff - nonMonthDuration +
1) / nonMonthDuration;
+ return origin + (n * nonMonthDuration);
+ }
+
+ @Override
+ protected void doTransform(Column column, ColumnBuilder columnBuilder) {
+ for (int i = 0, n = column.getPositionCount(); i < n; i++) {
+ if (!column.isNull(i)) {
+ long result = dateBin(column.getLong(i));
+ columnBuilder.writeLong(result);
+ } else {
+ // If source is null, return null
+ columnBuilder.appendNull();
+ }
+ }
+ }
+}
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/TableBuiltinScalarFunction.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/TableBuiltinScalarFunction.java
index 663bb0d22e6..52bd6cd7c0e 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/TableBuiltinScalarFunction.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/TableBuiltinScalarFunction.java
@@ -56,6 +56,7 @@ public enum TableBuiltinScalarFunction {
LN("ln"),
LOG10("log10"),
SQRT("sqrt"),
+ DATE_BIN("date_bin"),
;
private final String functionName;
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DateTimeUtils.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DateTimeUtils.java
index 089aafa0106..99c0445485e 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DateTimeUtils.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/utils/DateTimeUtils.java
@@ -55,7 +55,7 @@ public class DateTimeUtils {
// forbidding instantiation
}
- private static final String TIMESTAMP_PRECISION =
+ public static final String TIMESTAMP_PRECISION =
CommonDescriptor.getInstance().getConfig().getTimestampPrecision();
public static long correctPrecision(long millis) {
diff --git
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionTest.java
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionTest.java
new file mode 100644
index 00000000000..ef28168881d
--- /dev/null
+++
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/transformation/dag/column/unary/scalar/DateBinFunctionTest.java
@@ -0,0 +1,101 @@
+/*
+ * Licensed 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.
+ */
+
+package org.apache.iotdb.db.queryengine.transformation.dag.column.unary.scalar;
+
+import org.junit.Before;
+import org.junit.Test;
+
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.time.ZoneId;
+import java.util.TimeZone;
+
+import static org.apache.tsfile.read.common.type.TimestampType.TIMESTAMP;
+import static org.junit.Assert.assertEquals;
+
+public class DateBinFunctionTest {
+
+ private static final SimpleDateFormat format = new
SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+ private static DateBinFunctionColumnTransformer transformer;
+
+ private static final long MILLIS_IN_SECOND = 1000;
+ private static final long MILLIS_IN_MINUTE = 60 * MILLIS_IN_SECOND;
+ private static final long MILLIS_IN_HOUR = 60 * MILLIS_IN_MINUTE;
+ private static final long MILLIS_IN_DAY = 24 * MILLIS_IN_HOUR;
+
+ @Before
+ public void before() {
+ format.setTimeZone(TimeZone.getTimeZone("UTC"));
+ }
+
+ public long getTimestamp(String time) throws ParseException {
+ return format.parse(time).getTime();
+ }
+
+ public DateBinFunctionColumnTransformer getTransformer(
+ long monthDuration, long nonMonthDuration, long origin) {
+ return new DateBinFunctionColumnTransformer(
+ TIMESTAMP, monthDuration, nonMonthDuration, null, origin,
ZoneId.of("UTC+0"));
+ }
+
+ @Test
+ public void testWithoutOrigin() throws ParseException {
+ transformer = getTransformer(0, MILLIS_IN_DAY, 0);
+ long result = transformer.dateBin(getTimestamp("2000-01-01 00:00:00.000"));
+ assertEquals(getTimestamp("2000-01-01 00:00:00.000"), result);
+ }
+
+ @Test
+ public void testOrigin() throws ParseException {
+ transformer = getTransformer(0, MILLIS_IN_HOUR, getTimestamp("2000-01-01
01:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("2000-01-01 01:30:00.000"));
+ assertEquals(getTimestamp("2000-01-01 01:00:00.000"), result);
+ }
+
+ @Test
+ public void testMonthInterval() throws ParseException {
+ transformer = getTransformer(1, 0, getTimestamp("2000-01-01
00:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("2000-01-01 00:30:00.000"));
+ assertEquals(getTimestamp("2000-01-01 00:00:00.000"), result);
+ }
+
+ @Test
+ public void testOriginGtSource() throws ParseException {
+ transformer = getTransformer(1, 0, getTimestamp("2000-05-01
00:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("2000-01-01 00:00:00.000"));
+ assertEquals(getTimestamp("2000-01-01 00:00:00.000"), result);
+ }
+
+ @Test
+ public void testOriginBeforeUnixEpoch() throws ParseException {
+ transformer = getTransformer(0, MILLIS_IN_DAY, getTimestamp("1969-12-31
00:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("2000-01-01 00:00:00.000"));
+ assertEquals(getTimestamp("2000-01-01 00:00:00.000"), result);
+ }
+
+ @Test
+ public void testZeroInterval() throws ParseException {
+ transformer = getTransformer(0, 0, getTimestamp("2000-01-01
00:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("2000-01-01 00:00:00.000"));
+ assertEquals(getTimestamp("2000-01-01 00:00:00.000"), result);
+ }
+
+ @Test
+ public void testSourceBeforeUnixEpoch() throws ParseException {
+ transformer = getTransformer(0, MILLIS_IN_DAY, getTimestamp("2000-01-01
00:00:00.000"));
+ long result = transformer.dateBin(getTimestamp("1969-12-31 23:00:00.000"));
+ assertEquals(getTimestamp("1969-12-31 00:00:00.000"), result);
+ }
+}
diff --git
a/iotdb-core/relational-grammar/src/main/antlr4/org/apache/iotdb/db/relational/grammar/sql/RelationalSql.g4
b/iotdb-core/relational-grammar/src/main/antlr4/org/apache/iotdb/db/relational/grammar/sql/RelationalSql.g4
index 0e61741d4c8..395c902636d 100644
---
a/iotdb-core/relational-grammar/src/main/antlr4/org/apache/iotdb/db/relational/grammar/sql/RelationalSql.g4
+++
b/iotdb-core/relational-grammar/src/main/antlr4/org/apache/iotdb/db/relational/grammar/sql/RelationalSql.g4
@@ -570,6 +570,7 @@ primaryExpression
trimSource=valueExpression ')'
#trim
| TRIM '(' trimSource=valueExpression ',' trimChar=valueExpression ')'
#trim
| SUBSTRING '(' valueExpression FROM valueExpression (FOR
valueExpression)? ')' #substring
+ | DATE_BIN '(' timeDuration ',' valueExpression (',' timeValue)? ')'
#dateBin
| '(' expression ')'
#parenthesizedExpression
;
@@ -809,6 +810,7 @@ DATABASE: 'DATABASE';
DATABASES: 'DATABASES';
DATANODES: 'DATANODES';
DATE: 'DATE';
+DATE_BIN: 'DATE_BIN';
DAY: 'DAY' | 'D';
DEALLOCATE: 'DEALLOCATE';
DECLARE: 'DECLARE';
