Skip to content

Commit 2a1794f

Browse files
Yury-Fridlyandgithub-actions[bot]
Yury-Fridlyand
authored and
github-actions[bot]
committed
Add functions ADDTIME and SUBTIME. (#132) (#1194)
* Add functions `ADDTIME` and `SUBTIME`. (#132) Signed-off-by: Yury-Fridlyand <yury.fridlyand@improving.com> (cherry picked from commit 7630f87)
1 parent 628be97 commit 2a1794f

13 files changed

Lines changed: 626 additions & 12 deletions

File tree

‎core/src/main/java/org/opensearch/sql/expression/datetime/DateTimeFunction.java‎

Lines changed: 140 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_TIME_FORMATTER_SHORT_YEAR;
3131
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_TIME_FORMATTER_STRICT_WITH_TZ;
3232
import static org.opensearch.sql.utils.DateTimeUtils.extractDate;
33+
import static org.opensearch.sql.utils.DateTimeUtils.extractDateTime;
3334

3435
import java.math.BigDecimal;
3536
import java.math.RoundingMode;
@@ -95,6 +96,7 @@ public class DateTimeFunction {
9596
*/
9697
public void register(BuiltinFunctionRepository repository) {
9798
repository.register(adddate());
99+
repository.register(addtime());
98100
repository.register(convert_tz());
99101
repository.register(curtime());
100102
repository.register(curdate());
@@ -132,6 +134,7 @@ public void register(BuiltinFunctionRepository repository) {
132134
repository.register(second(BuiltinFunctionName.SECOND));
133135
repository.register(second(BuiltinFunctionName.SECOND_OF_MINUTE));
134136
repository.register(subdate());
137+
repository.register(subtime());
135138
repository.register(sysdate());
136139
repository.register(time());
137140
repository.register(time_to_sec());
@@ -247,6 +250,52 @@ private DefaultFunctionResolver adddate() {
247250
return add_date(BuiltinFunctionName.ADDDATE.getName());
248251
}
249252

253+
/**
254+
* Adds expr2 to expr1 and returns the result.
255+
* (TIME, TIME/DATE/DATETIME/TIMESTAMP) -> TIME
256+
* (DATE/DATETIME/TIMESTAMP, TIME/DATE/DATETIME/TIMESTAMP) -> DATETIME
257+
* TODO: MySQL has these signatures too
258+
* (STRING, STRING/TIME) -> STRING // second arg - string with time only
259+
* (x, STRING) -> NULL // second arg - string with timestamp
260+
* (x, STRING/DATE) -> x // second arg - string with date only
261+
*/
262+
private DefaultFunctionResolver addtime() {
263+
return define(BuiltinFunctionName.ADDTIME.getName(),
264+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
265+
TIME, TIME, TIME),
266+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
267+
TIME, TIME, DATE),
268+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
269+
TIME, TIME, DATETIME),
270+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
271+
TIME, TIME, TIMESTAMP),
272+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
273+
DATETIME, DATETIME, TIME),
274+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
275+
DATETIME, DATETIME, DATE),
276+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
277+
DATETIME, DATETIME, DATETIME),
278+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
279+
DATETIME, DATETIME, TIMESTAMP),
280+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
281+
DATETIME, DATE, TIME),
282+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
283+
DATETIME, DATE, DATE),
284+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
285+
DATETIME, DATE, DATETIME),
286+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
287+
DATETIME, DATE, TIMESTAMP),
288+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
289+
DATETIME, TIMESTAMP, TIME),
290+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
291+
DATETIME, TIMESTAMP, DATE),
292+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
293+
DATETIME, TIMESTAMP, DATETIME),
294+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprAddTime),
295+
DATETIME, TIMESTAMP, TIMESTAMP)
296+
);
297+
}
298+
250299
/**
251300
* Converts date/time from a specified timezone to another specified timezone.
252301
* The supported signatures:
@@ -573,6 +622,52 @@ private DefaultFunctionResolver subdate() {
573622
return sub_date(BuiltinFunctionName.SUBDATE.getName());
574623
}
575624

625+
/**
626+
* Subtracts expr2 from expr1 and returns the result.
627+
* (TIME, TIME/DATE/DATETIME/TIMESTAMP) -> TIME
628+
* (DATE/DATETIME/TIMESTAMP, TIME/DATE/DATETIME/TIMESTAMP) -> DATETIME
629+
* TODO: MySQL has these signatures too
630+
* (STRING, STRING/TIME) -> STRING // second arg - string with time only
631+
* (x, STRING) -> NULL // second arg - string with timestamp
632+
* (x, STRING/DATE) -> x // second arg - string with date only
633+
*/
634+
private DefaultFunctionResolver subtime() {
635+
return define(BuiltinFunctionName.SUBTIME.getName(),
636+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
637+
TIME, TIME, TIME),
638+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
639+
TIME, TIME, DATE),
640+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
641+
TIME, TIME, DATETIME),
642+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
643+
TIME, TIME, TIMESTAMP),
644+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
645+
DATETIME, DATETIME, TIME),
646+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
647+
DATETIME, DATETIME, DATE),
648+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
649+
DATETIME, DATETIME, DATETIME),
650+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
651+
DATETIME, DATETIME, TIMESTAMP),
652+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
653+
DATETIME, DATE, TIME),
654+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
655+
DATETIME, DATE, DATE),
656+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
657+
DATETIME, DATE, DATETIME),
658+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
659+
DATETIME, DATE, TIMESTAMP),
660+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
661+
DATETIME, TIMESTAMP, TIME),
662+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
663+
DATETIME, TIMESTAMP, DATE),
664+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
665+
DATETIME, TIMESTAMP, DATETIME),
666+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprSubTime),
667+
DATETIME, TIMESTAMP, TIMESTAMP)
668+
);
669+
}
670+
576671
/**
577672
* Extracts the time part of a date and time value.
578673
* Also to construct a time type. The supported signatures:
@@ -752,6 +847,39 @@ private ExprValue exprAddDateDays(ExprValue date, ExprValue days) {
752847
: exprValue);
753848
}
754849

850+
/**
851+
* Adds or subtracts time to/from date and returns the result.
852+
*
853+
* @param functionProperties A FunctionProperties object.
854+
* @param temporal A Date/Time/Datetime/Timestamp value to change.
855+
* @param temporalDelta A Date/Time/Datetime/Timestamp object to add/subtract time from.
856+
* @param isAdd A flag: true to add, false to subtract.
857+
* @return A value calculated.
858+
*/
859+
private ExprValue exprApplyTime(FunctionProperties functionProperties,
860+
ExprValue temporal, ExprValue temporalDelta, Boolean isAdd) {
861+
var interval = Duration.between(LocalTime.MIN, temporalDelta.timeValue());
862+
var result = isAdd
863+
? extractDateTime(temporal, functionProperties).plus(interval)
864+
: extractDateTime(temporal, functionProperties).minus(interval);
865+
return temporal.type() == TIME
866+
? new ExprTimeValue(result.toLocalTime())
867+
: new ExprDatetimeValue(result);
868+
}
869+
870+
/**
871+
* Adds time to date and returns the result.
872+
*
873+
* @param functionProperties A FunctionProperties object.
874+
* @param temporal A Date/Time/Datetime/Timestamp value to change.
875+
* @param temporalDelta A Date/Time/Datetime/Timestamp object to add time from.
876+
* @return A value calculated.
877+
*/
878+
private ExprValue exprAddTime(FunctionProperties functionProperties,
879+
ExprValue temporal, ExprValue temporalDelta) {
880+
return exprApplyTime(functionProperties, temporal, temporalDelta, true);
881+
}
882+
755883
/**
756884
* CONVERT_TZ function implementation for ExprValue.
757885
* Returns null for time zones outside of +13:00 and -12:00.
@@ -1164,6 +1292,18 @@ private ExprValue exprSubDateInterval(ExprValue date, ExprValue expr) {
11641292
: exprValue);
11651293
}
11661294

1295+
/**
1296+
* Subtracts expr2 from expr1 and returns the result.
1297+
*
1298+
* @param temporal A Date/Time/Datetime/Timestamp value to change.
1299+
* @param temporalDelta A Date/Time/Datetime/Timestamp to subtract time from.
1300+
* @return A value calculated.
1301+
*/
1302+
private ExprValue exprSubTime(FunctionProperties functionProperties,
1303+
ExprValue temporal, ExprValue temporalDelta) {
1304+
return exprApplyTime(functionProperties, temporal, temporalDelta, false);
1305+
}
1306+
11671307
/**
11681308
* Time implementation for ExprValue.
11691309
*

‎core/src/main/java/org/opensearch/sql/expression/function/BuiltinFunctionName.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,7 @@ public enum BuiltinFunctionName {
5959
* Date and Time Functions.
6060
*/
6161
ADDDATE(FunctionName.of("adddate")),
62+
ADDTIME(FunctionName.of("addtime")),
6263
CONVERT_TZ(FunctionName.of("convert_tz")),
6364
DATE(FunctionName.of("date")),
6465
DATEDIFF(FunctionName.of("datediff")),
@@ -88,6 +89,7 @@ public enum BuiltinFunctionName {
8889
SECOND(FunctionName.of("second")),
8990
SECOND_OF_MINUTE(FunctionName.of("second_of_minute")),
9091
SUBDATE(FunctionName.of("subdate")),
92+
SUBTIME(FunctionName.of("subtime")),
9193
TIME(FunctionName.of("time")),
9294
TIMEDIFF(FunctionName.of("timediff")),
9395
TIME_TO_SEC(FunctionName.of("time_to_sec")),

‎core/src/main/java/org/opensearch/sql/utils/DateTimeUtils.java‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,6 +130,17 @@ public Boolean isValidMySqlTimeZoneId(ZoneId zone) {
130130
|| passedTzValidator.isEqual(minTzValidator));
131131
}
132132

