Skip to content

Commit b366b76

Browse files
GabeFernandez310GabeFernandez310
authored
Add The TO_SECONDS Function To The SQL Plugin (#232)
* Added Basic Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added IT Test Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Implementation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Changed Integration Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Test For Time Type Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Documentation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Addressed PR Comments Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Docs and Implementation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Checkstyle Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Changed DateTimeFormatter Priority Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added More Formatters Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Updated Docs Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Reworked Implementation For Formatters Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Cleanup Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Test Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Implementation And Code Coverage Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Changed getFormatter Function Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Comments Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com>
1 parent 259b001 commit b366b76

10 files changed

Lines changed: 323 additions & 0 deletions

File tree

‎core/src/main/java/org/opensearch/sql/expression/DSL.java‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -306,6 +306,10 @@ public static FunctionExpression datetime(Expression... expressions) {
306306
return compile(FunctionProperties.None, BuiltinFunctionName.DATETIME, expressions);
307307
}
308308

309+
public static FunctionExpression date_add(Expression... expressions) {
310+
return compile(FunctionProperties.None, BuiltinFunctionName.DATE_ADD, expressions);
311+
}
312+
309313
public static FunctionExpression day(Expression... expressions) {
310314
return compile(FunctionProperties.None, BuiltinFunctionName.DAY, expressions);
311315
}
@@ -434,6 +438,16 @@ public static FunctionExpression to_days(Expression... expressions) {
434438
return compile(FunctionProperties.None, BuiltinFunctionName.TO_DAYS, expressions);
435439
}
436440

441+
public static FunctionExpression to_seconds(FunctionProperties functionProperties,
442+
Expression... expressions) {
443+
return compile(functionProperties, BuiltinFunctionName.TO_SECONDS, expressions);
444+
}
445+
446+
public static FunctionExpression to_seconds(Expression... expressions) {
447+
return to_seconds(FunctionProperties.None, expressions);
448+
}
449+
450+
437451
public static FunctionExpression week(
438452
FunctionProperties functionProperties, Expression... expressions) {
439453
return compile(functionProperties, BuiltinFunctionName.WEEK, expressions);

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

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,10 +27,18 @@
2727
import static org.opensearch.sql.expression.function.FunctionDSL.nullMissingHandling;
2828
import static org.opensearch.sql.expression.function.FunctionDSL.nullMissingHandlingWithProperties;
2929
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_FORMATTER_LONG_YEAR;
30+
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_FORMATTER_NO_YEAR;
3031
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_FORMATTER_SHORT_YEAR;
32+
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_FORMATTER_SINGLE_DIGIT_MONTH;
33+
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_FORMATTER_SINGLE_DIGIT_YEAR;
3134
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_TIME_FORMATTER_LONG_YEAR;
3235
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_TIME_FORMATTER_SHORT_YEAR;
3336
import static org.opensearch.sql.utils.DateTimeFormatters.DATE_TIME_FORMATTER_STRICT_WITH_TZ;
37+
import static org.opensearch.sql.utils.DateTimeFormatters.FULL_DATE_LENGTH;
38+
import static org.opensearch.sql.utils.DateTimeFormatters.NO_YEAR_DATE_LENGTH;
39+
import static org.opensearch.sql.utils.DateTimeFormatters.SHORT_DATE_LENGTH;
40+
import static org.opensearch.sql.utils.DateTimeFormatters.SINGLE_DIGIT_MONTH_DATE_LENGTH;
41+
import static org.opensearch.sql.utils.DateTimeFormatters.SINGLE_DIGIT_YEAR_DATE_LENGTH;
3442
import static org.opensearch.sql.utils.DateTimeUtils.extractDate;
3543
import static org.opensearch.sql.utils.DateTimeUtils.extractDateTime;
3644

@@ -93,6 +101,9 @@
93101
@UtilityClass
94102
@SuppressWarnings("unchecked")
95103
public class DateTimeFunction {
104+
//The number of seconds per day
105+
public static final long SECONDS_PER_DAY = 86400;
106+
96107
// The number of days from year zero to year 1970.
97108
private static final Long DAYS_0000_TO_1970 = (146097 * 5L) - (30L * 365L + 7L);
98109

@@ -103,6 +114,7 @@ public class DateTimeFunction {
103114
// Mode used for week/week_of_year function by default when no argument is provided
104115
private static final ExprIntegerValue DEFAULT_WEEK_OF_YEAR_MODE = new ExprIntegerValue(0);
105116

117+
106118
// Map used to determine format output for the get_format function
107119
private static final Table<String, String, String> formats =
108120
ImmutableTable.<String, String, String>builder()
@@ -191,6 +203,7 @@ public void register(BuiltinFunctionRepository repository) {
191203
repository.register(utc_timestamp());
192204
repository.register(date_format());
193205
repository.register(to_days());
206+
repository.register(to_seconds());
194207
repository.register(unix_timestamp());
195208
repository.register(week(BuiltinFunctionName.WEEK));
196209
repository.register(week(BuiltinFunctionName.WEEKOFYEAR));
@@ -826,6 +839,17 @@ private DefaultFunctionResolver to_days() {
826839
impl(nullMissingHandling(DateTimeFunction::exprToDays), LONG, DATETIME));
827840
}
828841

842+
/**
843+
* TO_SECONDS(TIMESTAMP/LONG). return the seconds number of the given date.
844+
* Arguments of type STRING/TIMESTAMP/LONG are also accepted.
845+
* STRING/TIMESTAMP/LONG arguments are automatically cast to TIMESTAMP.
846+
*/
847+
private DefaultFunctionResolver to_seconds() {
848+
return define(BuiltinFunctionName.TO_SECONDS.getName(),
849+
impl(nullMissingHandling(DateTimeFunction::exprToSeconds), LONG, TIMESTAMP),
850+
impl(nullMissingHandling(DateTimeFunction::exprToSecondsForIntType), LONG, LONG));
851+
}
852+
829853
private FunctionResolver unix_timestamp() {
830854
return define(BuiltinFunctionName.UNIX_TIMESTAMP.getName(),
831855
implWithProperties(functionProperties
@@ -1626,6 +1650,80 @@ private ExprValue exprToDays(ExprValue date) {
16261650
return new ExprLongValue(date.dateValue().toEpochDay() + DAYS_0000_TO_1970);
16271651
}
16281652

1653+
/**
1654+
* To_seconds implementation for ExprValue.
1655+
*
1656+
* @param date ExprValue of Date/Datetime/Timestamp/String type.
1657+
* @return ExprValue.
1658+
*/
1659+
private ExprValue exprToSeconds(ExprValue date) {
1660+
return new ExprLongValue(
1661+
date.datetimeValue().toEpochSecond(ZoneOffset.UTC) + DAYS_0000_TO_1970 * SECONDS_PER_DAY);
1662+
}
1663+
1664+
/**
1665+
* Helper function to determine the correct formatter for date arguments passed in as integers.
1666+
*
1667+
* @param dateAsInt is an integer formatted as one of YYYYMMDD, YYMMDD, YMMDD, MMDD, MDD
1668+
* @return is a DateTimeFormatter that can parse the input.
1669+
*/
1670+
private DateTimeFormatter getFormatter(int dateAsInt) {
1671+
int length = String.format("%d", dateAsInt).length();
1672+
1673+
if (length > 8) {
1674+
throw new DateTimeException("Integer argument was out of range");
1675+
}
1676+
1677+
//Check below from YYYYMMDD - MMDD which format should be used
1678+
switch (length) {
1679+
//Check if dateAsInt is at least 8 digits long
1680+
case FULL_DATE_LENGTH:
1681+
return DATE_FORMATTER_LONG_YEAR;
1682+
1683+
//Check if dateAsInt is at least 6 digits long
1684+
case SHORT_DATE_LENGTH:
1685+
return DATE_FORMATTER_SHORT_YEAR;
1686+
1687+
//Check if dateAsInt is at least 5 digits long
1688+
case SINGLE_DIGIT_YEAR_DATE_LENGTH:
1689+
return DATE_FORMATTER_SINGLE_DIGIT_YEAR;
1690+
1691+
//Check if dateAsInt is at least 4 digits long
1692+
case NO_YEAR_DATE_LENGTH:
1693+
return DATE_FORMATTER_NO_YEAR;
1694+
1695+
//Check if dateAsInt is at least 3 digits long
1696+
case SINGLE_DIGIT_MONTH_DATE_LENGTH:
1697+
return DATE_FORMATTER_SINGLE_DIGIT_MONTH;
1698+
1699+
default:
1700+
break;
1701+
}
1702+
1703+
throw new DateTimeException("No Matching Format");
1704+
}
1705+
1706+
/**
1707+
* To_seconds implementation with an integer argument for ExprValue.
1708+
*
1709+
* @param dateExpr ExprValue of an Integer/Long formatted for a date (e.g., 950501 = 1995-05-01)
1710+
* @return ExprValue.
1711+
*/
1712+
private ExprValue exprToSecondsForIntType(ExprValue dateExpr) {
1713+
try {
1714+
//Attempt to parse integer argument as date
1715+
LocalDate date = LocalDate.parse(String.valueOf(dateExpr.integerValue()),
1716+
getFormatter(dateExpr.integerValue()));
1717+
1718+
return new ExprLongValue(date.toEpochSecond(LocalTime.MIN, ZoneOffset.UTC)
1719+
+ DAYS_0000_TO_1970 * SECONDS_PER_DAY);
1720+
1721+
} catch (DateTimeException ignored) {
1722+
//Return null if parsing error
1723+
return ExprNullValue.of();
1724+
}
1725+
}
1726+
16291727
/**
16301728
* Week for date implementation for ExprValue.
16311729
*

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@ public enum BuiltinFunctionName {
104104
TIMESTAMP(FunctionName.of("timestamp")),
105105
TIME_FORMAT(FunctionName.of("time_format")),
106106
TO_DAYS(FunctionName.of("to_days")),
107+
TO_SECONDS(FunctionName.of("to_seconds")),
107108
UTC_DATE(FunctionName.of("utc_date")),
108109
UTC_TIME(FunctionName.of("utc_time")),
109110
UTC_TIMESTAMP(FunctionName.of("utc_timestamp")),

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,21 @@
2929
@UtilityClass
3030
public class DateTimeFormatters {
3131

32+
//Length of a date formatted as YYYYMMDD.
33+
public static final int FULL_DATE_LENGTH = 8;
34+
35+
//Length of a date formatted as YYMMDD.
36+
public static final int SHORT_DATE_LENGTH = 6;
37+
38+
//Length of a date formatted as YMMDD.
39+
public static final int SINGLE_DIGIT_YEAR_DATE_LENGTH = 5;
40+
41+
//Length of a date formatted as MMDD.
42+
public static final int NO_YEAR_DATE_LENGTH = 4;
43+
44+
//Length of a date formatted as MDD.
45+
public static final int SINGLE_DIGIT_MONTH_DATE_LENGTH = 3;
46+
3247
public static final DateTimeFormatter TIME_ZONE_FORMATTER_NO_COLON =
3348
new DateTimeFormatterBuilder()
3449
.appendOffset("+HHmm", "Z")
@@ -133,6 +148,30 @@ public class DateTimeFormatters {
133148
.toFormatter(Locale.ROOT)
134149
.withResolverStyle(ResolverStyle.STRICT);
135150

151+
// MDD
152+
public static final DateTimeFormatter DATE_FORMATTER_SINGLE_DIGIT_MONTH =
153+
new DateTimeFormatterBuilder()
154+
.parseDefaulting(YEAR, 2000)
155+
.appendPattern("Mdd")
156+
.toFormatter()
157+
.withResolverStyle(ResolverStyle.STRICT);
158+
159+
// MMDD
160+
public static final DateTimeFormatter DATE_FORMATTER_NO_YEAR =
161+
new DateTimeFormatterBuilder()
162+
.parseDefaulting(YEAR, 2000)
163+
.appendPattern("MMdd")
164+
.toFormatter()
165+
.withResolverStyle(ResolverStyle.STRICT);
166+
167+
// YMMDD
168+
public static final DateTimeFormatter DATE_FORMATTER_SINGLE_DIGIT_YEAR =
169+
new DateTimeFormatterBuilder()
170+
.appendValueReduced(YEAR, 1, 1, 2000)
171+
.appendPattern("MMdd")
172+
.toFormatter()
173+
.withResolverStyle(ResolverStyle.STRICT);
174+
136175
// YYMMDD
137176
public static final DateTimeFormatter DATE_FORMATTER_SHORT_YEAR =
138177
new DateTimeFormatterBuilder()

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

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,126 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
7+
package org.opensearch.sql.expression.datetime;
8+
9+
import static org.junit.jupiter.api.Assertions.assertEquals;
10+
import static org.junit.jupiter.api.Assertions.assertThrows;
11+
import static org.opensearch.sql.data.type.ExprCoreType.LONG;
12+
import static org.opensearch.sql.expression.datetime.DateTimeFunction.SECONDS_PER_DAY;
13+
14+
import java.time.DateTimeException;
15+
import java.time.Duration;
16+
import java.time.LocalDate;
17+
import java.time.LocalTime;
18+
import java.time.ZoneOffset;
19+
import java.util.stream.Stream;
20+
import org.junit.jupiter.api.Test;
21+
import org.junit.jupiter.api.extension.ExtendWith;
22+
import org.junit.jupiter.params.ParameterizedTest;
23+
import org.junit.jupiter.params.provider.Arguments;
24+
import org.junit.jupiter.params.provider.MethodSource;
25+
import org.mockito.Mock;
26+
import org.mockito.junit.jupiter.MockitoExtension;
27+
import org.opensearch.sql.data.model.ExprDateValue;
28+
import org.opensearch.sql.data.model.ExprDatetimeValue;
29+
import org.opensearch.sql.data.model.ExprIntervalValue;
30+
import org.opensearch.sql.data.model.ExprLongValue;
31+
import org.opensearch.sql.data.model.ExprNullValue;
32+
import org.opensearch.sql.data.model.ExprStringValue;
33+
import org.opensearch.sql.data.model.ExprTimeValue;
34+
import org.opensearch.sql.data.model.ExprTimestampValue;
35+
import org.opensearch.sql.data.model.ExprValue;
36+
import org.opensearch.sql.exception.SemanticCheckException;
37+
import org.opensearch.sql.expression.DSL;
38+
import org.opensearch.sql.expression.Expression;
39+
import org.opensearch.sql.expression.ExpressionTestBase;
40+
import org.opensearch.sql.expression.FunctionExpression;
41+
42+
43+
@ExtendWith(MockitoExtension.class)
44+
class ToSecondsTest extends ExpressionTestBase {
45+
46+
private static final long SECONDS_FROM_0001_01_01_TO_EPOCH_START = 62167219200L;
47+
48+
private static Stream<Arguments> getTestDataForToSeconds() {
49+
return Stream.of(
50+
Arguments.of(new ExprLongValue(101), new ExprLongValue(63113904000L)),
51+
Arguments.of(new ExprLongValue(1030), new ExprLongValue(63140083200L)),
52+
Arguments.of(new ExprLongValue(50101), new ExprLongValue(63271756800L)),
53+
Arguments.of(new ExprLongValue(950501), new ExprLongValue(62966505600L)),
54+
Arguments.of(new ExprLongValue(19950501), new ExprLongValue(62966505600L)),
55+
Arguments.of(new ExprLongValue(9950501), ExprNullValue.of()),
56+
Arguments.of(new ExprLongValue(-123L), ExprNullValue.of()),
57+
Arguments.of(new ExprLongValue(1), ExprNullValue.of()),
58+
Arguments.of(new ExprLongValue(919950501), ExprNullValue.of()),
59+
Arguments.of(new ExprStringValue("2009-11-29 00:00:00"), new ExprLongValue(63426672000L)),
60+
Arguments.of(new ExprStringValue("2009-11-29 13:43:32"), new ExprLongValue(63426721412L)),
61+
Arguments.of(new ExprDateValue("2009-11-29"), new ExprLongValue(63426672000L)),
62+
Arguments.of(new ExprDatetimeValue("2009-11-29 13:43:32"),
63+
new ExprLongValue(63426721412L)),
64+
Arguments.of(new ExprTimestampValue("2009-11-29 13:43:32"),
65+
new ExprLongValue(63426721412L))
66+
);
67+
}
68+
69+
@ParameterizedTest
70+
@MethodSource("getTestDataForToSeconds")
71+
public void testToSeconds(ExprValue arg, ExprValue expected) {
72+
FunctionExpression expr = DSL.to_seconds(DSL.literal(arg));
73+
assertEquals(LONG, expr.type());
74+
assertEquals(expected, eval(expr));
75+
}
76+
77+
@Test
78+
public void testToSecondsWithTimeType() {
79+
FunctionExpression expr = DSL.to_seconds(functionProperties,
80+
DSL.literal(new ExprTimeValue("10:11:12")));
81+
82+
long expected = SECONDS_FROM_0001_01_01_TO_EPOCH_START
83+
+ LocalDate.now(functionProperties.getQueryStartClock())
84+
.toEpochSecond(LocalTime.parse("10:11:12"), ZoneOffset.UTC);
85+
86+
assertEquals(expected, eval(expr).longValue());
87+
}
88+
89+
private static Stream<Arguments> getInvalidTestDataForToSeconds() {
90+
return Stream.of(
91+
Arguments.of(new ExprStringValue("asdfasdf")),
92+
Arguments.of(new ExprStringValue("2000-14-10")),
93+
Arguments.of(new ExprStringValue("2000-10-45")),
94+
Arguments.of(new ExprStringValue("2000-10-10 70:00:00")),
95+
Arguments.of(new ExprStringValue("2000-10-10 00:70:00")),
96+
Arguments.of(new ExprStringValue("2000-10-10 00:00:70"))
97+
);
98+
}
99+
100+
@ParameterizedTest
101+
@MethodSource("getInvalidTestDataForToSeconds")
102+
public void testToSecondsInvalidArg(ExprValue arg) {
103+
FunctionExpression expr = DSL.to_seconds(DSL.literal(arg));
104+
assertThrows(SemanticCheckException.class, () -> eval(expr));
105+
}
106+
107+
@Test
108+
public void testToSecondsWithDateAdd() {
109+
LocalDate date = LocalDate.of(2000, 1, 1);
110+
FunctionExpression dateExpr = DSL.to_seconds(DSL.literal(new ExprDateValue(date)));
111+
long addedSeconds = SECONDS_PER_DAY;
112+
long expected = eval(dateExpr).longValue() + addedSeconds;
113+
114+
FunctionExpression dateAddExpr = DSL.date_add(
115+
DSL.literal(new ExprDateValue(date)),
116+
DSL.literal(new ExprIntervalValue(Duration.ofSeconds(addedSeconds))));
117+
118+
long result = eval(DSL.to_seconds(DSL.literal(eval(dateAddExpr)))).longValue();
119+
120+
assertEquals(expected, result);
121+
}
122+
123+
private ExprValue eval(Expression expression) {
124+
return expression.valueOf();
125+
}
126+
}

‎docs/user/dql/functions.rst‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2459,6 +2459,28 @@ Example::
24592459
| 733687 |
24602460
+------------------------------+
24612461

2462+
TO_SECONDS
2463+
----------
2464+
2465+
Description
2466+
>>>>>>>>>>>
2467+
2468+
Usage: to_seconds(date) returns the number of seconds since the year 0 of the given value. Returns NULL if value is invalid.
2469+
An argument of a LONG type can be used. It must be formatted as YMMDD, YYMMDD, YYYMMDD or YYYYMMDD. Note that a LONG type argument cannot have leading 0s as it will be parsed using an octal numbering system.
2470+
2471+
Argument type: STRING/LONG/DATE/DATETIME/TIME/TIMESTAMP
2472+
2473+
Return type: LONG
2474+
2475+
Example::
2476+
2477+
os> SELECT TO_SECONDS(DATE '2008-10-07'), TO_SECONDS(950228)
2478+
fetched rows / total rows = 1/1
2479+
+---------------------------------+----------------------+
2480+
| TO_SECONDS(DATE '2008-10-07') | TO_SECONDS(950228) |
2481+
|---------------------------------+----------------------|
2482+
| 63390556800 | 62961148800 |
2483+
+---------------------------------+----------------------+
24622484

24632485
UNIX_TIMESTAMP
24642486
--------------

0 commit comments

Comments
 (0)