Skip to content

Commit 5ca6251

Browse files
mihaibudiumihaibudiu
committed
[SQL] Add support for FORMAT_TIMESTAMP and FORMAT_TIME
Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
1 parent 91c6514 commit 5ca6251

6 files changed

Lines changed: 231 additions & 8 deletions

File tree

‎crates/sqllib/src/timestamp.rs‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1780,6 +1780,20 @@ pub fn format_date__(format: SqlString, date: Date) -> SqlString {
17801780

17811781
some_function2!(format_date, SqlString, Date, SqlString);
17821782

1783+
#[doc(hidden)]
1784+
pub fn format_time__(format: SqlString, date: Time) -> SqlString {
1785+
SqlString::from(date.to_time().format(format.str()).to_string())
1786+
}
1787+
1788+
some_function2!(format_time, SqlString, Time, SqlString);
1789+
1790+
#[doc(hidden)]
1791+
pub fn format_timestamp__(format: SqlString, date: Timestamp) -> SqlString {
1792+
SqlString::from(date.to_naiveDateTime().format(format.str()).to_string())
1793+
}
1794+
1795+
some_function2!(format_timestamp, SqlString, Timestamp, SqlString);
1796+
17831797
#[doc(hidden)]
17841798
pub fn parse_date__(format: SqlString, st: SqlString) -> Option<Date> {
17851799
let nd = NaiveDate::parse_from_str(st.str(), format.str());

‎docs.feldera.com/docs/sql/datetime.md‎

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -438,12 +438,14 @@ AND T.ts <= NOW() + INTERVAL 1 DAYS`).
438438

439439
We support the following functions for formatting and parsing date-like values:
440440

441-
| Operation | Arguments | Result | Example |
442-
|--------------------|-----------------------|-----------|--------------------------------------|
443-
| `FORMAT_DATE` | string_format, date | string | `FORMAT_DATE('%Y-%m', DATE '2020-10-10')` => `2020-10` |
444-
| `PARSE_DATE` | string_format, string | DATE | `PARSE_DATE(' %Y-%m-%d', ' 2020-10-01')` => `2020-10-01` |
445-
| `PARSE_TIME` | string_format, string | TIME | `PARSE_TIME('%H:%M', '10:10')` => `10:10:00` |
446-
| `PARSE_TIMESTAMP` | string_format, string | TIMESTAMP | `PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2020-10-01 00:00:00')` => `2020-10-01 00:00:00` |
441+
| Operation | Arguments | Result | Example |
442+
|-------------------------------------------------|--------------------------|-----------|--------------------------------------|
443+
| <a id="format_date"></a> `FORMAT_DATE` | string_format, date | string | `FORMAT_DATE('%Y-%m', DATE '2020-10-10')` => `2020-10` |
444+
| <a id="format_timestamp"></a>`FORMAT_TIMESTAMP` | string_format, timestamp | string | `FORMAT_TIMESTAMP('%Y-%m %H,%M,%S', TIMESTAMP '2020-10-10 10:00:00')` => `2020-10 10,00,00` |
445+
| <a id="format_time"></a> `FORMAT_TIME` | string_format, time | string | `FORMAT_TIME('%H-%M-%S', TIME '10:00:00')` => `10-00-00` |
446+
| <a id="parse_date"></a> `PARSE_DATE` | string_format, string | DATE | `PARSE_DATE(' %Y-%m-%d', ' 2020-10-01')` => `2020-10-01` |
447+
| <a id="parse_time"></a> `PARSE_TIME` | string_format, string | TIME | `PARSE_TIME('%H:%M', '10:10')` => `10:10:00` |
448+
| <a id="parse_timestamp"></a> `PARSE_TIMESTAMP` | string_format, string | TIMESTAMP | `PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2020-10-01 00:00:00')` => `2020-10-01 00:00:00` |
447449

448450
If the string cannot be parsed according to the specified format:
449451

‎docs.feldera.com/docs/sql/function-index.md‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -107,7 +107,9 @@
107107
* `EXTRACT`: [datetime](datetime.md#time_extract), [datetime](datetime.md#date_extract), [datetime](datetime.md#timestamp_extract)
108108
* `FILTER`: [aggregates](aggregates.md#filter)
109109
* `FLOOR`: [decimal](decimal.md#floor), [float](float.md#floor), [datetime](datetime.md#date_floor), [datetime](datetime.md#timestamp_floor), [datetime](datetime.md#time_floor)
110-
* `FORMAT_DATE`: [datetime](datetime.md#date-parsing-and-formatting)
110+
* `FORMAT_DATE`: [datetime](datetime.md#format_date)
111+
* `FORMAT_TIME`: [datetime](datetime.md#format_time)
112+
* `FORMAT_TIMESTAMP`: [datetime](datetime.md#format_timestamp)
111113
* `GREATEST`: [comparisons](comparisons.md#greatest)
112114
* `GREATEST_IGNORE_NULLS`: [comparisons](comparisons.md#greatest_ignore_nulls)
113115
* `GROUPING`: [grammar](grammar.md#grouping)

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1426,6 +1426,8 @@ else if (arg0Type.is(DBSPTypeMap.class))
14261426
return compilePolymorphicFunction(false, call, node, type, ops, 2);
14271427
}
14281428
case "format_date":
1429+
case "format_timestamp":
1430+
case "format_time":
14291431
this.checkFormatArg(ops, 0);
14301432
return compileFunction(call, node, type, ops, 2);
14311433
case "bround": {

‎sql-to-dbsp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/CustomFunctions.java‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,8 @@ public CustomFunctions() {
6969
this.functions.add(new BlackboxFunction());
7070
this.functions.add(new BroundFunction());
7171
this.functions.add(new FormatDateFunction());
72+
this.functions.add(new FormatTimestampFunction());
73+
this.functions.add(new FormatTimeFunction());
7274
this.functions.add(new GreatestNonNullsFunction());
7375
this.functions.add(new GunzipFunction());
7476
this.functions.add(new InitcapSpacesFunction());
@@ -174,7 +176,26 @@ public CalciteFunctionClone(SqlFunction calciteFunction, String documentationFil
174176

175177
static class FormatDateFunction extends CalciteFunctionClone {
176178
private FormatDateFunction() {
177-
super(SqlLibraryOperators.FORMAT_DATE, "datetime#date-parsing-and-formatting", FunctionDocumentation.NO_FILE);
179+
super(SqlLibraryOperators.FORMAT_DATE, "datetime#format_date", FunctionDocumentation.NO_FILE);
180+
}
181+
}
182+
183+
static class FormatTimestampFunction extends NonOptimizedFunction {
184+
private FormatTimestampFunction() {
185+
super("FORMAT_TIMESTAMP",
186+
ReturnTypes.VARCHAR.andThen(SqlTypeTransforms.TO_NULLABLE),
187+
OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, <TIMESTAMP>)",
188+
OperandTypes.CHARACTER, OperandTypes.TIMESTAMP),
189+
SqlFunctionCategory.USER_DEFINED_FUNCTION,
190+
"datetime#format_timestamp",
191+
FunctionDocumentation.NO_FILE
192+
);
193+
}
194+
}
195+
196+
static class FormatTimeFunction extends CalciteFunctionClone {
197+
private FormatTimeFunction() {
198+
super(SqlLibraryOperators.FORMAT_TIME, "datetime#format_time", FunctionDocumentation.NO_FILE);
178199
}
179200
}
180201

‎sql-to-dbsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/mysql/DateFormatsTests.java‎

Lines changed: 182 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,6 +136,188 @@ public void testCorners() {
136136
(1 row)""");
137137
}
138138

139+
record R(int year, int month, int day, String format, String expected) {
140+
@Override
141+
public String toString() {
142+
return this.year + "-" + String.format("%02d", this.month) + "-" + String.format("%02d", this.day);
143+
}
144+
}
145+
146+
@Test
147+
public void testFormatDate() {
148+
R[] tests = {
149+
new R(2024, 3, 5, "%Y", "2024"),
150+
new R(2024, 3, 5, "%C", "20"),
151+
new R(2024, 3, 5, "%y", "24"),
152+
new R(2024, 3, 5, "%m", "03"),
153+
new R(2024, 3, 5, "%b", "Mar"),
154+
new R(2024, 3, 5, "%B", "March"),
155+
new R(2024, 3, 5, "%h", "Mar"),
156+
new R(2024, 3, 5, "%d", "05"),
157+
new R(2024, 3, 5, "%e", " 5"),
158+
new R(2024, 3, 5, "%a", "Tue"),
159+
new R(2024, 3, 5, "%A", "Tuesday"),
160+
new R(2024, 3, 5, "%u", "2"),
161+
new R(2024, 3, 5, "%w", "2"),
162+
new R(2024, 1, 1, "%U", "00"),
163+
new R(2024, 1, 1, "%W", "01"),
164+
new R(2024, 1, 1, "%G", "2024"),
165+
new R(2024, 1, 1, "%g", "24"),
166+
new R(2024, 1, 1, "%V", "01"),
167+
new R(2024, 12, 31, "%j", "366"),
168+
new R(2024, 3, 5, "%D", "03/05/24"),
169+
new R(2024, 3, 5, "%F", "2024-03-05"),
170+
new R(2024, 3, 5, "%x", "03/05/24"), // Chrono uses US locale
171+
new R(2024, 3, 5, "%%", "%"),
172+
new R(2024, 3, 5, "%q", "1")
173+
};
174+
175+
StringBuilder builder = new StringBuilder();
176+
for (R r: tests) {
177+
builder.append("SELECT format_date('")
178+
.append(r.format)
179+
.append("', '")
180+
.append(r).append("');\n")
181+
.append(" r\n")
182+
.append("------\n")
183+
.append(" ").append(r.expected).append("\n")
184+
.append("(1 row)\n\n");
185+
}
186+
this.qs(builder.toString());
187+
}
188+
189+
record T(int hour, int minute, int second, int microsec) {
190+
@Override
191+
public String toString() {
192+
return String.format("%02d", this.hour) + ":" + String.format("%02d", this.minute) + ":" + String.format("%02d", this.second)
193+
+ "." + String.format("%06d", this.microsec);
194+
}
195+
}
196+
197+
record FE(String format, String expected) {}
198+
199+
@Test
200+
public void testFormatTime() {
201+
FE[] tests = {
202+
new FE("%H", "13"),
203+
new FE("%k", "13"),
204+
new FE("%-H", "13"),
205+
new FE("%_H", "13"),
206+
new FE("%I", "01"),
207+
new FE("%l", " 1"),
208+
new FE("%-I", "1"),
209+
new FE("%M", "05"),
210+
new FE("%-M", "5"),
211+
new FE("%S", "09"),
212+
new FE("%-S", "9"),
213+
new FE("%.f", ".123456"),
214+
new FE("%.3f", ".123"),
215+
new FE("%.6f", ".123456"),
216+
new FE("%.9f", ".123456000"),
217+
new FE("%p", "PM"),
218+
new FE("%P", "pm"),
219+
new FE("%T", "13:05:09"),
220+
new FE("%R", "13:05"),
221+
new FE("%X", "13:05:09"),
222+
new FE("%r", "01:05:09 PM"),
223+
new FE("%%", "%"),
224+
new FE("%H%%%M", "13%05"),
225+
};
226+
227+
StringBuilder builder = new StringBuilder();
228+
T time = new T(13, 5, 9, 123456);
229+
for (FE r: tests) {
230+
builder.append("SELECT format_time('")
231+
.append(r.format)
232+
.append("', '")
233+
.append(time).append("');\n")
234+
.append(" r\n")
235+
.append("------\n")
236+
.append(" ").append(r.expected).append("\n")
237+
.append("(1 row)\n\n");
238+
}
239+
this.qs(builder.toString());
240+
}
241+
242+
@Test
243+
public void testFormatTimestamp() {
244+
FE[] tests = {
245+
new FE("%Y", "2024"),
246+
new FE("%y", "24"),
247+
new FE("%C", "20"),
248+
// --- Month ---
249+
new FE("%m", "03"),
250+
new FE("%-m", "3"),
251+
new FE("%b", "Mar"),
252+
new FE("%B", "March"),
253+
// --- Day ---
254+
new FE("%d", "05"),
255+
new FE("%-d", "5"),
256+
new FE("%e", " 5"),
257+
new FE("%j", "065"), // ordinal day
258+
// --- Weekday ---
259+
new FE("%a", "Tue"),
260+
new FE("%A", "Tuesday"),
261+
new FE("%u", "2"), // ISO weekday (Mon=1)
262+
new FE("%w", "2"), // Sunday=0
263+
// --- ISO week date ---
264+
new FE("%G", "2024"),
265+
new FE("%g", "24"),
266+
new FE("%V", "10"),
267+
// --- Hour (24h) ---
268+
new FE("%H", "13"),
269+
new FE("%k", "13"),
270+
new FE("%-H", "13"),
271+
new FE("%_H", "13"),
272+
// --- Hour (12h) ---
273+
new FE("%I", "01"),
274+
new FE("%l", " 1"),
275+
new FE("%-I", "1"),
276+
// --- Minute ---
277+
new FE("%M", "05"),
278+
new FE("%-M", "5"),
279+
// --- Second ---
280+
new FE("%S", "09"),
281+
new FE("%-S", "9"),
282+
new FE("%f", "123456000"),
283+
new FE("%.f", ".123456"),
284+
new FE("%.3f", ".123"),
285+
new FE("%.6f", ".123456"),
286+
new FE("%.9f", ".123456000"),
287+
// --- AM/PM ---
288+
new FE("%p", "PM"),
289+
new FE("%P", "pm"),
290+
// --- Unix timestamp ---
291+
// 2024‑03‑05 13:05:09 UTC → 1709643909
292+
new FE("%s", "1709643909"),
293+
// --- Composite formats ---
294+
new FE("%F", "2024-03-05"),
295+
new FE("%D", "03/05/24"),
296+
new FE("%x", "03/05/24"),
297+
new FE("%T", "13:05:09"),
298+
new FE("%R", "13:05"),
299+
new FE("%X", "13:05:09"),
300+
new FE("%r", "01:05:09 PM"),
301+
// --- Literal percent ---
302+
new FE("%%", "%"),
303+
new FE("%Y%%%m", "2024%03"),
304+
new FE("%H%%%M", "13%05"),
305+
new FE("%H%%M", "13%M"),
306+
};
307+
308+
StringBuilder builder = new StringBuilder();
309+
for (FE r: tests) {
310+
builder.append("SELECT format_timestamp('")
311+
.append(r.format)
312+
.append("', TIMESTAMP '2024-03-05 13:05:09.123456');\n")
313+
.append(" r\n")
314+
.append("------\n")
315+
.append(" ").append(r.expected).append("\n")
316+
.append("(1 row)\n\n");
317+
}
318+
this.qs(builder.toString());
319+
}
320+
139321
@Test
140322
public void testAllFormat() {
141323
// Test all format flags that can be applied to a DATE.

0 commit comments

Comments
 (0)