Skip to content

Commit 03e4f97

Browse files
GabeFernandez310GabeFernandez310
authored
Add YEARWEEK Function To OpenSearch SQL (#1417)
* Add `YEARWEEK` Function To OpenSearch SQL * Added Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Implementation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Implementation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Documentation Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Addressed PR Comments Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Cleaned YearweekTest File Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added Tests and Fixed Docs Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Added And Modified Unit Tests Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Jacoco In Core Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Reworked convertWeekModeFromMySqlToJava Function Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Refactored To Remove Extra Function Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> * Fixed Magic Number Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com> --------- Signed-off-by: GabeFernandez310 <Gabriel.Fernandez@improving.com>
1 parent 6496692 commit 03e4f97

9 files changed

Lines changed: 287 additions & 1 deletion

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
@@ -475,6 +475,11 @@ public static FunctionExpression year(Expression... expressions) {
475475
return compile(FunctionProperties.None, BuiltinFunctionName.YEAR, expressions);
476476
}
477477

478+
public static FunctionExpression yearweek(
479+
FunctionProperties functionProperties, Expression... expressions) {
480+
return compile(functionProperties, BuiltinFunctionName.YEARWEEK, expressions);
481+
}
482+
478483
public static FunctionExpression divide(Expression... expressions) {
479484
return compile(FunctionProperties.None, BuiltinFunctionName.DIVIDE, expressions);
480485
}

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

Lines changed: 72 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,7 @@ public void register(BuiltinFunctionRepository repository) {
200200
repository.register(week(BuiltinFunctionName.WEEK_OF_YEAR));
201201
repository.register(weekday());
202202
repository.register(year());
203+
repository.register(yearweek());
203204
}
204205

205206
/**
@@ -926,6 +927,30 @@ private DefaultFunctionResolver year() {
926927
);
927928
}
928929

930+
/**
931+
* YEARWEEK(DATE[,mode]). return the week number for date.
932+
*/
933+
private DefaultFunctionResolver yearweek() {
934+
return define(BuiltinFunctionName.YEARWEEK.getName(),
935+
implWithProperties(nullMissingHandlingWithProperties((functionProperties, arg)
936+
-> yearweekToday(
937+
DEFAULT_WEEK_OF_YEAR_MODE,
938+
functionProperties.getQueryStartClock())), INTEGER, TIME),
939+
impl(nullMissingHandling(DateTimeFunction::exprYearweekWithoutMode), INTEGER, DATE),
940+
impl(nullMissingHandling(DateTimeFunction::exprYearweekWithoutMode), INTEGER, DATETIME),
941+
impl(nullMissingHandling(DateTimeFunction::exprYearweekWithoutMode), INTEGER, TIMESTAMP),
942+
impl(nullMissingHandling(DateTimeFunction::exprYearweekWithoutMode), INTEGER, STRING),
943+
implWithProperties(nullMissingHandlingWithProperties((functionProperties, time, modeArg)
944+
-> yearweekToday(
945+
modeArg,
946+
functionProperties.getQueryStartClock())), INTEGER, TIME, INTEGER),
947+
impl(nullMissingHandling(DateTimeFunction::exprYearweek), INTEGER, DATE, INTEGER),
948+
impl(nullMissingHandling(DateTimeFunction::exprYearweek), INTEGER, DATETIME, INTEGER),
949+
impl(nullMissingHandling(DateTimeFunction::exprYearweek), INTEGER, TIMESTAMP, INTEGER),
950+
impl(nullMissingHandling(DateTimeFunction::exprYearweek), INTEGER, STRING, INTEGER)
951+
);
952+
}
953+
929954
/**
930955
* Formats date according to format specifier. First argument is date, second is format.
931956
* Detailed supported signatures:
@@ -1792,7 +1817,7 @@ private Double unixTimeStampOfImpl(ExprValue value) {
17921817
* @return ExprValue.
17931818
*/
17941819
private ExprValue exprWeekWithoutMode(ExprValue date) {
1795-
return exprWeek(date, new ExprIntegerValue(0));
1820+
return exprWeek(date, DEFAULT_WEEK_OF_YEAR_MODE);
17961821
}
17971822

17981823
/**
@@ -1805,6 +1830,52 @@ private ExprValue exprYear(ExprValue date) {
18051830
return new ExprIntegerValue(date.dateValue().getYear());
18061831
}
18071832

1833+
/**
1834+
* Helper function to extract the yearweek output from a given date.
1835+
*
1836+
* @param date is a LocalDate input argument.
1837+
* @param mode is an integer containing the mode used to parse the LocalDate.
1838+
* @return is a long containing the formatted output for the yearweek function.
1839+
*/
1840+
private ExprIntegerValue extractYearweek(LocalDate date, int mode) {
1841+
// Needed to align with MySQL. Due to how modes for this function work.
1842+
// See description of modes here ...
1843+
// https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week
1844+
int modeJava = CalendarLookup.getWeekNumber(mode, date) != 0 ? mode :
1845+
mode <= 4 ? 2 :
1846+
7;
1847+
1848+
int formatted = CalendarLookup.getYearNumber(modeJava, date) * 100
1849+
+ CalendarLookup.getWeekNumber(modeJava, date);
1850+
1851+
return new ExprIntegerValue(formatted);
1852+
}
1853+
1854+
/**
1855+
* Yearweek for date implementation for ExprValue.
1856+
*
1857+
* @param date ExprValue of Date/Datetime/Time/Timestamp/String type.
1858+
* @param mode ExprValue of Integer type.
1859+
*/
1860+
private ExprValue exprYearweek(ExprValue date, ExprValue mode) {
1861+
return extractYearweek(date.dateValue(), mode.integerValue());
1862+
}
1863+
1864+
/**
1865+
* Yearweek for date implementation for ExprValue.
1866+
* When mode is not specified default value mode 0 is used.
1867+
*
1868+
* @param date ExprValue of Date/Datetime/Time/Timestamp/String type.
1869+
* @return ExprValue.
1870+
*/
1871+
private ExprValue exprYearweekWithoutMode(ExprValue date) {
1872+
return exprYearweek(date, new ExprIntegerValue(0));
1873+
}
1874+
1875+
private ExprValue yearweekToday(ExprValue mode, Clock clock) {
1876+
return extractYearweek(LocalDateTime.now(clock).toLocalDate(), mode.integerValue());
1877+
}
1878+
18081879
private ExprValue monthOfYearToday(Clock clock) {
18091880
return new ExprIntegerValue(LocalDateTime.now(clock).getMonthValue());
18101881
}

‎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
@@ -118,6 +118,7 @@ public enum BuiltinFunctionName {
118118
WEEKOFYEAR(FunctionName.of("weekofyear")),
119119
WEEK_OF_YEAR(FunctionName.of("week_of_year")),
120120
YEAR(FunctionName.of("year")),
121+
YEARWEEK(FunctionName.of("yearweek")),
121122
// `now`-like functions
122123
NOW(FunctionName.of("now")),
123124
CURDATE(FunctionName.of("curdate")),

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

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
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.assertAll;
11+
import static org.junit.jupiter.api.Assertions.assertEquals;
12+
import static org.junit.jupiter.api.Assertions.assertThrows;
13+
import static org.opensearch.sql.data.model.ExprValueUtils.integerValue;
14+
import static org.opensearch.sql.data.type.ExprCoreType.INTEGER;
15+
16+
import java.time.LocalDate;
17+
import java.util.stream.Stream;
18+
import org.junit.jupiter.api.Test;
19+
import org.junit.jupiter.params.ParameterizedTest;
20+
import org.junit.jupiter.params.provider.Arguments;
21+
import org.junit.jupiter.params.provider.MethodSource;
22+
import org.opensearch.sql.data.model.ExprDateValue;
23+
import org.opensearch.sql.data.model.ExprDatetimeValue;
24+
import org.opensearch.sql.data.model.ExprTimeValue;
25+
import org.opensearch.sql.data.model.ExprValue;
26+
import org.opensearch.sql.exception.SemanticCheckException;
27+
import org.opensearch.sql.expression.DSL;
28+
import org.opensearch.sql.expression.Expression;
29+
import org.opensearch.sql.expression.ExpressionTestBase;
30+
import org.opensearch.sql.expression.FunctionExpression;
31+
32+
class YearweekTest extends ExpressionTestBase {
33+
34+
private void yearweekQuery(String date, int mode, int expectedResult) {
35+
FunctionExpression expression = DSL
36+
.yearweek(
37+
functionProperties,
38+
DSL.literal(new ExprDateValue(date)), DSL.literal(mode));
39+
assertAll(
40+
() -> assertEquals(INTEGER, expression.type()),
41+
() -> assertEquals(
42+
String.format("yearweek(DATE '%s', %d)", date, mode), expression.toString()),
43+
() -> assertEquals(integerValue(expectedResult), eval(expression))
44+
);
45+
}
46+
47+
private static Stream<Arguments> getTestDataForYearweek() {
48+
//Test the behavior of different modes passed into the 'yearweek' function
49+
return Stream.of(
50+
Arguments.of("2019-01-05", 0, 201852),
51+
Arguments.of("2019-01-05", 1, 201901),
52+
Arguments.of("2019-01-05", 2, 201852),
53+
Arguments.of("2019-01-05", 3, 201901),
54+
Arguments.of("2019-01-05", 4, 201901),
55+
Arguments.of("2019-01-05", 5, 201853),
56+
Arguments.of("2019-01-05", 6, 201901),
57+
Arguments.of("2019-01-05", 7, 201853),
58+
Arguments.of("2019-01-06", 0, 201901),
59+
Arguments.of("2019-01-06", 1, 201901),
60+
Arguments.of("2019-01-06", 2, 201901),
61+
Arguments.of("2019-01-06", 3, 201901),
62+
Arguments.of("2019-01-06", 4, 201902),
63+
Arguments.of("2019-01-06", 5, 201853),
64+
Arguments.of("2019-01-06", 6, 201902),
65+
Arguments.of("2019-01-06", 7, 201853),
66+
Arguments.of("2019-01-07", 0, 201901),
67+
Arguments.of("2019-01-07", 1, 201902),
68+
Arguments.of("2019-01-07", 2, 201901),
69+
Arguments.of("2019-01-07", 3, 201902),
70+
Arguments.of("2019-01-07", 4, 201902),
71+
Arguments.of("2019-01-07", 5, 201901),
72+
Arguments.of("2019-01-07", 6, 201902),
73+
Arguments.of("2019-01-07", 7, 201901),
74+
Arguments.of("2000-01-01", 0, 199952),
75+
Arguments.of("2000-01-01", 2, 199952),
76+
Arguments.of("1999-12-31", 0, 199952),
77+
Arguments.of("1999-01-01", 0, 199852),
78+
Arguments.of("1999-01-01", 1, 199852),
79+
Arguments.of("1999-01-01", 4, 199852),
80+
Arguments.of("1999-01-01", 5, 199852),
81+
Arguments.of("1999-01-01", 6, 199852)
82+
);
83+
}
84+
85+
@ParameterizedTest(name = "{0} | {1}")
86+
@MethodSource("getTestDataForYearweek")
87+
public void testYearweak(String date, int mode, int expected) {
88+
yearweekQuery(date, mode, expected);
89+
}
90+
91+
@Test
92+
public void testYearweekWithoutMode() {
93+
LocalDate date = LocalDate.of(2019,1,05);
94+
95+
FunctionExpression expression = DSL
96+
.yearweek(
97+
functionProperties,
98+
DSL.literal(new ExprDateValue(date)), DSL.literal(0));
99+
100+
FunctionExpression expressionWithoutMode = DSL
101+
.yearweek(
102+
functionProperties,
103+
DSL.literal(new ExprDateValue(date)));
104+
105+
assertEquals(eval(expression), eval(expressionWithoutMode));
106+
}
107+
108+
@Test
109+
public void testYearweekWithTimeType() {
110+
int week = LocalDate.now(functionProperties.getQueryStartClock()).get(ALIGNED_WEEK_OF_YEAR);
111+
int year = LocalDate.now(functionProperties.getQueryStartClock()).getYear();
112+
int expected = Integer.parseInt(String.format("%d%02d", year, week));
113+
114+
FunctionExpression expression = DSL
115+
.yearweek(
116+
functionProperties,
117+
DSL.literal(new ExprTimeValue("10:11:12")), DSL.literal(0));
118+
119+
FunctionExpression expressionWithoutMode = DSL
120+
.yearweek(
121+
functionProperties,
122+
DSL.literal(new ExprTimeValue("10:11:12")));
123+
124+
assertAll(
125+
() -> assertEquals(expected, eval(expression).integerValue()),
126+
() -> assertEquals(expected, eval(expressionWithoutMode).integerValue())
127+
);
128+
}
129+
130+
@Test
131+
public void testInvalidYearWeek() {
132+
assertAll(
133+
//test invalid month
134+
() -> assertThrows(
135+
SemanticCheckException.class,
136+
() -> yearweekQuery("2019-13-05 01:02:03", 0, 0)),
137+
//test invalid day
138+
() -> assertThrows(
139+
SemanticCheckException.class,
140+
() -> yearweekQuery("2019-01-50 01:02:03", 0, 0)),
141+
//test invalid leap year
142+
() -> assertThrows(
143+
SemanticCheckException.class,
144+
() -> yearweekQuery("2019-02-29 01:02:03", 0, 0))
145+
);
146+
}
147+
148+
@Test
149+
public void yearweekModeInUnsupportedFormat() {
150+
FunctionExpression expression1 = DSL
151+
.yearweek(
152+
functionProperties,
153+
DSL.literal(new ExprDatetimeValue("2019-01-05 10:11:12")), DSL.literal(8));
154+
SemanticCheckException exception =
155+
assertThrows(SemanticCheckException.class, () -> eval(expression1));
156+
assertEquals("mode:8 is invalid, please use mode value between 0-7",
157+
exception.getMessage());
158+
159+
FunctionExpression expression2 = DSL
160+
.yearweek(
161+
functionProperties,
162+
DSL.literal(new ExprDatetimeValue("2019-01-05 10:11:12")), DSL.literal(-1));
163+
exception = assertThrows(SemanticCheckException.class, () -> eval(expression2));
164+
assertEquals("mode:-1 is invalid, please use mode value between 0-7",
165+
exception.getMessage());
166+
}
167+
168+
private ExprValue eval(Expression expression) {
169+
return expression.valueOf();
170+
}
171+
}

‎docs/user/dql/functions.rst‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2813,6 +2813,28 @@ Example::
28132813
+----------------------------+
28142814

28152815

2816+
YEARWEEK
2817+
--------
2818+
2819+
Description
2820+
>>>>>>>>>>>
2821+
2822+
Usage: yearweek(date) returns the year and week for date as an integer. It accepts and optional mode arguments aligned with those available for the `WEEK`_ function.
2823+
2824+
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
2825+
2826+
Return type: INTEGER
2827+
2828+
Example::
2829+
2830+
os> SELECT YEARWEEK('2020-08-26'), YEARWEEK('2019-01-05', 0)
2831+
fetched rows / total rows = 1/1
2832+
+--------------------------+-----------------------------+
2833+
| YEARWEEK('2020-08-26') | YEARWEEK('2019-01-05', 0) |
2834+
|--------------------------+-----------------------------|
2835+
| 202034 | 201852 |
2836+
+--------------------------+-----------------------------+
2837+
28162838
String Functions
28172839
================
28182840

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1001,6 +1001,14 @@ public void testWeekAlternateSyntaxesReturnTheSameResults() throws IOException {
10011001
compareWeekResults("CAST(datetime0 AS timestamp)", TEST_INDEX_CALCS);
10021002
}
10031003

1004+
@Test
1005+
public void testYearweek() throws IOException {
1006+
JSONObject result = executeQuery(
1007+
String.format("SELECT yearweek(time0), yearweek(time0, 4) FROM %s LIMIT 2", TEST_INDEX_CALCS));
1008+
1009+
verifyDataRows(result, rows(189952, 189952), rows(189953, 190001));
1010+
}
1011+
10041012
void verifyDateFormat(String date, String type, String format, String formatted) throws IOException {
10051013
String query = String.format("date_format(%s('%s'), '%s')", type, date, format);
10061014
JSONObject result = executeQuery("select " + query);

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -342,6 +342,7 @@ STRCMP: 'STRCMP';
342342

343343
// DATE AND TIME FUNCTIONS
344344
ADDDATE: 'ADDDATE';
345+
YEARWEEK: 'YEARWEEK';
345346

346347
// RELEVANCE FUNCTIONS AND PARAMETERS
347348
ALLOW_LEADING_WILDCARD: 'ALLOW_LEADING_WILDCARD';

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -484,6 +484,7 @@ dateTimeFunctionName
484484
| WEEK_OF_YEAR
485485
| WEEKOFYEAR
486486
| YEAR
487+
| YEARWEEK
487488
;
488489

489490
textFunctionName

‎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
@@ -555,6 +555,12 @@ public void can_parse_wildcard_query_relevance_function() {
555555
+ "boost=1.5, case_insensitive=true, rewrite=\"scoring_boolean\")"));
556556
}
557557

558+
@Test
559+
public void can_parse_yearweek_function() {
560+
assertNotNull(parser.parse("SELECT yearweek('1987-01-01')"));
561+
assertNotNull(parser.parse("SELECT yearweek('1987-01-01', 1)"));
562+
}
563+
558564
@Test
559565
public void describe_request_accepts_only_quoted_string_literals() {
560566
assertAll(

0 commit comments

Comments
 (0)