133+
/**
134+
* Extracts LocalDateTime from a datetime ExprValue.
135+
* Uses `FunctionProperties` for `ExprTimeValue`.
136+
*/
137+
public static LocalDateTime extractDateTime(ExprValue value,
138+
FunctionProperties functionProperties) {
139+
return value instanceof ExprTimeValue
140+
? ((ExprTimeValue) value).datetimeValue(functionProperties)
141+
: value.datetimeValue();
142+
}
143+
133144
/**
134145
* Extracts LocalDate from a datetime ExprValue.
135146
* Uses `FunctionProperties` for `ExprTimeValue`.

‎core/src/test/java/org/opensearch/sql/expression/datetime/AddTimeAndSubTimeTest.java‎

Lines changed: 127 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,127 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.expression.datetime;
7+
8+
import static org.junit.jupiter.api.Assertions.assertEquals;
9+
import static org.opensearch.sql.data.type.ExprCoreType.DATETIME;
10+
import static org.opensearch.sql.data.type.ExprCoreType.TIME;
11+
12+
import java.time.Instant;
13+
import java.time.LocalDate;
14+
import java.time.LocalDateTime;
15+
import java.time.LocalTime;
16+
import java.time.temporal.Temporal;
17+
import java.util.stream.Stream;
18+
import org.junit.jupiter.api.Test;
19+
import org.junit.jupiter.params.ParameterizedTest;
20+
import org.junit.jupiter.params.provider.Arguments;
21+
import org.junit.jupiter.params.provider.MethodSource;
22+
23+
public class AddTimeAndSubTimeTest extends DateTimeTestBase {
24+
25+
@Test
26+
// (TIME, TIME/DATE/DATETIME/TIMESTAMP) -> TIME
27+
public void return_time_when_first_arg_is_time() {
28+
var res = addtime(LocalTime.of(21, 0), LocalTime.of(0, 5));
29+
assertEquals(TIME, res.type());
30+
assertEquals(LocalTime.of(21, 5), res.timeValue());
31+
32+
res = subtime(LocalTime.of(21, 0), LocalTime.of(0, 5));
33+
assertEquals(TIME, res.type());
34+
assertEquals(LocalTime.of(20, 55), res.timeValue());
35+
36+
res = addtime(LocalTime.of(12, 20), Instant.ofEpochSecond(42));
37+
assertEquals(TIME, res.type());
38+
assertEquals(LocalTime.of(12, 20, 42), res.timeValue());
39+
40+
res = subtime(LocalTime.of(10, 0), Instant.ofEpochSecond(42));
41+
assertEquals(TIME, res.type());
42+
assertEquals(LocalTime.of(9, 59, 18), res.timeValue());
43+
44+
res = addtime(LocalTime.of(2, 3, 4), LocalDateTime.of(1961, 4, 12, 9, 7));
45+
assertEquals(TIME, res.type());
46+
assertEquals(LocalTime.of(11, 10, 4), res.timeValue());
47+
48+
res = subtime(LocalTime.of(12, 3, 4), LocalDateTime.of(1961, 4, 12, 9, 7));
49+
assertEquals(TIME, res.type());
50+
assertEquals(LocalTime.of(2, 56, 4), res.timeValue());
51+
52+
res = addtime(LocalTime.of(9, 7), LocalDate.now());
53+
assertEquals(TIME, res.type());
54+
assertEquals(LocalTime.of(9, 7), res.timeValue());
55+
56+
res = subtime(LocalTime.of(9, 7), LocalDate.of(1961, 4, 12));
57+
assertEquals(TIME, res.type());
58+
assertEquals(LocalTime.of(9, 7), res.timeValue());
59+
}
60+
61+
@Test
62+
public void time_limited_by_24_hours() {
63+
var res = addtime(LocalTime.of(21, 0), LocalTime.of(14, 5));
64+
assertEquals(TIME, res.type());
65+
assertEquals(LocalTime.of(11, 5), res.timeValue());
66+
67+
res = subtime(LocalTime.of(14, 0), LocalTime.of(21, 5));
68+
assertEquals(TIME, res.type());
69+
assertEquals(LocalTime.of(16, 55), res.timeValue());
70+
}
71+
72+
// Function signature is:
73+
// (DATE/DATETIME/TIMESTAMP, TIME/DATE/DATETIME/TIMESTAMP) -> DATETIME
74+
private static Stream<Arguments> getTestData() {
75+
return Stream.of(
76+
// DATETIME and TIME/DATE/DATETIME/TIMESTAMP
77+
Arguments.of(LocalDateTime.of(1961, 4, 12, 9, 7), LocalTime.of(1, 48),
78+
LocalDateTime.of(1961, 4, 12, 10, 55), LocalDateTime.of(1961, 4, 12, 7, 19)),
79+
Arguments.of(LocalDateTime.of(1961, 4, 12, 9, 7), LocalDate.of(2000, 1, 1),
80+
LocalDateTime.of(1961, 4, 12, 9, 7), LocalDateTime.of(1961, 4, 12, 9, 7)),
81+
Arguments.of(LocalDateTime.of(1961, 4, 12, 9, 7), LocalDateTime.of(1235, 5, 6, 1, 48),
82+
LocalDateTime.of(1961, 4, 12, 10, 55), LocalDateTime.of(1961, 4, 12, 7, 19)),
83+
Arguments.of(LocalDateTime.of(1961, 4, 12, 9, 7), Instant.ofEpochSecond(42),
84+
LocalDateTime.of(1961, 4, 12, 9, 7, 42), LocalDateTime.of(1961, 4, 12, 9, 6, 18)),
85+
// DATE and TIME/DATE/DATETIME/TIMESTAMP
86+
Arguments.of(LocalDate.of(1961, 4, 12), LocalTime.of(9, 7),
87+
LocalDateTime.of(1961, 4, 12, 9, 7), LocalDateTime.of(1961, 4, 11, 14, 53)),
88+
Arguments.of(LocalDate.of(1961, 4, 12), LocalDate.of(2000, 1, 1),
89+
LocalDateTime.of(1961, 4, 12, 0, 0), LocalDateTime.of(1961, 4, 12, 0, 0)),
90+
Arguments.of(LocalDate.of(1961, 4, 12), LocalDateTime.of(1235, 5, 6, 1, 48),
91+
LocalDateTime.of(1961, 4, 12, 1, 48), LocalDateTime.of(1961, 4, 11, 22, 12)),
92+
Arguments.of(LocalDate.of(1961, 4, 12), Instant.ofEpochSecond(42),
93+
LocalDateTime.of(1961, 4, 12, 0, 0, 42), LocalDateTime.of(1961, 4, 11, 23, 59, 18)),
94+
// TIMESTAMP and TIME/DATE/DATETIME/TIMESTAMP
95+
Arguments.of(Instant.ofEpochSecond(42), LocalTime.of(9, 7),
96+
LocalDateTime.of(1970, 1, 1, 9, 7, 42), LocalDateTime.of(1969, 12, 31, 14, 53, 42)),
97+
Arguments.of(Instant.ofEpochSecond(42), LocalDate.of(1961, 4, 12),
98+
LocalDateTime.of(1970, 1, 1, 0, 0, 42), LocalDateTime.of(1970, 1, 1, 0, 0, 42)),
99+
Arguments.of(Instant.ofEpochSecond(42), LocalDateTime.of(1961, 4, 12, 9, 7),
100+
LocalDateTime.of(1970, 1, 1, 9, 7, 42), LocalDateTime.of(1969, 12, 31, 14, 53, 42)),
101+
Arguments.of(Instant.ofEpochSecond(42), Instant.ofEpochMilli(42),
102+
LocalDateTime.of(1970, 1, 1, 0, 0, 42, 42000000),
103+
LocalDateTime.of(1970, 1, 1, 0, 0, 41, 958000000))
104+
);
105+
}
106+
107+
/**
108+
* Check that `ADDTIME` and `SUBTIME` functions result value and type.
109+
* @param arg1 First argument.
110+
* @param arg2 Second argument.
111+
* @param addTimeExpectedResult Expected result for `ADDTIME`.
112+
* @param subTimeExpectedResult Expected result for `SUBTIME`.
113+
*/
114+
@ParameterizedTest
115+
@MethodSource("getTestData")
116+
public void return_datetime_when_first_arg_is_not_time(Temporal arg1, Temporal arg2,
117+
LocalDateTime addTimeExpectedResult,
118+
LocalDateTime subTimeExpectedResult) {
119+
var res = addtime(arg1, arg2);
120+
assertEquals(DATETIME, res.type());
121+
assertEquals(addTimeExpectedResult, res.datetimeValue());
122+
123+
res = subtime(arg1, arg2);
124+
assertEquals(DATETIME, res.type());
125+
assertEquals(subTimeExpectedResult, res.datetimeValue());
126+
}
127+
}

0 commit comments

Comments
 (0)