Skip to content

Commit 32a5124

Browse files
GabeFernandez310GabeFernandez310
authored
Add last_day Function To OpenSearch SQL Plugin
Added Testing And Implementation --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com>
1 parent 72547f4 commit 32a5124

9 files changed

Lines changed: 179 additions & 0 deletions

File tree

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -361,6 +361,11 @@ public static FunctionExpression hour_of_day(Expression... expressions) {
361361
return compile(FunctionProperties.None, BuiltinFunctionName.HOUR_OF_DAY, expressions);
362362
}
363363

364+
public static FunctionExpression last_day(FunctionProperties functionProperties,
365+
Expression... expressions) {
366+
return compile(functionProperties, BuiltinFunctionName.LAST_DAY, expressions);
367+
}
368+
364369
public static FunctionExpression microsecond(Expression... expressions) {
365370
return compile(FunctionProperties.None, BuiltinFunctionName.MICROSECOND, expressions);
366371
}

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

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -145,6 +145,7 @@ public void register(BuiltinFunctionRepository repository) {
145145
repository.register(get_format());
146146
repository.register(hour(BuiltinFunctionName.HOUR));
147147
repository.register(hour(BuiltinFunctionName.HOUR_OF_DAY));
148+
repository.register(last_day());
148149
repository.register(localtime());
149150
repository.register(localtimestamp());
150151
repository.register(makedate());
@@ -550,6 +551,18 @@ private DefaultFunctionResolver hour(BuiltinFunctionName name) {
550551
);
551552
}
552553

554+
private DefaultFunctionResolver last_day() {
555+
return define(BuiltinFunctionName.LAST_DAY.getName(),
556+
impl(nullMissingHandling(DateTimeFunction::exprLastDay), DATE, STRING),
557+
implWithProperties(nullMissingHandlingWithProperties((functionProperties, arg)
558+
-> DateTimeFunction.exprLastDayToday(
559+
functionProperties.getQueryStartClock())), DATE, TIME),
560+
impl(nullMissingHandling(DateTimeFunction::exprLastDay), DATE, DATE),
561+
impl(nullMissingHandling(DateTimeFunction::exprLastDay), DATE, DATETIME),
562+
impl(nullMissingHandling(DateTimeFunction::exprLastDay), DATE, TIMESTAMP)
563+
);
564+
}
565+
553566
private FunctionResolver makedate() {
554567
return define(BuiltinFunctionName.MAKEDATE.getName(),
555568
impl(nullMissingHandling(DateTimeFunction::exprMakeDate), DATE, DOUBLE, DOUBLE));
@@ -1240,6 +1253,39 @@ private ExprValue exprHour(ExprValue time) {
12401253
HOURS.between(LocalTime.MIN, time.timeValue()));
12411254
}
12421255

