Skip to content

Commit 2c46797

Browse files
GabeFernandez310GabeFernandez310
authored
Add Minute_Of_Day Function To SQL Plugin (opensearch-project#1207)
Signed-off-by: GabeFernandez310 <gabrielf@bitquilltech.com> Signed-off-by: GabeFernandez310 <gabrielf@bitquilltech.com>
1 parent 7714819 commit 2c46797

8 files changed

Lines changed: 159 additions & 0 deletions

File tree

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -354,6 +354,10 @@ public static FunctionExpression minute(Expression... expressions) {
354354
return compile(FunctionProperties.None, BuiltinFunctionName.MINUTE, expressions);
355355
}
356356

357+
public static FunctionExpression minute_of_day(Expression... expressions) {
358+
return compile(FunctionProperties.None, BuiltinFunctionName.MINUTE_OF_DAY, expressions);
359+
}
360+
357361
public static FunctionExpression month(Expression... expressions) {
358362
return compile(FunctionProperties.None, BuiltinFunctionName.MONTH, expressions);
359363
}

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

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66

77
package org.opensearch.sql.expression.datetime;
88

9+
import static java.time.temporal.ChronoUnit.MINUTES;
910
import static java.time.temporal.ChronoUnit.MONTHS;
1011
import static org.opensearch.sql.data.type.ExprCoreType.DATE;
1112
import static org.opensearch.sql.data.type.ExprCoreType.DATETIME;
@@ -114,6 +115,7 @@ public void register(BuiltinFunctionRepository repository) {
114115
repository.register(maketime());
115116
repository.register(microsecond());
116117
repository.register(minute());
118+
repository.register(minute_of_day());
117119
repository.register(month(BuiltinFunctionName.MONTH));
118120
repository.register(month(BuiltinFunctionName.MONTH_OF_YEAR));
119121
repository.register(monthName());
@@ -436,6 +438,19 @@ private DefaultFunctionResolver minute() {
436438
);
437439
}
438440

441+
/**
442+
* MINUTE(STRING/TIME/DATETIME/TIMESTAMP). return the minute value for time.
443+
*/
444+
private DefaultFunctionResolver minute_of_day() {
445+
return define(BuiltinFunctionName.MINUTE_OF_DAY.getName(),
446+
impl(nullMissingHandling(DateTimeFunction::exprMinuteOfDay), INTEGER, STRING),
447+
impl(nullMissingHandling(DateTimeFunction::exprMinuteOfDay), INTEGER, TIME),
448+
impl(nullMissingHandling(DateTimeFunction::exprMinuteOfDay), INTEGER, DATE),
449+
impl(nullMissingHandling(DateTimeFunction::exprMinuteOfDay), INTEGER, DATETIME),
450+
impl(nullMissingHandling(DateTimeFunction::exprMinuteOfDay), INTEGER, TIMESTAMP)
451+
);
452+
}
453+
439454
/**
440455
* MONTH(STRING/DATE/DATETIME/TIMESTAMP). return the month for date (1-12).
441456
*/
@@ -933,6 +948,17 @@ private ExprValue exprMinute(ExprValue time) {
933948
return new ExprIntegerValue(time.timeValue().getMinute());
934949
}
935950

951+
/**
952+
* Minute_of_day implementation for ExprValue.
953+
*
954+
* @param time ExprValue of Time/String type.
955+
* @return ExprValue.
956+
*/
957+
private ExprValue exprMinuteOfDay(ExprValue time) {
958+
return new ExprIntegerValue(
959+
MINUTES.between(LocalTime.MIN, time.timeValue()));
960+
}
961+
936962
/**
937963
* Month for date implementation for ExprValue.
938964
*

‎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
@@ -77,6 +77,7 @@ public enum BuiltinFunctionName {
7777
MAKETIME(FunctionName.of("maketime")),
7878
MICROSECOND(FunctionName.of("microsecond")),
7979
MINUTE(FunctionName.of("minute")),
80+
MINUTE_OF_DAY(FunctionName.of("minute_of_day")),
8081
MONTH(FunctionName.of("month")),
8182
MONTH_OF_YEAR(FunctionName.of("month_of_year")),
8283
MONTHNAME(FunctionName.of("monthname")),

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

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -614,6 +614,37 @@ public void hour() {
614614
assertEquals("hour(\"2020-08-17 01:02:03\")", expression.toString());
615615
}
616616

617+
private void testInvalidMinuteOfDay(String date) {
618+
FunctionExpression expression = DSL.minute_of_day(DSL.literal(new ExprDateValue(date)));
619+
eval(expression);
620+
}
621+
622+
@Test
623+
public void invalidMinuteOfDay() {
624+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
625+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
626+
627+
assertThrows(SemanticCheckException.class,
628+
() -> testInvalidMinuteOfDay("2022-12-14 12:23:3400"));
629+
assertThrows(SemanticCheckException.class,
630+
() -> testInvalidMinuteOfDay("2022-12-14 12:2300:34"));
631+
assertThrows(SemanticCheckException.class,
632+
() -> testInvalidMinuteOfDay("2022-12-14 1200:23:34"));
633+
assertThrows(SemanticCheckException.class,
634+
() -> testInvalidMinuteOfDay("2022-12-1400 12:23:34"));
635+
assertThrows(SemanticCheckException.class,
636+
() -> testInvalidMinuteOfDay("2022-1200-14 12:23:34"));
637+
assertThrows(SemanticCheckException.class,
638+
() -> testInvalidMinuteOfDay("12:23:3400"));
639+
assertThrows(SemanticCheckException.class,
640+
() -> testInvalidMinuteOfDay("12:2300:34"));
641+
assertThrows(SemanticCheckException.class,
642+
() -> testInvalidMinuteOfDay("1200:23:34"));
643+
assertThrows(SemanticCheckException.class,
644+
() -> testInvalidMinuteOfDay("asdfasdfasdf"));
645+
646+
}
647+
617648
@Test
618649
public void microsecond() {
619650
when(nullRef.type()).thenReturn(TIME);
@@ -691,6 +722,48 @@ public void minute() {
691722
assertEquals("minute(\"2020-08-17 01:02:03\")", expression.toString());
692723
}
693724

725+
private void testMinuteOfDay(String date, int value) {
726+
FunctionExpression expression = DSL.minute_of_day(DSL.literal(new ExprTimeValue(date)));
727+
assertEquals(INTEGER, expression.type());
728+
assertEquals(integerValue(value), eval(expression));
729+
}
730+
731+
@Test
732+
public void minuteOfDay() {
733+
when(nullRef.type()).thenReturn(TIME);
734+
when(missingRef.type()).thenReturn(TIME);
735+
assertEquals(nullValue(), eval(DSL.minute_of_day(nullRef)));
736+
assertEquals(missingValue(), eval(DSL.minute_of_day(missingRef)));
737+
738+
FunctionExpression expression = DSL.minute_of_day(DSL.literal(new ExprTimeValue("01:02:03")));
739+
assertEquals(INTEGER, expression.type());
740+
assertEquals(integerValue(62), eval(expression));
741+
assertEquals("minute_of_day(TIME '01:02:03')", expression.toString());
742+
743+
expression = DSL.minute_of_day(DSL.literal("01:02:03"));
744+
assertEquals(INTEGER, expression.type());
745+
assertEquals(integerValue(62), eval(expression));
746+
assertEquals("minute_of_day(\"01:02:03\")", expression.toString());
747+
748+
expression = DSL.minute_of_day(DSL.literal(new ExprTimestampValue("2020-08-17 01:02:03")));
749+
assertEquals(INTEGER, expression.type());
750+
assertEquals(integerValue(62), expression.valueOf(env));
751+
assertEquals("minute_of_day(TIMESTAMP '2020-08-17 01:02:03')", expression.toString());
752+
753+
expression = DSL.minute_of_day(DSL.literal(new ExprDatetimeValue("2020-08-17 01:02:03")));
754+
assertEquals(INTEGER, expression.type());
755+
assertEquals(integerValue(62), expression.valueOf(env));
756+
assertEquals("minute_of_day(DATETIME '2020-08-17 01:02:03')", expression.toString());
757+
758+
expression = DSL.minute_of_day(DSL.literal("2020-08-17 01:02:03"));
759+
assertEquals(INTEGER, expression.type());
760+
assertEquals(integerValue(62), expression.valueOf(env));
761+
assertEquals("minute_of_day(\"2020-08-17 01:02:03\")", expression.toString());
762+
763+
testMinuteOfDay("2020-08-17 23:59:59", 1439);
764+
testMinuteOfDay("2020-08-17 00:00:01", 0);
765+
}
766+
694767
@Test
695768
public void month() {
696769
when(nullRef.type()).thenReturn(DATE);

‎docs/user/dql/functions.rst‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1762,6 +1762,28 @@ Example::
17621762
| 2 |
17631763
+-----------------------------+
17641764

1765+
MINUTE_OF_DAY
1766+
------
1767+
1768+
Description
1769+
>>>>>>>>>>>
1770+
1771+
Usage: minute_of_day(time) returns the minute value for time within a 24 hour day, in the range 0 to 1439.
1772+
1773+
Argument type: STRING/TIME/DATETIME/TIMESTAMP
1774+
1775+
Return type: INTEGER
1776+
1777+
Example::
1778+
1779+
os> SELECT MINUTE_OF_DAY((TIME '01:02:03'))
1780+
fetched rows / total rows = 1/1
1781+
+------------------------------------+
1782+
| MINUTE_OF_DAY((TIME '01:02:03')) |
1783+
|------------------------------------|
1784+
| 62 |
1785+
+------------------------------------+
1786+
17651787

17661788
MONTH
17671789
-----

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

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -366,6 +366,30 @@ public void testMinute() throws IOException {
366366
verifyDataRows(result, rows(30));
367367
}
368368

369+
370+
@Test
371+
public void testMinuteOfDay() throws IOException {
372+
JSONObject result = executeQuery("select minute_of_day(timestamp('2020-09-16 17:30:00'))");
373+
verifySchema(result, schema("minute_of_day(timestamp('2020-09-16 17:30:00'))", null, "integer"));
374+
verifyDataRows(result, rows(1050));
375+
376+
result = executeQuery("select minute_of_day(datetime('2020-09-16 17:30:00'))");
377+
verifySchema(result, schema("minute_of_day(datetime('2020-09-16 17:30:00'))", null, "integer"));
378+
verifyDataRows(result, rows(1050));
379+
380+
result = executeQuery("select minute_of_day(time('17:30:00'))");
381+
verifySchema(result, schema("minute_of_day(time('17:30:00'))", null, "integer"));
382+
verifyDataRows(result, rows(1050));
383+
384+
result = executeQuery("select minute_of_day('2020-09-16 17:30:00')");
385+
verifySchema(result, schema("minute_of_day('2020-09-16 17:30:00')", null, "integer"));
386+
verifyDataRows(result, rows(1050));
387+
388+
result = executeQuery("select minute_of_day('17:30:00')");
389+
verifySchema(result, schema("minute_of_day('17:30:00')", null, "integer"));
390+
verifyDataRows(result, rows(1050));
391+
}
392+
369393
@Test
370394
public void testMonth() throws IOException {
371395
JSONObject result = executeQuery("select month(date('2020-09-16'))");

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -433,6 +433,7 @@ dateTimeFunctionName
433433
| MAKETIME
434434
| MICROSECOND
435435
| MINUTE
436+
| MINUTE_OF_DAY
436437
| MONTH
437438
| MONTHNAME
438439
| NOW

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -451,6 +451,14 @@ public void can_parse_match_phrase_relevance_function() {
451451
assertNotNull(parser.parse("SELECT * FROM test WHERE match_phrase(column, 100500)"));
452452
}
453453

454+
@Test
455+
public void can_parse_minute_of_day_function() {
456+
assertNotNull(parser.parse("SELECT minute_of_day(\"12:23:34\");"));
457+
assertNotNull(parser.parse("SELECT minute_of_day('12:23:34');"));;
458+
assertNotNull(parser.parse("SELECT minute_of_day(\"2022-12-14 12:23:34\");"));;
459+
assertNotNull(parser.parse("SELECT minute_of_day('2022-12-14 12:23:34');"));;
460+
}
461+
454462
@Test
455463
public void can_parse_wildcard_query_relevance_function() {
456464
assertNotNull(

0 commit comments

Comments
 (0)