Skip to content

Commit 87018c6

Browse files
GabeFernandez310GabeFernandez310
authored
Add EXTRACT Function To OpenSearch SQL Plugin (#1421)
* Add `EXTRACT` Function To OpenSearch SQL Plugin * Added Basic Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Lexer And Parser Language Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Implementation And Fixed Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Documentation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Modified Implementation And Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Checkstyle Issues Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Javadoc comments and Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Changed Implementation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Changed A Test Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Integration Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Refactored Extract Tests Into New File Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Refactored Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Renamed Rule In Parser Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed AstExpressionBuilder Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Removed Unneeded Code And Added Parser Test Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com>
1 parent 03e4f97 commit 87018c6

11 files changed

Lines changed: 459 additions & 0 deletions

File tree

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -362,6 +362,15 @@ public static FunctionExpression day_of_week(
362362
return compile(functionProperties, BuiltinFunctionName.DAY_OF_WEEK, expressions);
363363
}
364364

365+
public static FunctionExpression extract(FunctionProperties functionProperties,
366+
Expression... expressions) {
367+
return compile(functionProperties, BuiltinFunctionName.EXTRACT, expressions);
368+
}
369+
370+
public static FunctionExpression extract(Expression... expressions) {
371+
return extract(FunctionProperties.None, expressions);
372+
}
373+
365374
public static FunctionExpression from_days(Expression... expressions) {
366375
return compile(FunctionProperties.None, BuiltinFunctionName.FROM_DAYS, expressions);
367376
}

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

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@
3535
import static org.opensearch.sql.utils.DateTimeUtils.extractDate;
3636
import static org.opensearch.sql.utils.DateTimeUtils.extractDateTime;
3737

38+
import com.google.common.collect.ImmutableMap;
3839
import com.google.common.collect.ImmutableTable;
3940
import com.google.common.collect.Table;
4041
import java.math.BigDecimal;
@@ -56,6 +57,7 @@
5657
import java.time.temporal.TemporalAmount;
5758
import java.util.Arrays;
5859
import java.util.Locale;
60+
import java.util.Map;
5961
import java.util.TimeZone;
6062
import java.util.concurrent.TimeUnit;
6163
import java.util.stream.Stream;
@@ -105,6 +107,32 @@ public class DateTimeFunction {
105107
// Mode used for week/week_of_year function by default when no argument is provided
106108
private static final ExprIntegerValue DEFAULT_WEEK_OF_YEAR_MODE = new ExprIntegerValue(0);
107109

110+
111+
// Map used to determine format output for the extract function
112+
private static final Map<String, String> extract_formats =
113+
ImmutableMap.<String, String>builder()
114+
.put("MICROSECOND", "SSSSSS")
115+
.put("SECOND", "ss")
116+
.put("MINUTE", "mm")
117+
.put("HOUR", "HH")
118+
.put("DAY", "dd")
119+
.put("WEEK", "w")
120+
.put("MONTH", "MM")
121+
.put("YEAR", "yyyy")
122+
.put("SECOND_MICROSECOND", "ssSSSSSS")
123+
.put("MINUTE_MICROSECOND", "mmssSSSSSS")
124+
.put("MINUTE_SECOND", "mmss")
125+
.put("HOUR_MICROSECOND", "HHmmssSSSSSS")
126+
.put("HOUR_SECOND", "HHmmss")
127+
.put("HOUR_MINUTE", "HHmm")
128+
.put("DAY_MICROSECOND", "ddHHmmssSSSSSS")
129+
.put("DAY_SECOND", "ddHHmmss")
130+
.put("DAY_MINUTE", "ddHHmm")
131+
.put("DAY_HOUR", "ddHH")
132+
.put("YEAR_MONTH", "yyyyMM")
133+
.put("QUARTER", "Q")
134+
.build();
135+
108136
// Map used to determine format output for the get_format function
109137
private static final Table<String, String, String> formats =
110138
ImmutableTable.<String, String, String>builder()
@@ -157,6 +185,7 @@ public void register(BuiltinFunctionRepository repository) {
157185
repository.register(dayOfWeek(BuiltinFunctionName.DAY_OF_WEEK.getName()));
158186
repository.register(dayOfYear(BuiltinFunctionName.DAYOFYEAR));
159187
repository.register(dayOfYear(BuiltinFunctionName.DAY_OF_YEAR));
188+
repository.register(extract());
160189
repository.register(from_days());
161190
repository.register(from_unixtime());
162191
repository.register(get_format());
@@ -539,6 +568,17 @@ private DefaultFunctionResolver dayOfYear(BuiltinFunctionName dayOfYear) {
539568
);
540569
}
541570

571+
private DefaultFunctionResolver extract() {
572+
return define(BuiltinFunctionName.EXTRACT.getName(),
573+
implWithProperties(nullMissingHandlingWithProperties(DateTimeFunction::exprExtractForTime),
574+
LONG, STRING, TIME),
575+
impl(nullMissingHandling(DateTimeFunction::exprExtract), LONG, STRING, DATE),
576+
impl(nullMissingHandling(DateTimeFunction::exprExtract), LONG, STRING, DATETIME),
577+
impl(nullMissingHandling(DateTimeFunction::exprExtract), LONG, STRING, TIMESTAMP),
578+
impl(nullMissingHandling(DateTimeFunction::exprExtract), LONG, STRING, STRING)
579+
);
580+
}
581+
542582
/**
543583
* FROM_DAYS(LONG). return the date value given the day number N.
544584
*/
@@ -1285,6 +1325,48 @@ private ExprValue exprDayOfYear(ExprValue date) {
12851325
return new ExprIntegerValue(date.dateValue().getDayOfYear());
12861326
}
12871327

1328+
/**
1329+
* Obtains a formatted long value for a specified part and datetime for the 'extract' function.
1330+
*
1331+
* @param part is an ExprValue which comes from a defined list of accepted values.
1332+
* @param datetime the date to be formatted as an ExprValue.
1333+
* @return is a LONG formatted according to the input arguments.
1334+
*/
1335+
public ExprLongValue formatExtractFunction(ExprValue part, ExprValue datetime) {
1336+
String partName = part.stringValue().toUpperCase();
1337+
LocalDateTime arg = datetime.datetimeValue();
1338+
String text = arg.format(DateTimeFormatter.ofPattern(
1339+
extract_formats.get(partName), Locale.ENGLISH));
1340+
1341+
return new ExprLongValue(Long.parseLong(text));
1342+
}
1343+
1344+
/**
1345+
* Implements extract function. Returns a LONG formatted according to the 'part' argument.
1346+
*
1347+
* @param part Literal that determines the format of the outputted LONG.
1348+
* @param datetime The date/datetime to be formatted.
1349+
* @return A LONG
1350+
*/
1351+
private ExprValue exprExtract(ExprValue part, ExprValue datetime) {
1352+
return formatExtractFunction(part, datetime);
1353+
}
1354+
1355+
/**
1356+
* Implements extract function. Returns a LONG formatted according to the 'part' argument.
1357+
*
1358+
* @param part Literal that determines the format of the outputted LONG.
1359+
* @param time The time to be formatted.
1360+
* @return A LONG
1361+
*/
1362+
private ExprValue exprExtractForTime(FunctionProperties functionProperties,
1363+
ExprValue part,
1364+
ExprValue time) {
1365+
return formatExtractFunction(
1366+
part,
1367+
new ExprDatetimeValue(extractDateTime(time, functionProperties)));
1368+
}
1369+
12881370
/**
12891371
* From_days implementation for ExprValue.
12901372
*

‎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
DAYOFYEAR(FunctionName.of("dayofyear")),
8181
DAY_OF_WEEK(FunctionName.of("day_of_week")),
8282
DAY_OF_YEAR(FunctionName.of("day_of_year")),
83+
EXTRACT(FunctionName.of("extract")),
8384
FROM_DAYS(FunctionName.of("from_days")),
8485
FROM_UNIXTIME(FunctionName.of("from_unixtime")),
8586
GET_FORMAT(FunctionName.of("get_format")),

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

Lines changed: 156 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,156 @@
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 java.time.temporal.ChronoField.ALIGNED_WEEK_OF_YEAR;
10+
import static org.junit.jupiter.api.Assertions.assertEquals;
11+
import static org.opensearch.sql.data.type.ExprCoreType.LONG;
12+
13+
import java.time.LocalDate;
14+
import java.util.stream.Stream;
15+
import org.junit.jupiter.api.Test;
16+
import org.junit.jupiter.params.ParameterizedTest;
17+
import org.junit.jupiter.params.provider.Arguments;
18+
import org.junit.jupiter.params.provider.MethodSource;
19+
import org.opensearch.sql.data.model.ExprDateValue;
20+
import org.opensearch.sql.data.model.ExprDatetimeValue;
21+
import org.opensearch.sql.data.model.ExprTimeValue;
22+
import org.opensearch.sql.data.model.ExprValue;
23+
import org.opensearch.sql.expression.DSL;
24+
import org.opensearch.sql.expression.Expression;
25+
import org.opensearch.sql.expression.ExpressionTestBase;
26+
import org.opensearch.sql.expression.FunctionExpression;
27+
28+
class ExtractTest extends ExpressionTestBase {
29+
30+
private final String datetimeInput = "2023-02-11 10:11:12.123";
31+
32+
private final String timeInput = "10:11:12.123";
33+
34+
private final String dateInput = "2023-02-11";
35+
36+
private static Stream<Arguments> getDatetimeResultsForExtractFunction() {
37+
return Stream.of(
38+
Arguments.of("DAY_MICROSECOND", 11101112123000L),
39+
Arguments.of("DAY_SECOND", 11101112),
40+
Arguments.of("DAY_MINUTE", 111011),
41+
Arguments.of("DAY_HOUR", 1110)
42+
);
43+
}
44+
45+
private static Stream<Arguments> getTimeResultsForExtractFunction() {
46+
return Stream.of(
47+
Arguments.of("MICROSECOND", 123000),
48+
Arguments.of("SECOND", 12),
49+
Arguments.of("MINUTE", 11),
50+
Arguments.of("HOUR", 10),
51+
Arguments.of("SECOND_MICROSECOND", 12123000),
52+
Arguments.of("MINUTE_MICROSECOND", 1112123000),
53+
Arguments.of("MINUTE_SECOND", 1112),
54+
Arguments.of("HOUR_MICROSECOND", 101112123000L),
55+
Arguments.of("HOUR_SECOND", 101112),
56+
Arguments.of("HOUR_MINUTE", 1011)
57+
);
58+
}
59+
60+
private static Stream<Arguments> getDateResultsForExtractFunction() {
61+
return Stream.of(
62+
Arguments.of("DAY", 11),
63+
Arguments.of("WEEK", 6),
64+
Arguments.of("MONTH", 2),
65+
Arguments.of("QUARTER", 1),
66+
Arguments.of("YEAR", 2023),
67+
Arguments.of("YEAR_MONTH", 202302)
68+
);
69+
}
70+
71+
@ParameterizedTest(name = "{0}")
72+
@MethodSource({
73+
"getDatetimeResultsForExtractFunction",
74+
"getTimeResultsForExtractFunction",
75+
"getDateResultsForExtractFunction"})
76+
public void testExtractWithDatetime(String part, long expected) {
77+
FunctionExpression datetimeExpression = DSL.extract(
78+
DSL.literal(part),
79+
DSL.literal(new ExprDatetimeValue(datetimeInput)));
80+
81+
assertEquals(LONG, datetimeExpression.type());
82+
assertEquals(expected, eval(datetimeExpression).longValue());
83+
assertEquals(
84+
String.format("extract(\"%s\", DATETIME '2023-02-11 10:11:12.123')", part),
85+
datetimeExpression.toString());
86+
}
87+
88+
private void datePartWithTimeArgQuery(String part, String time, long expected) {
89+
ExprTimeValue timeValue = new ExprTimeValue(time);
90+
FunctionExpression datetimeExpression = DSL.extract(
91+
functionProperties,
92+
DSL.literal(part),
93+
DSL.literal(timeValue));
94+
95+
assertEquals(LONG, datetimeExpression.type());
96+
assertEquals(expected,
97+
eval(datetimeExpression).longValue());
98+
}
99+
100+
101+
@Test
102+
public void testExtractDatePartWithTimeType() {
103+
datePartWithTimeArgQuery(
104+
"DAY",
105+
timeInput,
106+
LocalDate.now(functionProperties.getQueryStartClock()).getDayOfMonth());
107+
108+
datePartWithTimeArgQuery(
109+
"WEEK",
110+
timeInput,
111+
LocalDate.now(functionProperties.getQueryStartClock()).get(ALIGNED_WEEK_OF_YEAR));
112+
113+
datePartWithTimeArgQuery(
114+
"MONTH",
115+
timeInput,
116+
LocalDate.now(functionProperties.getQueryStartClock()).getMonthValue());
117+
118+
datePartWithTimeArgQuery(
119+
"YEAR",
120+
timeInput,
121+
LocalDate.now(functionProperties.getQueryStartClock()).getYear());
122+
}
123+
124+
@ParameterizedTest(name = "{0}")
125+
@MethodSource("getDateResultsForExtractFunction")
126+
public void testExtractWithDate(String part, long expected) {
127+
FunctionExpression datetimeExpression = DSL.extract(
128+
DSL.literal(part),
129+
DSL.literal(new ExprDateValue(dateInput)));
130+
131+
assertEquals(LONG, datetimeExpression.type());
132+
assertEquals(expected, eval(datetimeExpression).longValue());
133+
assertEquals(
134+
String.format("extract(\"%s\", DATE '2023-02-11')", part),
135+
datetimeExpression.toString());
136+
}
137+
138+
@ParameterizedTest(name = "{0}")
139+
@MethodSource("getTimeResultsForExtractFunction")
140+
public void testExtractWithTime(String part, long expected) {
141+
FunctionExpression datetimeExpression = DSL.extract(
142+
functionProperties,
143+
DSL.literal(part),
144+
DSL.literal(new ExprTimeValue(timeInput)));
145+
146+
assertEquals(LONG, datetimeExpression.type());
147+
assertEquals(expected, eval(datetimeExpression).longValue());
148+
assertEquals(
149+
String.format("extract(\"%s\", TIME '10:11:12.123')", part),
150+
datetimeExpression.toString());
151+
}
152+
153+
private ExprValue eval(Expression expression) {
154+
return expression.valueOf();
155+
}
156+
}

‎docs/user/dql/functions.rst‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1752,6 +1752,75 @@ Example::
17521752
+-------------------------------------------------+
17531753

17541754

1755+
EXTRACT
1756+
_______
1757+
1758+
Description
1759+
>>>>>>>>>>>
1760+
1761+
Usage: extract(part FROM date) returns a LONG with digits in order according to the given 'part' arguments.
1762+
The specific format of the returned long is determined by the table below.
1763+
1764+
Argument type: PART
1765+
PART must be one of the following tokens in the table below.
1766+
1767+
The format specifiers found in this table are the same as those found in the `DATE_FORMAT`_ function.
1768+
.. list-table:: The following table describes the mapping of a 'part' to a particular format.
1769+
:widths: 20 80
1770+
:header-rows: 1
1771+
1772+
* - Part
1773+
- Format
1774+
* - MICROSECOND
1775+
- %f
1776+
* - SECOND
1777+
- %s
1778+
* - MINUTE
1779+
- %i
1780+
* - HOUR
1781+
- %H
1782+
* - DAY
1783+
- %d
1784+
* - WEEK
1785+
- %X
1786+
* - MONTH
1787+
- %m
1788+
* - YEAR
1789+
- %V
1790+
* - SECOND_MICROSECOND
1791+
- %s%f
1792+
* - MINUTE_MICROSECOND
1793+
- %i%s%f
1794+
* - MINUTE_SECOND
1795+
- %i%s
1796+
* - HOUR_MICROSECOND
1797+
- %H%i%s%f
1798+
* - HOUR_SECOND
1799+
- %H%i%s
1800+
* - HOUR_MINUTE
1801+
- %H%i
1802+
* - DAY_MICROSECOND
1803+
- %d%H%i%s%f
1804+
* - DAY_SECOND
1805+
- %d%H%i%s
1806+
* - DAY_MINUTE
1807+
- %d%H%i
1808+
* - DAY_HOUR
1809+
- %d%H%
1810+
* - YEAR_MONTH
1811+
- %V%m
1812+
1813+
Return type: LONG
1814+
1815+
Example::
1816+
1817+
os> SELECT extract(YEAR_MONTH FROM "2023-02-07 10:11:12");
1818+
fetched rows / total rows = 1/1
1819+
+--------------------------------------------------+
1820+
| extract(YEAR_MONTH FROM "2023-02-07 10:11:12") |
1821+
|--------------------------------------------------|
1822+
| 202302 |
1823+
+--------------------------------------------------+
17551824

17561825
FROM_DAYS
17571826
---------

0 commit comments

Comments
 (0)