1256+
/**
1257+
* Helper function to retrieve the last day of a month based on a LocalDate argument.
1258+
*
1259+
* @param today a LocalDate.
1260+
* @return a LocalDate associated with the last day of the month for the given input.
1261+
*/
1262+
private LocalDate getLastDay(LocalDate today) {
1263+
return LocalDate.of(
1264+
today.getYear(),
1265+
today.getMonth(),
1266+
today.getMonth().length(today.isLeapYear()));
1267+
}
1268+
1269+
/**
1270+
* Returns a DATE for the last day of the month of a given argument.
1271+
*
1272+
* @param datetime A DATE/DATETIME/TIMESTAMP/STRING ExprValue.
1273+
* @return An DATE value corresponding to the last day of the month of the given argument.
1274+
*/
1275+
private ExprValue exprLastDay(ExprValue datetime) {
1276+
return new ExprDateValue(getLastDay(datetime.dateValue()));
1277+
}
1278+
1279+
/**
1280+
* Returns a DATE for the last day of the current month.
1281+
*
1282+
* @param clock The clock for the query start time from functionProperties.
1283+
* @return An DATE value corresponding to the last day of the month of the given argument.
1284+
*/
1285+
private ExprValue exprLastDayToday(Clock clock) {
1286+
return new ExprDateValue(getLastDay(formatNow(clock).toLocalDate()));
1287+
}
1288+
12431289
/**
12441290
* Following MySQL, function receives arguments of type double and rounds them before use.
12451291
* Furthermore:

‎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
@@ -80,6 +80,7 @@ public enum BuiltinFunctionName {
8080
GET_FORMAT(FunctionName.of("get_format")),
8181
HOUR(FunctionName.of("hour")),
8282
HOUR_OF_DAY(FunctionName.of("hour_of_day")),
83+
LAST_DAY(FunctionName.of("last_day")),
8384
MAKEDATE(FunctionName.of("makedate")),
8485
MAKETIME(FunctionName.of("maketime")),
8586
MICROSECOND(FunctionName.of("microsecond")),

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

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -843,6 +843,80 @@ public void hourOfDayInvalidArguments() {
843843

844844
}
845845

846+
private void checkForExpectedDay(
847+
FunctionExpression functionExpression,
848+
String expectedDay,
849+
String testExpr) {
850+
assertEquals(DATE, functionExpression.type());
851+
assertEquals(new ExprDateValue(expectedDay), eval(functionExpression));
852+
assertEquals(testExpr, functionExpression.toString());
853+
}
854+
855+
private static Stream<Arguments> getTestDataForLastDay() {
856+
return Stream.of(
857+
Arguments.of(new ExprDateValue("2017-01-20"), "2017-01-31", "last_day(DATE '2017-01-20')"),
858+
//Leap year
859+
Arguments.of(new ExprDateValue("2020-02-20"), "2020-02-29", "last_day(DATE '2020-02-20')"),
860+
//Non leap year
861+
Arguments.of(new ExprDateValue("2017-02-20"), "2017-02-28", "last_day(DATE '2017-02-20')"),
862+
Arguments.of(new ExprDateValue("2017-03-20"), "2017-03-31", "last_day(DATE '2017-03-20')"),
863+
Arguments.of(new ExprDateValue("2017-04-20"), "2017-04-30", "last_day(DATE '2017-04-20')"),
864+
Arguments.of(new ExprDateValue("2017-05-20"), "2017-05-31", "last_day(DATE '2017-05-20')"),
865+
Arguments.of(new ExprDateValue("2017-06-20"), "2017-06-30", "last_day(DATE '2017-06-20')"),
866+
Arguments.of(new ExprDateValue("2017-07-20"), "2017-07-31", "last_day(DATE '2017-07-20')"),
867+
Arguments.of(new ExprDateValue("2017-08-20"), "2017-08-31", "last_day(DATE '2017-08-20')"),
868+
Arguments.of(new ExprDateValue("2017-09-20"), "2017-09-30", "last_day(DATE '2017-09-20')"),
869+
Arguments.of(new ExprDateValue("2017-10-20"), "2017-10-31", "last_day(DATE '2017-10-20')"),
870+
Arguments.of(new ExprDateValue("2017-11-20"), "2017-11-30", "last_day(DATE '2017-11-20')"),
871+
Arguments.of(new ExprDateValue("2017-12-20"), "2017-12-31", "last_day(DATE '2017-12-20')")
872+
);
873+
}
874+
875+
@ParameterizedTest(name = "{2}")
876+
@MethodSource("getTestDataForLastDay")
877+
public void testLastDay(ExprValue testedDateTime, String expectedResult, String expectedQuery) {
878+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
879+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
880+
881+
checkForExpectedDay(
882+
DSL.last_day(functionProperties, DSL.literal(testedDateTime)),
883+
expectedResult,
884+
expectedQuery
885+
);
886+
}
887+
888+
@Test
889+
public void testLastDayWithTimeType() {
890+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
891+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
892+
893+
FunctionExpression expression = DSL.last_day(
894+
functionProperties, DSL.literal(new ExprTimeValue("12:23:34")));
895+
896+
LocalDate expected = LocalDate.now(functionProperties.getQueryStartClock());
897+
LocalDate result = eval(expression).dateValue();
898+
899+
900+
assertAll(
901+
() -> assertEquals((expected.lengthOfMonth()), result.getDayOfMonth()),
902+
() -> assertEquals("last_day(TIME '12:23:34')", expression.toString())
903+
);
904+
}
905+
906+
private void lastDay(String date) {
907+
FunctionExpression expression = DSL.day_of_week(
908+
functionProperties, DSL.literal(new ExprDateValue(date)));
909+
eval(expression);
910+
}
911+
912+
@Test
913+
public void testLastDayInvalidArgument() {
914+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
915+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
916+
917+
assertThrows(SemanticCheckException.class, () -> lastDay("asdfasdf"));
918+
}
919+
846920
@Test
847921
public void microsecond() {
848922
when(nullRef.type()).thenReturn(TIME);

‎docs/user/dql/functions.rst‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2690,6 +2690,24 @@ Example::
26902690
| hello,world |
26912691
+------------------------------------+
26922692

2693+
LAST_DAY
2694+
--------
2695+
2696+
Usage: Returns the last day of the month as a DATE for a valid argument.
2697+
2698+
Argument type: DATE/DATETIME/STRING/TIMESTAMP/TIME
2699+
2700+
Return type: DATE
2701+
2702+
Example::
2703+
2704+
os> SELECT last_day('2023-02-06');
2705+
fetched rows / total rows = 1/1
2706+
+--------------------------+
2707+
| last_day('2023-02-06') |
2708+
|--------------------------|
2709+
| 2023-02-28 |
2710+
+--------------------------+
26932711

26942712
LEFT
26952713
----

‎integ-test/src/test/java/org/opensearch/sql/sql/DateTimeFunctionIT.java‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -513,6 +513,33 @@ public void testHourFunctionAliasesReturnTheSameResults() throws IOException {
513513
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
514514
}
515515

516+
@Test
517+
public void testLastDay() throws IOException {
518+
JSONObject result = executeQuery(
519+
String.format("SELECT last_day(cast(date0 as date)) FROM %s LIMIT 3",
520+
TEST_INDEX_CALCS));
521+
verifyDataRows(result,
522+
rows("2004-04-30"),
523+
rows("1972-07-31"),
524+
rows("1975-11-30"));
525+
526+
result = executeQuery(
527+
String.format("SELECT last_day(datetime(cast(date0 AS string))) FROM %s LIMIT 3",
528+
TEST_INDEX_CALCS));
529+
verifyDataRows(result,
530+
rows("2004-04-30"),
531+
rows("1972-07-31"),
532+
rows("1975-11-30"));
533+
534+
result = executeQuery(
535+
String.format("SELECT last_day(cast(date0 AS timestamp)) FROM %s LIMIT 3",
536+
TEST_INDEX_CALCS));
537+
verifyDataRows(result,
538+
rows("2004-04-30"),
539+
rows("1972-07-31"),
540+
rows("1975-11-30"));
541+
}
542+
516543
@Test
517544
public void testMicrosecond() throws IOException {
518545
JSONObject result = executeQuery("select microsecond(timestamp('2020-09-16 17:30:00.123456'))");

‎sql/src/main/antlr/OpenSearchSQLLexer.g4‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,6 +217,7 @@ GET_FORMAT: 'GET_FORMAT';
217217
IF: 'IF';
218218
IFNULL: 'IFNULL';
219219
ISNULL: 'ISNULL';
220+
LAST_DAY: 'LAST_DAY';
220221
LENGTH: 'LENGTH';
221222
LN: 'LN';
222223
LOCALTIME: 'LOCALTIME';

‎sql/src/main/antlr/OpenSearchSQLParser.g4‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -452,6 +452,7 @@ dateTimeFunctionName
452452
| FROM_UNIXTIME
453453
| HOUR
454454
| HOUR_OF_DAY
455+
| LAST_DAY
455456
| MAKEDATE
456457
| MAKETIME
457458
| MICROSECOND

‎sql/src/test/java/org/opensearch/sql/sql/antlr/SQLSyntaxParserTest.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -521,6 +521,12 @@ public void can_parse_minute_of_day_function() {
521521
assertNotNull(parser.parse("SELECT minute_of_day('2022-12-14 12:23:34');"));;
522522
}
523523

524+
@Test
525+
public void can_parse_last_day_function() {
526+
assertNotNull(parser.parse("SELECT last_day(\"2017-06-20\")"));
527+
assertNotNull(parser.parse("SELECT last_day('2004-01-01 01:01:01')"));
528+
}
529+
524530
@Test
525531
public void can_parse_wildcard_query_relevance_function() {
526532
assertNotNull(

0 commit comments

Comments
 (0)