Skip to content

[SQL] Add support for FORMAT_TIMESTAMP and FORMAT_TIME #5889

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
mihaibudiu merged 1 commit into from
Mar 23, 2026
Merged

[SQL] Add support for FORMAT_TIMESTAMP and FORMAT_TIME #5889

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
14 changes: 14 additions & 0 deletions crates/sqllib/src/timestamp.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1780,6 +1780,20 @@ pub fn format_date__(format: SqlString, date: Date) -> SqlString {

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

#[doc(hidden)]
pub fn format_time__(format: SqlString, date: Time) -> SqlString {
SqlString::from(date.to_time().format(format.str()).to_string())
}

some_function2!(format_time, SqlString, Time, SqlString);

#[doc(hidden)]
pub fn format_timestamp__(format: SqlString, date: Timestamp) -> SqlString {
SqlString::from(date.to_naiveDateTime().format(format.str()).to_string())
}

some_function2!(format_timestamp, SqlString, Timestamp, SqlString);

#[doc(hidden)]
pub fn parse_date__(format: SqlString, st: SqlString) -> Option<Date> {
let nd = NaiveDate::parse_from_str(st.str(), format.str());
Expand Down
14 changes: 8 additions & 6 deletions docs.feldera.com/docs/sql/datetime.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -438,12 +438,14 @@ AND T.ts <= NOW() + INTERVAL 1 DAYS`).

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

| Operation | Arguments | Result | Example |
|--------------------|-----------------------|-----------|--------------------------------------|
| `FORMAT_DATE` | string_format, date | string | `FORMAT_DATE('%Y-%m', DATE '2020-10-10')` => `2020-10` |
| `PARSE_DATE` | string_format, string | DATE | `PARSE_DATE(' %Y-%m-%d', ' 2020-10-01')` => `2020-10-01` |
| `PARSE_TIME` | string_format, string | TIME | `PARSE_TIME('%H:%M', '10:10')` => `10:10:00` |
| `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` |
| Operation | Arguments | Result | Example |
|-------------------------------------------------|--------------------------|-----------|--------------------------------------|
| <a id="format_date"></a> `FORMAT_DATE` | string_format, date | string | `FORMAT_DATE('%Y-%m', DATE '2020-10-10')` => `2020-10` |
| <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` |
| <a id="format_time"></a> `FORMAT_TIME` | string_format, time | string | `FORMAT_TIME('%H-%M-%S', TIME '10:00:00')` => `10-00-00` |
| <a id="parse_date"></a> `PARSE_DATE` | string_format, string | DATE | `PARSE_DATE(' %Y-%m-%d', ' 2020-10-01')` => `2020-10-01` |
| <a id="parse_time"></a> `PARSE_TIME` | string_format, string | TIME | `PARSE_TIME('%H:%M', '10:10')` => `10:10:00` |
| <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` |

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

Expand Down
4 changes: 3 additions & 1 deletion docs.feldera.com/docs/sql/function-index.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -107,7 +107,9 @@
* `EXTRACT`: [datetime](datetime.md#time_extract), [datetime](datetime.md#date_extract), [datetime](datetime.md#timestamp_extract)
* `FILTER`: [aggregates](aggregates.md#filter)
* `FLOOR`: [decimal](decimal.md#floor), [float](float.md#floor), [datetime](datetime.md#date_floor), [datetime](datetime.md#timestamp_floor), [datetime](datetime.md#time_floor)
* `FORMAT_DATE`: [datetime](datetime.md#date-parsing-and-formatting)
* `FORMAT_DATE`: [datetime](datetime.md#format_date)
* `FORMAT_TIME`: [datetime](datetime.md#format_time)
* `FORMAT_TIMESTAMP`: [datetime](datetime.md#format_timestamp)
* `GREATEST`: [comparisons](comparisons.md#greatest)
* `GREATEST_IGNORE_NULLS`: [comparisons](comparisons.md#greatest_ignore_nulls)
* `GROUPING`: [grammar](grammar.md#grouping)
Expand Down
2 changes: 2 additions & 0 deletions ...SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/ExpressionCompiler.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1426,6 +1426,8 @@ else if (arg0Type.is(DBSPTypeMap.class))
return compilePolymorphicFunction(false, call, node, type, ops, 2);
}
case "format_date":
case "format_timestamp":
case "format_time":
this.checkFormatArg(ops, 0);
return compileFunction(call, node, type, ops, 2);
case "bround": {
Expand Down
23 changes: 22 additions & 1 deletion ...src/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/CustomFunctions.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -69,6 +69,8 @@ public CustomFunctions() {
this.functions.add(new BlackboxFunction());
this.functions.add(new BroundFunction());
this.functions.add(new FormatDateFunction());
this.functions.add(new FormatTimestampFunction());
this.functions.add(new FormatTimeFunction());
this.functions.add(new GreatestNonNullsFunction());
this.functions.add(new GunzipFunction());
this.functions.add(new InitcapSpacesFunction());
Expand Down Expand Up @@ -174,7 +176,26 @@ public CalciteFunctionClone(SqlFunction calciteFunction, String documentationFil

static class FormatDateFunction extends CalciteFunctionClone {
private FormatDateFunction() {
super(SqlLibraryOperators.FORMAT_DATE, "datetime#date-parsing-and-formatting", FunctionDocumentation.NO_FILE);
super(SqlLibraryOperators.FORMAT_DATE, "datetime#format_date", FunctionDocumentation.NO_FILE);
}
}

static class FormatTimestampFunction extends NonOptimizedFunction {
private FormatTimestampFunction() {
super("FORMAT_TIMESTAMP",
ReturnTypes.VARCHAR.andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, <TIMESTAMP>)",
OperandTypes.CHARACTER, OperandTypes.TIMESTAMP),
SqlFunctionCategory.USER_DEFINED_FUNCTION,
"datetime#format_timestamp",
FunctionDocumentation.NO_FILE
);
}
}

static class FormatTimeFunction extends CalciteFunctionClone {
private FormatTimeFunction() {
super(SqlLibraryOperators.FORMAT_TIME, "datetime#format_time", FunctionDocumentation.NO_FILE);
}
}

Expand Down
182 changes: 182 additions & 0 deletions .../SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/mysql/DateFormatsTests.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -136,6 +136,188 @@ public void testCorners() {
(1 row)""");
}

record R(int year, int month, int day, String format, String expected) {
@Override
public String toString() {
return this.year + "-" + String.format("%02d", this.month) + "-" + String.format("%02d", this.day);
}
}

@Test
public void testFormatDate() {
R[] tests = {
new R(2024, 3, 5, "%Y", "2024"),
new R(2024, 3, 5, "%C", "20"),
new R(2024, 3, 5, "%y", "24"),
new R(2024, 3, 5, "%m", "03"),
new R(2024, 3, 5, "%b", "Mar"),
new R(2024, 3, 5, "%B", "March"),
new R(2024, 3, 5, "%h", "Mar"),
new R(2024, 3, 5, "%d", "05"),
new R(2024, 3, 5, "%e", " 5"),
new R(2024, 3, 5, "%a", "Tue"),
new R(2024, 3, 5, "%A", "Tuesday"),
new R(2024, 3, 5, "%u", "2"),
new R(2024, 3, 5, "%w", "2"),
new R(2024, 1, 1, "%U", "00"),
new R(2024, 1, 1, "%W", "01"),
new R(2024, 1, 1, "%G", "2024"),
new R(2024, 1, 1, "%g", "24"),
new R(2024, 1, 1, "%V", "01"),
new R(2024, 12, 31, "%j", "366"),
new R(2024, 3, 5, "%D", "03/05/24"),
new R(2024, 3, 5, "%F", "2024-03-05"),
new R(2024, 3, 5, "%x", "03/05/24"), // Chrono uses US locale
new R(2024, 3, 5, "%%", "%"),
new R(2024, 3, 5, "%q", "1")
};

StringBuilder builder = new StringBuilder();
for (R r: tests) {
builder.append("SELECT format_date('")
.append(r.format)
.append("', '")
.append(r).append("');\n")
.append(" r\n")
.append("------\n")
.append(" ").append(r.expected).append("\n")
.append("(1 row)\n\n");
}
this.qs(builder.toString());
}

record T(int hour, int minute, int second, int microsec) {
@Override
public String toString() {
return String.format("%02d", this.hour) + ":" + String.format("%02d", this.minute) + ":" + String.format("%02d", this.second)
+ "." + String.format("%06d", this.microsec);
}
}

record FE(String format, String expected) {}

@Test
public void testFormatTime() {
FE[] tests = {
new FE("%H", "13"),
new FE("%k", "13"),
new FE("%-H", "13"),
new FE("%_H", "13"),
new FE("%I", "01"),
new FE("%l", " 1"),
new FE("%-I", "1"),
new FE("%M", "05"),
new FE("%-M", "5"),
new FE("%S", "09"),
new FE("%-S", "9"),
new FE("%.f", ".123456"),
new FE("%.3f", ".123"),
new FE("%.6f", ".123456"),
new FE("%.9f", ".123456000"),
new FE("%p", "PM"),
new FE("%P", "pm"),
new FE("%T", "13:05:09"),
new FE("%R", "13:05"),
new FE("%X", "13:05:09"),
new FE("%r", "01:05:09 PM"),
new FE("%%", "%"),
new FE("%H%%%M", "13%05"),
};

StringBuilder builder = new StringBuilder();
T time = new T(13, 5, 9, 123456);
for (FE r: tests) {
builder.append("SELECT format_time('")
.append(r.format)
.append("', '")
.append(time).append("');\n")
.append(" r\n")
.append("------\n")
.append(" ").append(r.expected).append("\n")
.append("(1 row)\n\n");
}
this.qs(builder.toString());
}

@Test
public void testFormatTimestamp() {
FE[] tests = {
new FE("%Y", "2024"),
new FE("%y", "24"),
new FE("%C", "20"),
// --- Month ---
new FE("%m", "03"),
new FE("%-m", "3"),
new FE("%b", "Mar"),
new FE("%B", "March"),
// --- Day ---
new FE("%d", "05"),
new FE("%-d", "5"),
new FE("%e", " 5"),
new FE("%j", "065"), // ordinal day
// --- Weekday ---
new FE("%a", "Tue"),
new FE("%A", "Tuesday"),
new FE("%u", "2"), // ISO weekday (Mon=1)
new FE("%w", "2"), // Sunday=0
// --- ISO week date ---
new FE("%G", "2024"),
new FE("%g", "24"),
new FE("%V", "10"),
// --- Hour (24h) ---
new FE("%H", "13"),
new FE("%k", "13"),
new FE("%-H", "13"),
new FE("%_H", "13"),
// --- Hour (12h) ---
new FE("%I", "01"),
new FE("%l", " 1"),
new FE("%-I", "1"),
// --- Minute ---
new FE("%M", "05"),
new FE("%-M", "5"),
// --- Second ---
new FE("%S", "09"),
new FE("%-S", "9"),
new FE("%f", "123456000"),
new FE("%.f", ".123456"),
new FE("%.3f", ".123"),
new FE("%.6f", ".123456"),
new FE("%.9f", ".123456000"),
// --- AM/PM ---
new FE("%p", "PM"),
new FE("%P", "pm"),
// --- Unix timestamp ---
// 2024‑03‑05 13:05:09 UTC → 1709643909
new FE("%s", "1709643909"),
// --- Composite formats ---
new FE("%F", "2024-03-05"),
new FE("%D", "03/05/24"),
new FE("%x", "03/05/24"),
new FE("%T", "13:05:09"),
new FE("%R", "13:05"),
new FE("%X", "13:05:09"),
new FE("%r", "01:05:09 PM"),
// --- Literal percent ---
new FE("%%", "%"),
new FE("%Y%%%m", "2024%03"),
new FE("%H%%%M", "13%05"),
new FE("%H%%M", "13%M"),
};

StringBuilder builder = new StringBuilder();
for (FE r: tests) {
builder.append("SELECT format_timestamp('")
.append(r.format)
.append("', TIMESTAMP '2024-03-05 13:05:09.123456');\n")
.append(" r\n")
.append("------\n")
.append(" ").append(r.expected).append("\n")
.append("(1 row)\n\n");
}
this.qs(builder.toString());
}

@Test
public void testAllFormat() {
// Test all format flags that can be applied to a DATE.
Expand Down
Loading