Skip to content

Commit 3839763

Browse files
GabeFernandez310github-actions[bot]
GabeFernandez310
authored and
github-actions[bot]
committed
Add last_day Function To OpenSearch SQL Plugin (#1344)
Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> (cherry picked from commit d9114f5)
1 parent 46026af commit 3839763

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
@@ -357,6 +357,11 @@ public static FunctionExpression hour_of_day(Expression... expressions) {
357357
return compile(FunctionProperties.None, BuiltinFunctionName.HOUR_OF_DAY, expressions);
358358
}
359359

360+
public static FunctionExpression last_day(FunctionProperties functionProperties,
361+
Expression... expressions) {
362+
return compile(functionProperties, BuiltinFunctionName.LAST_DAY, expressions);
363+
}
364+
360365
public static FunctionExpression microsecond(Expression... expressions) {
361366
return compile(FunctionProperties.None, BuiltinFunctionName.MICROSECOND, expressions);
362367
}

‎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());
@@ -551,6 +552,18 @@ private DefaultFunctionResolver hour(BuiltinFunctionName name) {
551552
);
552553
}
553554

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

1288+
/**
1289+
* Helper function to retrieve the last day of a month based on a LocalDate argument.
1290+
*
1291+
* @param today a LocalDate.
1292+
* @return a LocalDate associated with the last day of the month for the given input.
1293+
*/
1294+
private LocalDate getLastDay(LocalDate today) {
1295+
return LocalDate.of(
1296+
today.getYear(),
1297+
today.getMonth(),
1298+
today.getMonth().length(today.isLeapYear()));
1299+
}
1300+
1301+
/**
1302+
* Returns a DATE for the last day of the month of a given argument.
1303+
*
1304+
* @param datetime A DATE/DATETIME/TIMESTAMP/STRING ExprValue.
1305+
* @return An DATE value corresponding to the last day of the month of the given argument.
1306+
*/
1307+
private ExprValue exprLastDay(ExprValue datetime) {
1308+
return new ExprDateValue(getLastDay(datetime.dateValue()));
1309+
}
1310+
1311+
/**
1312+
* Returns a DATE for the last day of the current month.
1313+
*
1314+
* @param clock The clock for the query start time from functionProperties.
1315+
* @return An DATE value corresponding to the last day of the month of the given argument.
1316+
*/
1317+
private ExprValue exprLastDayToday(Clock clock) {
1318+
return new ExprDateValue(getLastDay(formatNow(clock).toLocalDate()));
1319+
}
1320+
12751321
/**
12761322
* Following MySQL, function receives arguments of type double and rounds them before use.
12771323
* 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
@@ -846,6 +846,80 @@ public void hourOfDayInvalidArguments() {
846846

847847
}
848848

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

‎docs/user/dql/functions.rst‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2734,6 +2734,24 @@ Example::
27342734
| hello,world |
27352735
+------------------------------------+
27362736

2737+
LAST_DAY
2738+
--------
2739+
2740+
Usage: Returns the last day of the month as a DATE for a valid argument.
2741+
2742+
Argument type: DATE/DATETIME/STRING/TIMESTAMP/TIME
2743+
2744+
Return type: DATE
2745+
2746+
Example::
2747+
2748+
os> SELECT last_day('2023-02-06');
2749+
fetched rows / total rows = 1/1
2750+
+--------------------------+
2751+
| last_day('2023-02-06') |
2752+
|--------------------------|
2753+
| 2023-02-28 |
2754+
+--------------------------+
27372755

27382756
LEFT
27392757
----

‎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
@@ -457,6 +457,7 @@ dateTimeFunctionName
457457
| FROM_UNIXTIME
458458
| HOUR
459459
| HOUR_OF_DAY
460+
| LAST_DAY
460461
| MAKEDATE
461462
| MAKETIME
462463
| 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
@@ -520,6 +520,12 @@ public void can_parse_minute_of_day_function() {
520520
assertNotNull(parser.parse("SELECT minute_of_day('2022-12-14 12:23:34');"));;
521521
}
522522

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

0 commit comments

Comments
 (0)