opensearch-project · penghuo · Jan 9, 2023 · Jan 6, 2023
@@ -378,6 +378,10 @@ public static FunctionExpression second(Expression... expressions) {
     return compile(FunctionProperties.None, BuiltinFunctionName.SECOND, expressions);
   }
 
+  public static FunctionExpression second_of_minute(Expression... expressions) {
+    return compile(FunctionProperties.None, BuiltinFunctionName.SECOND_OF_MINUTE, expressions);
+  }
+
   public static FunctionExpression subdate(Expression... expressions) {
     return compile(FunctionProperties.None, BuiltinFunctionName.SUBDATE, expressions);
   }

@@ -9,6 +9,7 @@
 import static java.time.temporal.ChronoUnit.DAYS;
 import static java.time.temporal.ChronoUnit.MINUTES;
 import static java.time.temporal.ChronoUnit.MONTHS;
+import static java.time.temporal.ChronoUnit.SECONDS;
 import static org.opensearch.sql.data.type.ExprCoreType.DATE;
 import static org.opensearch.sql.data.type.ExprCoreType.DATETIME;
 import static org.opensearch.sql.data.type.ExprCoreType.DOUBLE;
@@ -128,7 +129,8 @@ public void register(BuiltinFunctionRepository repository) {
     repository.register(period_add());
     repository.register(period_diff());
     repository.register(quarter());
-    repository.register(second());
+    repository.register(second(BuiltinFunctionName.SECOND));
+    repository.register(second(BuiltinFunctionName.SECOND_OF_MINUTE));
     repository.register(subdate());
     repository.register(sysdate());
     repository.register(time());
@@ -557,10 +559,11 @@ private DefaultFunctionResolver quarter() {
   /**
    * SECOND(STRING/TIME/DATETIME/TIMESTAMP). return the second value for time.
    */
-  private DefaultFunctionResolver second() {
-    return define(BuiltinFunctionName.SECOND.getName(),
+  private DefaultFunctionResolver second(BuiltinFunctionName name) {
+    return define(name.getName(),
         impl(nullMissingHandling(DateTimeFunction::exprSecond), INTEGER, STRING),
         impl(nullMissingHandling(DateTimeFunction::exprSecond), INTEGER, TIME),
+        impl(nullMissingHandling(DateTimeFunction::exprSecond), INTEGER, DATE),
         impl(nullMissingHandling(DateTimeFunction::exprSecond), INTEGER, DATETIME),
         impl(nullMissingHandling(DateTimeFunction::exprSecond), INTEGER, TIMESTAMP)
     );
@@ -1131,7 +1134,8 @@ private ExprValue exprQuarter(ExprValue date) {
    * @return ExprValue.
    */
   private ExprValue exprSecond(ExprValue time) {
-    return new ExprIntegerValue(time.timeValue().getSecond());
+    return new ExprIntegerValue(
+        (SECONDS.between(LocalTime.MIN, time.timeValue()) % 60));
   }
 
   /**

@@ -86,6 +86,7 @@ public enum BuiltinFunctionName {
   PERIOD_DIFF(FunctionName.of("period_diff")),
   QUARTER(FunctionName.of("quarter")),
   SECOND(FunctionName.of("second")),
+  SECOND_OF_MINUTE(FunctionName.of("second_of_minute")),
   SUBDATE(FunctionName.of("subdate")),
   TIME(FunctionName.of("time")),
   TIMEDIFF(FunctionName.of("timediff")),

@@ -28,10 +28,14 @@
 import com.google.common.collect.ImmutableList;
 import java.time.LocalDate;
 import java.util.List;
+import java.util.stream.Stream;
 import lombok.AllArgsConstructor;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.ExtendWith;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
 import org.mockito.Mock;
 import org.mockito.junit.jupiter.MockitoExtension;
 import org.opensearch.sql.data.model.ExprDateValue;
@@ -46,6 +50,7 @@
 import org.opensearch.sql.expression.Expression;
 import org.opensearch.sql.expression.ExpressionTestBase;
 import org.opensearch.sql.expression.FunctionExpression;
+import org.opensearch.sql.expression.LiteralExpression;
 import org.opensearch.sql.expression.env.Environment;
 
 @ExtendWith(MockitoExtension.class)
@@ -904,6 +909,77 @@ public void second() {
     assertEquals("second(DATETIME '2020-08-17 01:02:03')", expression.toString());
   }
 
+  private void secondOfMinuteQuery(FunctionExpression dateExpression, int second, String testExpr) {
+    assertEquals(INTEGER, dateExpression.type());
+    assertEquals(integerValue(second), eval(dateExpression));
+    assertEquals(testExpr, dateExpression.toString());
+  }
+
+  private static Stream<Arguments> getTestDataForSecondOfMinute() {
+    return Stream.of(
+        Arguments.of(
+            DSL.literal(new ExprTimeValue("01:02:03")),
+            3,
+            "second_of_minute(TIME '01:02:03')"),
+        Arguments.of(
+            DSL.literal("01:02:03"),
+            3,
+            "second_of_minute(\"01:02:03\")"),
+        Arguments.of(
+            DSL.literal("2020-08-17 01:02:03"),
+            3,
+            "second_of_minute(\"2020-08-17 01:02:03\")"),
+        Arguments.of(
+
+            DSL.literal(new ExprTimestampValue("2020-08-17 01:02:03")),
+            3,
+            "second_of_minute(TIMESTAMP '2020-08-17 01:02:03')"),
+        Arguments.of(
+
+            DSL.literal(new ExprDatetimeValue("2020-08-17 01:02:03")),
+            3,
+            "second_of_minute(DATETIME '2020-08-17 01:02:03')")
+    );
+  }
+
+  @ParameterizedTest(name = "{2}")
+  @MethodSource("getTestDataForSecondOfMinute")
+  public void secondOfMinute(LiteralExpression arg, int expectedResult, String expectedString) {
+    lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
+    lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
+
+    secondOfMinuteQuery(DSL.second_of_minute(arg), expectedResult, expectedString);
+  }
+
+  private void invalidSecondOfMinuteQuery(String time) {
+    FunctionExpression expression = DSL.second_of_minute(DSL.literal(new ExprTimeValue(time)));
+    eval(expression);
+  }
+
+  @Test
+  public void secondOfMinuteInvalidArguments() {
+    when(nullRef.type()).thenReturn(TIME);
+    when(missingRef.type()).thenReturn(TIME);
+
+    assertAll(
+        () -> assertEquals(nullValue(), eval(DSL.second_of_minute(nullRef))),
+        () -> assertEquals(missingValue(), eval(DSL.second_of_minute(missingRef))),
+        //Invalid Seconds
+        () -> assertThrows(SemanticCheckException.class,
+            () -> invalidSecondOfMinuteQuery("12:23:61")),
+        //Invalid Minutes
+        () -> assertThrows(SemanticCheckException.class,
+            () -> invalidSecondOfMinuteQuery("12:61:34")),
+        //Invalid Hours
+        () -> assertThrows(SemanticCheckException.class,
+            () -> invalidSecondOfMinuteQuery("25:23:34")),
+        //incorrect format
+        () -> assertThrows(SemanticCheckException.class,
+            () -> invalidSecondOfMinuteQuery("asdfasdf"))
+    );
+  }
+
+
   @Test
   public void subdate() {
     FunctionExpression expr = DSL.subdate(DSL.date(DSL.literal("2020-08-26")), DSL.literal(7));

@@ -1961,6 +1961,7 @@ Description
 >>>>>>>>>>>
 
 Usage: second(time) returns the second for time, in the range 0 to 59.
+The function `second_of_minute`_ is provided as an alias
 
 Argument type: STRING/TIME/DATETIME/TIMESTAMP
 
@@ -1976,6 +1977,14 @@ Example::
     | 3                           |
     +-----------------------------+
 
+    os> SELECT SECOND_OF_MINUTE(time('01:02:03'))
+    fetched rows / total rows = 1/1
+    +--------------------------------------+
+    | SECOND_OF_MINUTE(time('01:02:03'))   |
+    |--------------------------------------|
+    | 3                                    |
+    +--------------------------------------+
+
 
 SUBDATE
 -------

@@ -493,6 +493,51 @@ public void testSecond() throws IOException {
     verifyDataRows(result, rows(0));
   }
 
+  @Test
+  public void testSecondOfMinute() throws IOException {
+    JSONObject result = executeQuery("select second_of_minute(timestamp('2020-09-16 17:30:00'))");
+    verifySchema(result, schema("second_of_minute(timestamp('2020-09-16 17:30:00'))", null, "integer"));
+    verifyDataRows(result, rows(0));
+
+    result = executeQuery("select second_of_minute(time('17:30:00'))");
+    verifySchema(result, schema("second_of_minute(time('17:30:00'))", null, "integer"));
+    verifyDataRows(result, rows(0));
+
+    result = executeQuery("select second_of_minute('2020-09-16 17:30:00')");
+    verifySchema(result, schema("second_of_minute('2020-09-16 17:30:00')", null, "integer"));
+    verifyDataRows(result, rows(0));
+
+    result = executeQuery("select second_of_minute('17:30:00')");
+    verifySchema(result, schema("second_of_minute('17:30:00')", null, "integer"));
+    verifyDataRows(result, rows(0));
+  }
+
+  @Test
+  public void testSecondFunctionAliasesReturnTheSameResults() throws IOException {
+    JSONObject result1 = executeQuery("SELECT second('2022-11-22 12:23:34')");
+    JSONObject result2 = executeQuery("SELECT second_of_minute('2022-11-22 12:23:34')");
+    verifyDataRows(result1, rows(34));
+    result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
+
+    result1 = executeQuery(String.format(
+        "SELECT second(datetime(CAST(time0 AS STRING))) FROM %s", TEST_INDEX_CALCS));
+    result2 = executeQuery(String.format(
+        "SELECT second_of_minute(datetime(CAST(time0 AS STRING))) FROM %s", TEST_INDEX_CALCS));
+    result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
+
+    result1 = executeQuery(String.format(
+        "SELECT second(CAST(time0 AS STRING)) FROM %s", TEST_INDEX_CALCS));
+    result2 = executeQuery(String.format(
+        "SELECT second_of_minute(CAST(time0 AS STRING)) FROM %s", TEST_INDEX_CALCS));
+    result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
+
+    result1 = executeQuery(String.format(
+        "SELECT second(CAST(datetime0 AS timestamp)) FROM %s", TEST_INDEX_CALCS));
+    result2 = executeQuery(String.format(
+        "SELECT second_of_minute(CAST(datetime0 AS timestamp)) FROM %s", TEST_INDEX_CALCS));
+    result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
+  }
+
   @Test
   public void testSubDate() throws IOException {
     JSONObject result =

@@ -442,6 +442,7 @@ dateTimeFunctionName
     | PERIOD_DIFF
     | QUARTER
     | SECOND
+    | SECOND_OF_MINUTE
     | SUBDATE
     | SYSDATE
     | TIME

@@ -268,6 +268,14 @@ public void can_parse_multi_match_relevance_function() {
             + "operator='AND', tie_breaker=0.3, type = \"most_fields\", fuzziness = \"AUTO\")"));
   }
 
+  @Test
+  public void can_parse_second_functions() {
+    assertNotNull(parser.parse("SELECT second('12:23:34')"));
+    assertNotNull(parser.parse("SELECT second_of_minute('2022-11-18')"));
+    assertNotNull(parser.parse("SELECT second('2022-11-18 12:23:34')"));
+    assertNotNull(parser.parse("SELECT second_of_minute('2022-11-18 12:23:34')"));
+  }
+
   @Test
   public void can_parse_simple_query_string_relevance_function() {
     assertNotNull(parser.parse(