This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 5630dc98f24 branch-4.0: [Feature](function) Support function
TIME_FORMAT (#58592) (#59704)
5630dc98f24 is described below
commit 5630dc98f242c7638d5888be0fe0b069ea0be41b
Author: linrrarity <[email protected]>
AuthorDate: Mon Jan 12 10:14:21 2026 +0800
branch-4.0: [Feature](function) Support function TIME_FORMAT (#58592)
(#59704)
pick: https://github.com/apache/doris/pull/58592
doc: https://github.com/apache/doris-website/pull/3135
The TIME_FORMAT function is used to convert a time value into a string
according to the specified format string. It supports formatting for
TIME and DATETIME types, and the output is a string that conforms to the
format requirements.
```sql
SELECT TIME_FORMAT('2025-11-25 15:30:45', '%Y-%m-%d %H:%i:%s') AS
date_with_time;
```
```text
+---------------------+
| date_with_time |
+---------------------+
| 0000-00-00 15:30:45 |
+---------------------+
```
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
be/src/vec/functions/date_time_transforms.h | 71 +++
.../function_datetime_string_to_string.cpp | 6 +
be/src/vec/runtime/time_value.h | 16 +
be/src/vec/runtime/vdatetime_value.cpp | 505 ++++++++++++---------
be/src/vec/runtime/vdatetime_value.h | 21 +
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../executable/DateTimeExtractAndTransform.java | 33 +-
.../expressions/functions/scalar/DateFormat.java | 4 +-
.../scalar/{DateFormat.java => TimeFormat.java} | 33 +-
.../trees/expressions/literal/TimeV2Literal.java | 4 +
.../expressions/visitor/ScalarFunctionVisitor.java | 5 +
.../doris/nereids/util/DateTimeFormatterUtils.java | 425 +++++++++++++++++
.../nereids/rules/expression/FoldConstantTest.java | 12 +-
.../sql-functions/doc_date_functions_test.out | 107 +++++
.../test_function_signature_all_types.groovy | 2 +-
.../sql-functions/doc_date_functions_test.groovy | 164 ++++++-
16 files changed, 1146 insertions(+), 264 deletions(-)
diff --git a/be/src/vec/functions/date_time_transforms.h
b/be/src/vec/functions/date_time_transforms.h
index f494750cf24..8954e664988 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -39,6 +39,7 @@
#include "vec/data_types/data_type_decimal.h"
#include "vec/data_types/data_type_string.h"
#include "vec/functions/date_format_type.h"
+#include "vec/runtime/time_value.h"
#include "vec/runtime/vdatetime_value.h"
#include "vec/utils/util.hpp"
@@ -427,6 +428,76 @@ struct FromUnixTimeDecimalImpl {
}
};
+template <PrimitiveType ArgPType>
+class FunctionTimeFormat : public IFunction {
+public:
+ using ArgColType = typename PrimitiveTypeTraits<ArgPType>::ColumnType;
+ using ArgCppType = typename PrimitiveTypeTraits<ArgPType>::CppType;
+
+ static constexpr auto name = "time_format";
+ String get_name() const override { return name; }
+ static FunctionPtr create() { return
std::make_shared<FunctionTimeFormat>(); }
+ DataTypes get_variadic_argument_types_impl() const override {
+ return {std::make_shared<typename
PrimitiveTypeTraits<ArgPType>::DataType>(),
+ std::make_shared<vectorized::DataTypeString>()};
+ }
+ DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments)
const override {
+ return make_nullable(std::make_shared<DataTypeString>());
+ }
+ size_t get_number_of_arguments() const override { return 2; }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ uint32_t result, size_t input_rows_count) const
override {
+ auto res_col = ColumnString::create();
+ ColumnString::Chars& res_chars = res_col->get_chars();
+ ColumnString::Offsets& res_offsets = res_col->get_offsets();
+
+ auto null_map = ColumnUInt8::create();
+ auto& null_map_data = null_map->get_data();
+ null_map_data.resize_fill(input_rows_count, 0);
+
+ res_offsets.reserve(input_rows_count);
+
+ ColumnPtr arg_col[2];
+ bool is_const[2];
+ for (size_t i = 0; i < 2; ++i) {
+ const ColumnPtr& col = block.get_by_position(arguments[i]).column;
+ std::tie(arg_col[i], is_const[i]) = unpack_if_const(col);
+ }
+
+ const auto* datetime_col = assert_cast<const
ArgColType*>(arg_col[0].get());
+ const auto* format_col = assert_cast<const
ColumnString*>(arg_col[1].get());
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ const auto& datetime_val =
datetime_col->get_element(index_check_const(i, is_const[0]));
+ StringRef format = format_col->get_data_at(index_check_const(i,
is_const[1]));
+ TimeValue::TimeType time = get_time_value(datetime_val);
+
+ char buf[100 + SAFE_FORMAT_STRING_MARGIN];
+ if (!TimeValue::to_format_string_conservative(format.data,
format.size, buf,
+ 100 +
SAFE_FORMAT_STRING_MARGIN, time)) {
+ null_map_data[i] = 1;
+ res_offsets.push_back(res_chars.size());
+ continue;
+ }
+ res_chars.insert(buf, buf + strlen(buf));
+ res_offsets.push_back(res_chars.size());
+ }
+ block.replace_by_position(result,
+ ColumnNullable::create(std::move(res_col),
std::move(null_map)));
+ return Status::OK();
+ }
+
+private:
+ TimeValue::TimeType get_time_value(const ArgCppType& datetime_val) const {
+ if constexpr (ArgPType == PrimitiveType::TYPE_TIMEV2) {
+ return static_cast<TimeValue::TimeType>(datetime_val);
+ } else {
+ return TimeValue::make_time(datetime_val.hour(),
datetime_val.minute(),
+ datetime_val.second(),
datetime_val.microsecond());
+ }
+ }
+};
+
#include "common/compile_check_end.h"
} // namespace doris::vectorized
diff --git a/be/src/vec/functions/function_datetime_string_to_string.cpp
b/be/src/vec/functions/function_datetime_string_to_string.cpp
index 53a472f3179..72fc5edd5a7 100644
--- a/be/src/vec/functions/function_datetime_string_to_string.cpp
+++ b/be/src/vec/functions/function_datetime_string_to_string.cpp
@@ -37,6 +37,9 @@ using FunctionFromUnixTimeNewDecimalOneArg =
FunctionDateTimeStringToString<FromUnixTimeDecimalImpl<false>>;
using FunctionFromUnixTimeNewDecimalTwoArg =
FunctionDateTimeStringToString<FromUnixTimeDecimalImpl<true>>;
+using FunctionTimeFormatDate = FunctionTimeFormat<TYPE_DATEV2>;
+using FunctionTimeFormatDateTime = FunctionTimeFormat<TYPE_DATETIMEV2>;
+using FunctionTimeFormatTime = FunctionTimeFormat<TYPE_TIMEV2>;
void register_function_date_time_string_to_string(SimpleFunctionFactory&
factory) {
factory.register_function<FunctionDateFormatV2>();
@@ -47,6 +50,9 @@ void
register_function_date_time_string_to_string(SimpleFunctionFactory& factory
factory.register_function<FunctionFromUnixTimeNewDecimalOneArg>();
factory.register_function<FunctionFromUnixTimeNewDecimalTwoArg>();
factory.register_function<FunctionDateTimeV2DateFormat>();
+ factory.register_function<FunctionTimeFormatDate>();
+ factory.register_function<FunctionTimeFormatDateTime>();
+ factory.register_function<FunctionTimeFormatTime>();
}
} // namespace doris::vectorized
diff --git a/be/src/vec/runtime/time_value.h b/be/src/vec/runtime/time_value.h
index 10c1a12bb03..340b4da42b3 100644
--- a/be/src/vec/runtime/time_value.h
+++ b/be/src/vec/runtime/time_value.h
@@ -28,6 +28,7 @@
#include "runtime/define_primitive_type.h"
#include "runtime/primitive_type.h"
#include "util/date_func.h"
+#include "vec/runtime/vdatetime_value.h"
namespace doris {
#include "common/compile_check_begin.h"
@@ -150,6 +151,21 @@ public:
}
static bool valid(double time) { return time <= MAX_TIME && time >=
-MAX_TIME; }
+
+ static bool to_format_string_conservative(const char* format, size_t len,
char* to,
+ size_t max_valid_length,
TimeType time) {
+ // If time is negative, we here only add a '-' to the begining of res
+ // This behavior is consistent with MySQL
+ if (time < 0) {
+ memcpy(to, "-", 1);
+ ++to;
+ time = -time;
+ }
+
+ return DatetimeValueUtil::to_format_string_without_check<true>(
+ format, len, to, max_valid_length, 0, 0, 0,
TimeValue::hour(time),
+ TimeValue::minute(time), TimeValue::second(time),
TimeValue::microsecond(time));
+ }
};
} // namespace doris
#include "common/compile_check_end.h"
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 3b77981e3ea..da3b2831289 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -2982,6 +2982,187 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
if (is_invalid(year(), month(), day(), hour(), minute(), second(),
microsecond())) {
return false;
}
+
+ return DatetimeValueUtil::to_format_string_without_check<false>(
+ format, len, to, max_valid_length, this->year(), this->month(),
this->day(),
+ this->hour(), this->minute(), this->second(), this->microsecond());
+}
+
+template <typename T>
+int64_t DateV2Value<T>::standardize_timevalue(int64_t value) {
+ if (value <= 0) {
+ return 0;
+ }
+ if (value >= 10000101000000L) {
+ // 9999-99-99 99:99:99
+ if (value > 99999999999999L) {
+ return 0;
+ }
+
+ // between 1000-01-01 00:00:00L and 9999-99-99 99:99:99
+ // all digits exist.
+ return value;
+ }
+ // 2000-01-01
+ if (value < 101) {
+ return 0;
+ }
+ // two digits year. 2000 ~ 2069
+ if (value <= (YY_PART_YEAR - 1) * 10000L + 1231L) {
+ return (value + 20000000L) * 1000000L;
+ }
+ // two digits year, invalid date
+ if (value < YY_PART_YEAR * 10000L + 101) {
+ return 0;
+ }
+ // two digits year. 1970 ~ 1999
+ if (value <= 991231L) {
+ return (value + 19000000L) * 1000000L;
+ }
+ if (value < 10000101) {
+ return 0;
+ }
+ // four digits years without hour.
+ if (value <= 99991231L) {
+ return value * 1000000L;
+ }
+ // below 0000-01-01
+ if (value < 101000000) {
+ return 0;
+ }
+
+ // below is with datetime, must have hh:mm:ss
+ // 2000 ~ 2069
+ if (value <= (YY_PART_YEAR - 1) * 10000000000L + 1231235959L) {
+ return value + 20000000000000L;
+ }
+ if (value < YY_PART_YEAR * 10000000000L + 101000000L) {
+ return 0;
+ }
+ // 1970 ~ 1999
+ if (value <= 991231235959L) {
+ return value + 19000000000000L;
+ }
+ return value;
+}
+
+template <typename T>
+bool DateV2Value<T>::from_date_int64(int64_t value) {
+ value = standardize_timevalue(value);
+ if (value <= 0) {
+ return false;
+ }
+ uint64_t date = value / 1000000;
+
+ auto [year, month, day, hour, minute, second] = std::tuple {0, 0, 0, 0, 0,
0};
+ year = date / 10000;
+ date %= 10000;
+ month = date / 100;
+ day = date % 100;
+
+ if constexpr (is_datetime) {
+ uint64_t time = value % 1000000;
+ hour = time / 10000;
+ time %= 10000;
+ minute = time / 100;
+ second = time % 100;
+ return check_range_and_set_time(year, month, day, hour, minute,
second, 0);
+ } else {
+ return check_range_and_set_time(year, month, day, 0, 0, 0, 0);
+ }
+}
+
+// An ISO week-numbering year (also called ISO year informally) has 52 or 53
full weeks. That is 364 or 371 days instead of the usual 365 or 366 days. These
53-week years occur on all years that have Thursday as 1 January and on leap
years that start on Wednesday. The extra week is sometimes referred to as a
leap week, although ISO 8601 does not use this term.
https://en.wikipedia.org/wiki/ISO_week_date
+template <typename T>
+uint16_t DateV2Value<T>::year_of_week() const {
+ constexpr uint8_t THURSDAY = 3;
+
+ if (date_v2_value_.month_ == 1) {
+ constexpr uint8_t MAX_DISTANCE_WITH_THURSDAY = 6 - THURSDAY;
+ if (date_v2_value_.day_ <= MAX_DISTANCE_WITH_THURSDAY) {
+ auto weekday = calc_weekday(daynr(), false);
+ // if the current day is after Thursday and Thursday is in the
previous year, return the previous year
+ return date_v2_value_.year_ -
+ (weekday > THURSDAY && weekday - THURSDAY >
date_v2_value_.day_ - 1);
+ }
+ } else if (date_v2_value_.month_ == 12) {
+ constexpr uint8_t MAX_DISTANCE_WITH_THURSDAY = THURSDAY - 0;
+ if (S_DAYS_IN_MONTH[12] - date_v2_value_.day_ <=
MAX_DISTANCE_WITH_THURSDAY) {
+ auto weekday = calc_weekday(daynr(), false);
+ // if the current day is before Thursday and Thursday is in the
next year, return the next year
+ return date_v2_value_.year_ +
+ (weekday < THURSDAY &&
+ (THURSDAY - weekday) > S_DAYS_IN_MONTH[12] -
date_v2_value_.day_);
+ }
+ }
+ return date_v2_value_.year_;
+}
+
+template <typename T>
+uint8_t DateV2Value<T>::calc_week(const uint32_t& day_nr, const uint16_t& year,
+ const uint8_t& month, const uint8_t& day,
uint8_t mode,
+ uint16_t* to_year, bool disable_lut) {
+ if (year == 0) [[unlikely]] {
+ *to_year = 0;
+ return 0;
+ }
+ if (config::enable_time_lut && !disable_lut && mode == 3 && year >= 1950
&& year < 2030) {
+ return doris::TimeLUT::GetImplement()
+ ->week_of_year_table[year - doris::LUT_START_YEAR][month -
1][day - 1];
+ }
+ // mode=4 is used for week()
+ if (config::enable_time_lut && !disable_lut && mode == 4 && year >= 1950
&& year < 2030) {
+ return doris::TimeLUT::GetImplement()
+ ->week_table[year - doris::LUT_START_YEAR][month - 1][day - 1];
+ }
+ bool monday_first = mode & WEEK_MONDAY_FIRST;
+ bool week_year = mode & WEEK_YEAR;
+ bool first_weekday = mode & WEEK_FIRST_WEEKDAY;
+ uint64_t daynr_first_day = doris::calc_daynr(year, 1, 1);
+ uint8_t weekday_first_day = doris::calc_weekday(daynr_first_day,
!monday_first);
+
+ int days = 0;
+ *to_year = year;
+
+ // Check weather the first days of this year belongs to last year
+ if (month == 1 && day <= (7 - weekday_first_day)) {
+ if (!week_year && ((first_weekday && weekday_first_day != 0) ||
+ (!first_weekday && weekday_first_day > 3))) {
+ return 0;
+ }
+ (*to_year)--;
+ week_year = true;
+ daynr_first_day -= (days = doris::calc_days_in_year(*to_year));
+ weekday_first_day = (weekday_first_day + 53 * 7 - days) % 7;
+ }
+
+ // How many days since first week
+ if ((first_weekday && weekday_first_day != 0) || (!first_weekday &&
weekday_first_day > 3)) {
+ // days in new year belongs to last year.
+ days = day_nr - (daynr_first_day + (7 - weekday_first_day));
+ } else {
+ // days in new year belongs to this year.
+ days = day_nr - (daynr_first_day - weekday_first_day);
+ }
+
+ if (week_year && days >= 52 * 7) {
+ weekday_first_day = (weekday_first_day +
doris::calc_days_in_year(*to_year)) % 7;
+ if ((first_weekday && weekday_first_day == 0) ||
+ (!first_weekday && weekday_first_day <= 3)) {
+ // Belong to next year.
+ (*to_year)++;
+ return 1;
+ }
+ }
+
+ return days / 7 + 1;
+}
+
+template <bool only_time>
+bool DatetimeValueUtil::to_format_string_without_check(const char* format,
size_t len, char* to,
+ size_t
max_valid_length, int16_t year,
+ int8_t month, int8_t
day, int hour,
+ int minute, int second,
int ms) {
char* const begin = to; // to check written bytes
char buf[64];
char* pos = nullptr;
@@ -3003,79 +3184,85 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
switch (ch = *ptr++) {
case 'y':
// Year, numeric (two digits)
- to = write_two_digits_to_string(this->year() % 100, to);
+ to = write_two_digits_to_string(year % 100, to);
cursor += 2;
pos = cursor;
break;
case 'Y':
// Year, numeric, four digits
- to = write_four_digits_to_string(this->year(), to);
+ to = write_four_digits_to_string(year, to);
cursor += 4;
pos = cursor;
break;
case 'd':
// Day of month (00...31)
- to = write_two_digits_to_string(this->day(), to);
+ to = write_two_digits_to_string(day, to);
cursor += 2;
pos = cursor;
break;
case 'H':
- to = write_two_digits_to_string(this->hour(), to);
- cursor += 2;
+ // Hour (00...838)
+ pos = int_to_str(hour, cursor);
+ to = append_with_prefix(cursor, static_cast<int>(pos - cursor),
'0', 2, to);
+ cursor += (pos - cursor);
pos = cursor;
break;
case 'i':
// Minutes, numeric (00..59)
- to = write_two_digits_to_string(this->minute(), to);
+ to = write_two_digits_to_string(minute, to);
cursor += 2;
pos = cursor;
break;
case 'm':
- to = write_two_digits_to_string(this->month(), to);
+ to = write_two_digits_to_string(month, to);
cursor += 2;
pos = cursor;
break;
case 'h':
case 'I':
// Hour (01..12)
- to = write_two_digits_to_string((this->hour() % 24 + 11) % 12 + 1,
to);
+ to = write_two_digits_to_string((hour % 24 + 11) % 12 + 1, to);
cursor += 2;
pos = cursor;
break;
case 's':
case 'S':
// Seconds (00..59)
- to = write_two_digits_to_string(this->second(), to);
+ to = write_two_digits_to_string(second, to);
cursor += 2;
pos = cursor;
break;
case 'a':
// Abbreviated weekday name
- if (this->year() == 0 && this->month() == 0) {
+ if constexpr (only_time) {
return false;
}
- to = append_string(s_ab_day_name[weekday()], to);
+ to = append_string(s_ab_day_name[calc_weekday(calc_daynr(year,
month, day), false)],
+ to);
break;
case 'b':
// Abbreviated month name
- if (this->month() == 0) {
+ if constexpr (only_time) {
return false;
}
- to = append_string(s_ab_month_name[this->month()], to);
+ to = append_string(s_ab_month_name[month], to);
break;
case 'c':
// Month, numeric (0...12)
- pos = int_to_str(this->month(), cursor);
+ pos = int_to_str(month, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
break;
case 'D':
// Day of the month with English suffix (0th, 1st, ...)
- pos = int_to_str(this->day(), cursor);
+ if constexpr (only_time) {
+ return false;
+ }
+ pos = int_to_str(day, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
- if (this->day() >= 10 && this->day() <= 19) {
+ if (day >= 10 && day <= 19) {
to = append_string("th", to);
} else {
- switch (this->day() % 10) {
+ switch (day % 10) {
case 1:
to = append_string("st", to);
break;
@@ -3093,39 +3280,42 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
break;
case 'e':
// Day of the month, numeric (0..31)
- pos = int_to_str(this->day(), cursor);
+ pos = int_to_str(day, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
break;
case 'f':
// Microseconds (000000..999999)
- pos = int_to_str(this->microsecond(), cursor);
+ pos = int_to_str(ms, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 6, to);
break;
case 'j':
// Day of year (001..366)
- pos = int_to_str(daynr() - doris::calc_daynr(this->year(), 1, 1) +
1, cursor);
+ if constexpr (only_time) {
+ return false;
+ }
+ pos = int_to_str(calc_daynr(year, month, day) - calc_daynr(year,
1, 1) + 1, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 3, to);
break;
case 'k':
- // Hour (0..23)
- pos = int_to_str(this->hour(), cursor);
+ // Hour (0..838)
+ pos = int_to_str(hour, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
break;
case 'l':
// Hour (1..12)
- pos = int_to_str((this->hour() % 24 + 11) % 12 + 1, cursor);
+ pos = int_to_str((hour % 24 + 11) % 12 + 1, cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
break;
case 'M':
// Month name (January..December)
- if (this->month() == 0) {
+ if constexpr (only_time) {
return false;
}
- to = append_string(s_month_name[this->month()], to);
+ to = append_string(s_month_name[month], to);
break;
case 'p':
// AM or PM
- if ((this->hour() % 24) >= 12) {
+ if ((hour % 24) >= 12) {
to = append_string("PM", to);
} else {
to = append_string("AM", to);
@@ -3133,17 +3323,17 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
break;
case 'r': {
// Time, 12-hour (hh:mm:ss followed by AM or PM)
- *to++ = (char)('0' + (((this->hour() + 11) % 12 + 1) / 10));
- *to++ = (char)('0' + (((this->hour() + 11) % 12 + 1) % 10));
+ *to++ = (char)('0' + (((hour + 11) % 12 + 1) / 10));
+ *to++ = (char)('0' + (((hour + 11) % 12 + 1) % 10));
*to++ = ':';
// Minute
- *to++ = (char)('0' + (this->minute() / 10));
- *to++ = (char)('0' + (this->minute() % 10));
+ *to++ = (char)('0' + (minute / 10));
+ *to++ = (char)('0' + (minute % 10));
*to++ = ':';
/* Second */
- *to++ = (char)('0' + (this->second() / 10));
- *to++ = (char)('0' + (this->second() % 10));
- if ((this->hour() % 24) >= 12) {
+ *to++ = (char)('0' + (second / 10));
+ *to++ = (char)('0' + (second % 10));
+ if ((hour % 24) >= 12) {
to = append_string(" PM", to);
} else {
to = append_string(" AM", to);
@@ -3151,66 +3341,89 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
break;
}
case 'T': {
- // Time, 24-hour (hh:mm:ss)
- *to++ = (char)('0' + ((this->hour() % 24) / 10));
- *to++ = (char)('0' + ((this->hour() % 24) % 10));
+ // Time, 24-hour (hh:mm:ss or hhh:mm:ss for TIME type)
+ if (hour < 100) {
+ *to++ = (char)('0' + (hour / 10));
+ *to++ = (char)('0' + (hour % 10));
+ } else {
+ pos = int_to_str(hour, cursor);
+ to = append_with_prefix(cursor, static_cast<int>(pos -
cursor), '0', 2, to);
+ }
*to++ = ':';
// Minute
- *to++ = (char)('0' + (this->minute() / 10));
- *to++ = (char)('0' + (this->minute() % 10));
+ *to++ = (char)('0' + (minute / 10));
+ *to++ = (char)('0' + (minute % 10));
*to++ = ':';
/* Second */
- *to++ = (char)('0' + (this->second() / 10));
- *to++ = (char)('0' + (this->second() % 10));
+ *to++ = (char)('0' + (second / 10));
+ *to++ = (char)('0' + (second % 10));
break;
}
case 'u':
// Week (00..53), where Monday is the first day of the week;
// WEEK() mode 1
- to = write_two_digits_to_string(week(mysql_week_mode(1)), to);
+ if constexpr (only_time) {
+ return false;
+ }
+ to = write_two_digits_to_string(week(year, month, day,
mysql_week_mode(1)), to);
cursor += 2;
pos = cursor;
break;
case 'U':
// Week (00..53), where Sunday is the first day of the week;
// WEEK() mode 0
- to = write_two_digits_to_string(week(mysql_week_mode(0)), to);
+ if constexpr (only_time) {
+ return false;
+ }
+ to = write_two_digits_to_string(week(year, month, day,
mysql_week_mode(0)), to);
cursor += 2;
pos = cursor;
break;
case 'v':
// Week (01..53), where Monday is the first day of the week;
// WEEK() mode 3; used with %x
- to = write_two_digits_to_string(week(mysql_week_mode(3)), to);
+ if constexpr (only_time) {
+ return false;
+ }
+ to = write_two_digits_to_string(week(year, month, day,
mysql_week_mode(3)), to);
cursor += 2;
pos = cursor;
break;
case 'V':
// Week (01..53), where Sunday is the first day of the week;
// WEEK() mode 2; used with %X
- to = write_two_digits_to_string(week(mysql_week_mode(2)), to);
+ if constexpr (only_time) {
+ return false;
+ }
+ to = write_two_digits_to_string(week(year, month, day,
mysql_week_mode(2)), to);
cursor += 2;
pos = cursor;
break;
case 'w':
// Day of the week (0=Sunday..6=Saturday)
- if (this->month() == 0 && this->year() == 0) {
+ if constexpr (only_time) {
return false;
}
- pos = int_to_str(doris::calc_weekday(daynr(), true), cursor);
+ pos = int_to_str(calc_weekday(calc_daynr(year, month, day), true),
cursor);
to = append_with_prefix(cursor, pos - cursor, '0', 1, to);
break;
case 'W':
// Weekday name (Sunday..Saturday)
- to = append_string(s_day_name[weekday()], to);
+ if constexpr (only_time) {
+ return false;
+ }
+ to = append_string(s_day_name[calc_weekday(calc_daynr(year, month,
day), false)], to);
break;
case 'x': {
// Year for the week, where Monday is the first day of the week,
// numeric, four digits; used with %v
- uint16_t year = 0;
- calc_week(this->daynr(), this->year(), this->month(), this->day(),
mysql_week_mode(3),
- &year, true);
- to = write_four_digits_to_string(year, to);
+ if constexpr (only_time) {
+ return false;
+ }
+ uint16_t year_to_write = 0;
+ calc_week(calc_daynr(year, month, day), year, month, day,
mysql_week_mode(3),
+ &year_to_write, true);
+ to = write_four_digits_to_string(year_to_write, to);
cursor += 4;
pos = cursor;
break;
@@ -3218,10 +3431,13 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
case 'X': {
// Year for the week where Sunday is the first day of the week,
// numeric, four digits; used with %V
- uint16_t year = 0;
- calc_week(this->daynr(), this->year(), this->month(), this->day(),
mysql_week_mode(2),
- &year);
- to = write_four_digits_to_string(year, to);
+ if constexpr (only_time) {
+ return false;
+ }
+ uint16_t year_to_write = 0;
+ calc_week(calc_daynr(year, month, day), year, month, day,
mysql_week_mode(2),
+ &year_to_write);
+ to = write_four_digits_to_string(year_to_write, to);
cursor += 4;
pos = cursor;
break;
@@ -3236,174 +3452,9 @@ bool
DateV2Value<T>::to_format_string_conservative(const char* format, size_t le
return true;
}
-template <typename T>
-int64_t DateV2Value<T>::standardize_timevalue(int64_t value) {
- if (value <= 0) {
- return 0;
- }
- if (value >= 10000101000000L) {
- // 9999-99-99 99:99:99
- if (value > 99999999999999L) {
- return 0;
- }
-
- // between 1000-01-01 00:00:00L and 9999-99-99 99:99:99
- // all digits exist.
- return value;
- }
- // 2000-01-01
- if (value < 101) {
- return 0;
- }
- // two digits year. 2000 ~ 2069
- if (value <= (YY_PART_YEAR - 1) * 10000L + 1231L) {
- return (value + 20000000L) * 1000000L;
- }
- // two digits year, invalid date
- if (value < YY_PART_YEAR * 10000L + 101) {
- return 0;
- }
- // two digits year. 1970 ~ 1999
- if (value <= 991231L) {
- return (value + 19000000L) * 1000000L;
- }
- if (value < 10000101) {
- return 0;
- }
- // four digits years without hour.
- if (value <= 99991231L) {
- return value * 1000000L;
- }
- // below 0000-01-01
- if (value < 101000000) {
- return 0;
- }
-
- // below is with datetime, must have hh:mm:ss
- // 2000 ~ 2069
- if (value <= (YY_PART_YEAR - 1) * 10000000000L + 1231235959L) {
- return value + 20000000000000L;
- }
- if (value < YY_PART_YEAR * 10000000000L + 101000000L) {
- return 0;
- }
- // 1970 ~ 1999
- if (value <= 991231235959L) {
- return value + 19000000000000L;
- }
- return value;
-}
-
-template <typename T>
-bool DateV2Value<T>::from_date_int64(int64_t value) {
- value = standardize_timevalue(value);
- if (value <= 0) {
- return false;
- }
- uint64_t date = value / 1000000;
-
- auto [year, month, day, hour, minute, second] = std::tuple {0, 0, 0, 0, 0,
0};
- year = date / 10000;
- date %= 10000;
- month = date / 100;
- day = date % 100;
-
- if constexpr (is_datetime) {
- uint64_t time = value % 1000000;
- hour = time / 10000;
- time %= 10000;
- minute = time / 100;
- second = time % 100;
- return check_range_and_set_time(year, month, day, hour, minute,
second, 0);
- } else {
- return check_range_and_set_time(year, month, day, 0, 0, 0, 0);
- }
-}
-
-// An ISO week-numbering year (also called ISO year informally) has 52 or 53
full weeks. That is 364 or 371 days instead of the usual 365 or 366 days. These
53-week years occur on all years that have Thursday as 1 January and on leap
years that start on Wednesday. The extra week is sometimes referred to as a
leap week, although ISO 8601 does not use this term.
https://en.wikipedia.org/wiki/ISO_week_date
-template <typename T>
-uint16_t DateV2Value<T>::year_of_week() const {
- constexpr uint8_t THURSDAY = 3;
-
- if (date_v2_value_.month_ == 1) {
- constexpr uint8_t MAX_DISTANCE_WITH_THURSDAY = 6 - THURSDAY;
- if (date_v2_value_.day_ <= MAX_DISTANCE_WITH_THURSDAY) {
- auto weekday = calc_weekday(daynr(), false);
- // if the current day is after Thursday and Thursday is in the
previous year, return the previous year
- return date_v2_value_.year_ -
- (weekday > THURSDAY && weekday - THURSDAY >
date_v2_value_.day_ - 1);
- }
- } else if (date_v2_value_.month_ == 12) {
- constexpr uint8_t MAX_DISTANCE_WITH_THURSDAY = THURSDAY - 0;
- if (S_DAYS_IN_MONTH[12] - date_v2_value_.day_ <=
MAX_DISTANCE_WITH_THURSDAY) {
- auto weekday = calc_weekday(daynr(), false);
- // if the current day is before Thursday and Thursday is in the
next year, return the next year
- return date_v2_value_.year_ +
- (weekday < THURSDAY &&
- (THURSDAY - weekday) > S_DAYS_IN_MONTH[12] -
date_v2_value_.day_);
- }
- }
- return date_v2_value_.year_;
-}
-
-template <typename T>
-uint8_t DateV2Value<T>::calc_week(const uint32_t& day_nr, const uint16_t& year,
- const uint8_t& month, const uint8_t& day,
uint8_t mode,
- uint16_t* to_year, bool disable_lut) {
- if (year == 0) [[unlikely]] {
- *to_year = 0;
- return 0;
- }
- if (config::enable_time_lut && !disable_lut && mode == 3 && year >= 1950
&& year < 2030) {
- return doris::TimeLUT::GetImplement()
- ->week_of_year_table[year - doris::LUT_START_YEAR][month -
1][day - 1];
- }
- // mode=4 is used for week()
- if (config::enable_time_lut && !disable_lut && mode == 4 && year >= 1950
&& year < 2030) {
- return doris::TimeLUT::GetImplement()
- ->week_table[year - doris::LUT_START_YEAR][month - 1][day - 1];
- }
- bool monday_first = mode & WEEK_MONDAY_FIRST;
- bool week_year = mode & WEEK_YEAR;
- bool first_weekday = mode & WEEK_FIRST_WEEKDAY;
- uint64_t daynr_first_day = doris::calc_daynr(year, 1, 1);
- uint8_t weekday_first_day = doris::calc_weekday(daynr_first_day,
!monday_first);
-
- int days = 0;
- *to_year = year;
-
- // Check weather the first days of this year belongs to last year
- if (month == 1 && day <= (7 - weekday_first_day)) {
- if (!week_year && ((first_weekday && weekday_first_day != 0) ||
- (!first_weekday && weekday_first_day > 3))) {
- return 0;
- }
- (*to_year)--;
- week_year = true;
- daynr_first_day -= (days = doris::calc_days_in_year(*to_year));
- weekday_first_day = (weekday_first_day + 53 * 7 - days) % 7;
- }
-
- // How many days since first week
- if ((first_weekday && weekday_first_day != 0) || (!first_weekday &&
weekday_first_day > 3)) {
- // days in new year belongs to last year.
- days = day_nr - (daynr_first_day + (7 - weekday_first_day));
- } else {
- // days in new year belongs to this year.
- days = day_nr - (daynr_first_day - weekday_first_day);
- }
-
- if (week_year && days >= 52 * 7) {
- weekday_first_day = (weekday_first_day +
doris::calc_days_in_year(*to_year)) % 7;
- if ((first_weekday && weekday_first_day == 0) ||
- (!first_weekday && weekday_first_day <= 3)) {
- // Belong to next year.
- (*to_year)++;
- return 1;
- }
- }
-
- return days / 7 + 1;
+uint8_t DatetimeValueUtil::week(int16_t year, int8_t month, int8_t day,
uint8_t mode) {
+ uint16_t year_to_write = 0;
+ return calc_week(calc_daynr(year, month, day), year, month, day, mode,
&year_to_write);
}
template <typename T>
@@ -3668,5 +3719,13 @@ template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::MONTH>(
template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::YEAR>();
template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::QUARTER>();
template bool
DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::WEEK>();
+
+template bool DatetimeValueUtil::to_format_string_without_check<false>(const
char*, size_t, char*,
+ size_t,
int16_t, int8_t,
+ int8_t,
int, int, int, int);
+
+template bool DatetimeValueUtil::to_format_string_without_check<true>(const
char*, size_t, char*,
+ size_t,
int16_t, int8_t,
+ int8_t,
int, int, int, int);
#include "common/compile_check_avoid_end.h"
} // namespace doris
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index ffb5da58408..b8fe4067fd3 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -817,6 +817,8 @@ inline const VecDateTimeValue
VecDateTimeValue::DEFAULT_VALUE(false, TYPE_DATETI
template <typename T>
class DateV2Value {
+ friend class DatetimeValueUtil;
+
public:
static constexpr bool is_datetime = std::is_same_v<T, DateTimeV2ValueType>;
using underlying_value = std::conditional_t<is_datetime, uint64_t,
uint32_t>;
@@ -1751,6 +1753,25 @@ inline uint32_t calc_daynr(uint16_t year, uint8_t month,
uint8_t day) {
return delsum + y / 4 - y / 100 + y / 400;
}
+class DatetimeValueUtil {
+public:
+ template <bool only_time>
+ static bool to_format_string_without_check(const char* format, size_t len,
char* to,
+ size_t max_valid_length,
int16_t year, int8_t month,
+ int8_t day, int hour, int
minute, int second,
+ int ms);
+
+private:
+ static uint8_t week(int16_t year, int8_t month, int8_t day, uint8_t mode);
+
+ static uint8_t calc_week(const uint32_t& day_nr, const uint16_t& year,
const uint8_t& month,
+ const uint8_t& day, uint8_t mode, uint16_t*
to_year,
+ bool disable_lut = false) {
+ return DateV2Value<DateTimeV2ValueType>::calc_week(day_nr, year,
month, day, mode, to_year,
+ disable_lut);
+ }
+};
+
template <typename T>
struct DateTraits {};
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index f06ef3e7752..48bb1375e3c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -485,6 +485,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Tan;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tanh;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Time;
import org.apache.doris.nereids.trees.expressions.functions.scalar.TimeDiff;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.TimeFormat;
import org.apache.doris.nereids.trees.expressions.functions.scalar.TimeToSec;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Timestamp;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToBase64;
@@ -1038,6 +1039,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(Tanh.class, "tanh"),
scalar(Time.class, "time"),
scalar(TimeDiff.class, "timediff"),
+ scalar(TimeFormat.class, "time_format"),
scalar(TimeToSec.class, "time_to_sec"),
scalar(Timestamp.class, "timestamp"),
scalar(ToBase64.class, "to_base64"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index ea6551687ba..2c005583c3c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -44,6 +44,7 @@ import org.apache.doris.nereids.types.DateV2Type;
import org.apache.doris.nereids.types.DecimalV3Type;
import org.apache.doris.nereids.types.StringType;
import org.apache.doris.nereids.types.TimeV2Type;
+import org.apache.doris.nereids.util.DateTimeFormatterUtils;
import org.apache.doris.nereids.util.DateUtils;
import org.apache.doris.qe.ConnectContext;
@@ -276,31 +277,37 @@ public class DateTimeExtractAndTransform {
* datetime arithmetic function date-format
*/
@ExecFunction(name = "date_format")
- public static Expression dateFormat(DateV2Literal date, StringLikeLiteral
format) {
+ public static Expression dateFormat(DateTimeV2Literal date,
StringLikeLiteral format) {
if (StringUtils.trim(format.getValue()).length() > 128) {
throw new AnalysisException("The length of format string in
date_format() function should not be greater"
+ " than 128.");
}
- DateTimeV2Literal datetime = new DateTimeV2Literal(date.getYear(),
date.getMonth(), date.getDay(), 0, 0, 0, 0);
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.dateTimeFormatterChecklength(format.getValue(),
datetime).format(
- java.time.LocalDate.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()))));
+ return new
VarcharLiteral(DateTimeFormatterUtils.toFormatStringConservative(date, format,
false));
}
/**
- * datetime arithmetic function date-format
+ * time_format constant folding for time literal.
*/
- @ExecFunction(name = "date_format")
- public static Expression dateFormat(DateTimeV2Literal date,
StringLikeLiteral format) {
+ @ExecFunction(name = "time_format")
+ public static Expression timeFormat(TimeV2Literal time, StringLikeLiteral
format) {
if (StringUtils.trim(format.getValue()).length() > 128) {
- throw new AnalysisException("The length of format string in
date_format() function should not be greater"
+ throw new AnalysisException("The length of format string in
time_format() function should not be greater"
+ " than 128.");
}
- format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.dateTimeFormatterChecklength(format.getValue(),
date).format(
- java.time.LocalDateTime.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()),
- ((int) date.getHour()), ((int) date.getMinute()),
((int) date.getSecond()),
- ((int) date.getMicroSecond() * 1000))));
+ return new
VarcharLiteral(DateTimeFormatterUtils.toFormatStringConservative(time, format));
+ }
+
+ /**
+ * time_format constant folding for datetimev2 literal.
+ */
+ @ExecFunction(name = "time_format")
+ public static Expression timeFormat(DateTimeV2Literal dateTime,
StringLikeLiteral format) {
+ if (StringUtils.trim(format.getValue()).length() > 128) {
+ throw new AnalysisException("The length of format string in
time_format() function should not be greater"
+ + " than 128.");
+ }
+ return new
VarcharLiteral(DateTimeFormatterUtils.toFormatStringConservative(dateTime,
format, true));
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
index 3da486abbed..6c26a94f635 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
@@ -28,7 +28,6 @@ import
org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeV2Type;
-import org.apache.doris.nereids.types.DateV2Type;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.util.DateUtils;
@@ -45,8 +44,7 @@ public class DateFormat extends ScalarFunction
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).args(DateTimeV2Type.WILDCARD,
- VarcharType.SYSTEM_DEFAULT),
-
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).args(DateV2Type.INSTANCE,
VarcharType.SYSTEM_DEFAULT));
+ VarcharType.SYSTEM_DEFAULT));
/**
* constructor with 2 arguments.
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimeFormat.java
similarity index 73%
copy from
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
copy to
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimeFormat.java
index 3da486abbed..6c96683e29d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateFormat.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/TimeFormat.java
@@ -19,16 +19,15 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.Monotonic;
-import
org.apache.doris.nereids.trees.expressions.functions.PropagateNullLiteral;
-import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeV2Type;
-import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.TimeV2Type;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.util.DateUtils;
@@ -38,25 +37,27 @@ import com.google.common.collect.ImmutableList;
import java.util.List;
/**
- * ScalarFunction 'date_format'. This class is generated by GenerateFunction.
+ * ScalarFunction 'time_format'.
*/
-public class DateFormat extends ScalarFunction
- implements BinaryExpression, ExplicitlyCastableSignature,
PropagateNullable, PropagateNullLiteral, Monotonic {
+public class TimeFormat extends ScalarFunction
+ implements BinaryExpression, ExplicitlyCastableSignature,
AlwaysNullable, Monotonic {
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).args(DateTimeV2Type.WILDCARD,
- VarcharType.SYSTEM_DEFAULT),
-
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).args(DateV2Type.INSTANCE,
VarcharType.SYSTEM_DEFAULT));
+ FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT)
+ .args(TimeV2Type.WILDCARD, VarcharType.SYSTEM_DEFAULT),
+ FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT)
+ .args(DateTimeV2Type.WILDCARD, VarcharType.SYSTEM_DEFAULT)
+ );
/**
* constructor with 2 arguments.
*/
- public DateFormat(Expression arg0, Expression arg1) {
- super("date_format", arg0, arg1);
+ public TimeFormat(Expression arg0, Expression arg1) {
+ super("time_format", arg0, arg1);
}
/** constructor for withChildren and reuse signature */
- private DateFormat(ScalarFunctionParams functionParams) {
+ private TimeFormat(ScalarFunctionParams functionParams) {
super(functionParams);
}
@@ -64,9 +65,9 @@ public class DateFormat extends ScalarFunction
* withChildren.
*/
@Override
- public DateFormat withChildren(List<Expression> children) {
+ public TimeFormat withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() == 2);
- return new DateFormat(getFunctionParams(children));
+ return new TimeFormat(getFunctionParams(children));
}
@Override
@@ -76,7 +77,7 @@ public class DateFormat extends ScalarFunction
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
- return visitor.visitDateFormat(this, context);
+ return visitor.visitTimeFormat(this, context);
}
@Override
@@ -101,6 +102,6 @@ public class DateFormat extends ScalarFunction
@Override
public Expression withConstantArgs(Expression literal) {
- return new DateFormat(literal, child(1));
+ return new TimeFormat(literal, child(1));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimeV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimeV2Literal.java
index 0d34d75344e..7a1201ccf5b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimeV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimeV2Literal.java
@@ -391,4 +391,8 @@ public class TimeV2Literal extends Literal {
public String computeToSql() {
return "'" + getStringValue() + "'";
}
+
+ public boolean isNegative() {
+ return negative;
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index 7f70a6bf586..122450fe1b8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -488,6 +488,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Tan;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tanh;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Time;
import org.apache.doris.nereids.trees.expressions.functions.scalar.TimeDiff;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.TimeFormat;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Timestamp;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToBase64;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ToBase64Binary;
@@ -2315,6 +2316,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(timeDiff, context);
}
+ default R visitTimeFormat(TimeFormat timeFormat, C context) {
+ return visitScalarFunction(timeFormat, context);
+ }
+
default R visitTimestamp(Timestamp timestamp, C context) {
return visitScalarFunction(timestamp, context);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateTimeFormatterUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateTimeFormatterUtils.java
index dd342820343..0e47a8f2915 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateTimeFormatterUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateTimeFormatterUtils.java
@@ -17,6 +17,11 @@
package org.apache.doris.nereids.util;
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimeV2Literal;
+
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.ResolverStyle;
@@ -106,4 +111,424 @@ public class DateTimeFormatterUtils {
.append(TIME_FORMATTER)
.append(ZONE_FORMATTER)
.toFormatter().withResolverStyle(ResolverStyle.STRICT);
+
+ private static final int WEEK_MONDAY_FIRST = 1;
+ private static final int WEEK_YEAR = 2;
+ private static final int WEEK_FIRST_WEEKDAY = 4;
+
+ private static final int MAX_FORMAT_RESULT_LENGTH = 100;
+ private static final int SAFE_FORMAT_STRING_MARGIN = 12;
+ private static final int MAX_FORMAT_STRING_LENGTH = 128;
+
+ private static final String[] ABBR_MONTH_NAMES = {
+ "", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
"Oct", "Nov", "Dec"
+ };
+
+ private static final String[] MONTH_NAMES = {
+ "", "January", "February", "March", "April", "May", "June",
"July", "August", "September",
+ "October", "November", "December"
+ };
+
+ private static final String[] ABBR_DAY_NAMES = {
+ "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"
+ };
+
+ private static final String[] DAY_NAMES = {
+ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
"Saturday", "Sunday"
+ };
+
+ private static void appendTwoDigits(StringBuilder builder, int value) {
+ builder.append((char) ('0' + (value / 10) % 10));
+ builder.append((char) ('0' + (value % 10)));
+ }
+
+ /**
+ * Conservative implementation of DATE_FORMAT/TIME_FORMAT for datetime
literals
+ * used in constant folding.
+ *
+ * @param datetime datetime literal to format
+ * @param format format pattern
+ * @param isTimeFormat true when invoked via time_format, false for
date_format
+ * @return formatted string or null when pattern requires missing date
fields
+ */
+ public static String toFormatStringConservative(DateTimeV2Literal
datetime, StringLikeLiteral format,
+ boolean isTimeFormat) {
+ int year = isTimeFormat ? 0 : (int) datetime.getYear();
+ int month = isTimeFormat ? 0 : (int) datetime.getMonth();
+ int day = isTimeFormat ? 0 : (int) datetime.getDay();
+ int hour = (int) datetime.getHour();
+ int minute = (int) datetime.getMinute();
+ int second = (int) datetime.getSecond();
+ int microsecond = (int) datetime.getMicroSecond();
+
+ String pattern = trimFormat(format.getValue());
+ return formatTemporalLiteral(year, month, day, hour, minute, second,
microsecond, pattern);
+ }
+
+ /**
+ * Conservative implementation of TIME_FORMAT for time literals used in
constant
+ * folding.
+ *
+ * @param time time literal to format
+ * @param format format pattern
+ * @return formatted string with sign preserved; null when pattern
requires date
+ * fields
+ */
+ public static String toFormatStringConservative(TimeV2Literal time,
StringLikeLiteral format) {
+ String pattern = trimFormat(format.getValue());
+ String res = formatTemporalLiteral(0, 0, 0, time.getHour(),
time.getMinute(),
+ time.getSecond(), time.getMicroSecond(), pattern);
+ if (time.isNegative()) {
+ res = "-" + res;
+ }
+ return res;
+ }
+
+ private static int calcWeekNumber(int year, int month, int day, int mode) {
+ int[] weekYear = new int[1];
+ return calcWeekNumberAndYear(year, month, day, mode, weekYear);
+ }
+
+ private static int calcWeekNumberAndYear(int year, int month, int day, int
mode, int[] toYear) {
+ return calcWeekInternal(calcDayNr(year, month, day), year, month, day,
mode, toYear);
+ }
+
+ private static int calcWeekInternal(long dayNr, int year, int month, int
day, int mode, int[] toYear) {
+ if (year == 0) {
+ toYear[0] = 0;
+ return 0;
+ }
+ boolean mondayFirst = (mode & WEEK_MONDAY_FIRST) != 0;
+ boolean weekYear = (mode & WEEK_YEAR) != 0;
+ boolean firstWeekday = (mode & WEEK_FIRST_WEEKDAY) != 0;
+
+ long daynrFirstDay = calcDayNr(year, 1, 1);
+ int weekdayFirstDay = calcWeekday(daynrFirstDay, !mondayFirst);
+
+ toYear[0] = year;
+
+ if (month == 1 && day <= (7 - weekdayFirstDay)) {
+ if (!weekYear && ((firstWeekday && weekdayFirstDay != 0) ||
(!firstWeekday && weekdayFirstDay > 3))) {
+ return 0;
+ }
+ toYear[0]--;
+ weekYear = true;
+ int days = calcDaysInYear(toYear[0]);
+ daynrFirstDay -= days;
+ weekdayFirstDay = (weekdayFirstDay + 53 * 7 - days) % 7;
+ }
+
+ int days;
+ if ((firstWeekday && weekdayFirstDay != 0) || (!firstWeekday &&
weekdayFirstDay > 3)) {
+ days = (int) (dayNr - (daynrFirstDay + (7 - weekdayFirstDay)));
+ } else {
+ days = (int) (dayNr - (daynrFirstDay - weekdayFirstDay));
+ }
+
+ if (weekYear && days >= 52 * 7) {
+ weekdayFirstDay = (weekdayFirstDay + calcDaysInYear(toYear[0])) %
7;
+ if ((firstWeekday && weekdayFirstDay == 0) || (!firstWeekday &&
weekdayFirstDay <= 3)) {
+ toYear[0]++;
+ return 1;
+ }
+ }
+
+ return days / 7 + 1;
+ }
+
+ private static int mysqlWeekMode(int mode) {
+ mode &= 7;
+ if ((mode & WEEK_MONDAY_FIRST) == 0) {
+ mode ^= WEEK_FIRST_WEEKDAY;
+ }
+ return mode;
+ }
+
+ private static int calcWeekday(long dayNr, boolean sundayFirst) {
+ return (int) ((dayNr + 5 + (sundayFirst ? 1 : 0)) % 7);
+ }
+
+ private static long calcDayNr(int year, int month, int day) {
+ // Align with BE/MySQL: Monday = 0 when sundayFirst=false in
calcWeekday.
+ if (year == 0 && month == 0) {
+ return 0;
+ }
+ if (year == 0 && month == 1 && day == 1) {
+ return 1;
+ }
+
+ long y = year;
+ long delsum = 365 * y + 31L * (month - 1) + day;
+ if (month <= 2) {
+ y--;
+ } else {
+ delsum -= (month * 4 + 23) / 10;
+ }
+ return delsum + y / 4 - y / 100 + y / 400;
+ }
+
+ private static int calcDaysInYear(int year) {
+ return isLeap(year) ? 366 : 365;
+ }
+
+ private static boolean isLeap(int year) {
+ return (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);
+ }
+
+ private static void appendFourDigits(StringBuilder builder, int value) {
+ if (value >= 1000 && value <= 9999) {
+ builder.append(value);
+ return;
+ }
+ appendWithPad(builder, value, 4, '0');
+ }
+
+ private static void appendWithPad(StringBuilder builder, int value, int
targetLength, char padChar) {
+ String str = Integer.toString(Math.abs(value));
+ for (int i = str.length(); i < targetLength; i++) {
+ builder.append(padChar);
+ }
+ builder.append(str);
+ }
+
+ // MySQL-compatible time_format for TIME/DATE/DATETIME literals.
+ private static String formatTemporalLiteral(int year, int month, int day,
int hour, int minute,
+ int second, int microsecond, String pattern) {
+ StringBuilder builder = new StringBuilder(pattern.length() + 16);
+
+ for (int i = 0; i < pattern.length(); i++) {
+ char c = pattern.charAt(i);
+ if (c != '%' || i == pattern.length() - 1) {
+ builder.append(c);
+ continue;
+ }
+
+ char spec = pattern.charAt(++i);
+ switch (spec) {
+ case 'y':
+ appendTwoDigits(builder, year % 100);
+ break;
+ case 'Y':
+ appendFourDigits(builder, year);
+ break;
+ case 'd':
+ appendTwoDigits(builder, day);
+ break;
+ case 'H':
+ if (hour < 100) {
+ appendTwoDigits(builder, hour);
+ } else {
+ appendWithPad(builder, hour, 2, '0');
+ }
+ break;
+ case 'i':
+ appendTwoDigits(builder, minute);
+ break;
+ case 'm':
+ appendTwoDigits(builder, month);
+ break;
+ case 'h':
+ case 'I': {
+ int hour12 = (hour % 24 + 11) % 12 + 1;
+ appendTwoDigits(builder, hour12);
+ break;
+ }
+ case 's':
+ case 'S':
+ appendTwoDigits(builder, second);
+ break;
+ case 'a':
+ if (month == 0 || day == 0 || year == 0) {
+ return null;
+ }
+ builder.append(ABBR_DAY_NAMES[calcWeekday(calcDayNr(year,
month, day), false)]);
+ break;
+ case 'b':
+ if (month == 0) {
+ return null;
+ }
+ builder.append(ABBR_MONTH_NAMES[month]);
+ break;
+ case 'c': {
+ String str = Integer.toString(month);
+ if (str.length() < 1) {
+ builder.append('0');
+ }
+ builder.append(str);
+ break;
+ }
+ case 'D':
+ if (month == 0) {
+ return null;
+ }
+ builder.append(day);
+ if (day >= 10 && day <= 19) {
+ builder.append("th");
+ } else {
+ switch (day % 10) {
+ case 1:
+ builder.append("st");
+ break;
+ case 2:
+ builder.append("nd");
+ break;
+ case 3:
+ builder.append("rd");
+ break;
+ default:
+ builder.append("th");
+ break;
+ }
+ }
+ break;
+ case 'e': {
+ String str = Integer.toString(day);
+ if (str.length() < 1) {
+ builder.append('0');
+ }
+ builder.append(str);
+ break;
+ }
+ case 'f':
+ appendWithPad(builder, microsecond, 6, '0');
+ break;
+ case 'j':
+ if (month == 0 || day == 0) {
+ return null;
+ }
+ int dayOfYear = (int) (calcDayNr(year, month, day) -
calcDayNr(year, 1, 1) + 1);
+ appendWithPad(builder, dayOfYear, 3, '0');
+ break;
+ case 'k': {
+ String str = Integer.toString(hour);
+ if (str.length() < 1) {
+ builder.append('0');
+ }
+ builder.append(str);
+ break;
+ }
+ case 'l': {
+ int hour12 = (hour % 24 + 11) % 12 + 1;
+ String str = Integer.toString(hour12);
+ if (str.length() < 1) {
+ builder.append('0');
+ }
+ builder.append(str);
+ break;
+ }
+ case 'M':
+ if (month == 0) {
+ return null;
+ }
+ builder.append(MONTH_NAMES[month]);
+ break;
+ case 'p':
+ builder.append((hour % 24) >= 12 ? "PM" : "AM");
+ break;
+ case 'r': {
+ int hour12 = (hour % 24 + 11) % 12 + 1;
+ appendTwoDigits(builder, hour12);
+ builder.append(':');
+ appendTwoDigits(builder, minute);
+ builder.append(':');
+ appendTwoDigits(builder, second);
+ builder.append(' ');
+ builder.append((hour % 24) >= 12 ? "PM" : "AM");
+ break;
+ }
+ case 'T':
+ if (hour < 100) {
+ appendTwoDigits(builder, hour);
+ } else {
+ appendWithPad(builder, hour, 2, '0');
+ }
+ builder.append(':');
+ appendTwoDigits(builder, minute);
+ builder.append(':');
+ appendTwoDigits(builder, second);
+ break;
+ case 'u':
+ if (month == 0) {
+ return null;
+ }
+ appendTwoDigits(builder, calcWeekNumber(year, month, day,
mysqlWeekMode(1)));
+ break;
+ case 'U':
+ if (month == 0) {
+ return null;
+ }
+ appendTwoDigits(builder, calcWeekNumber(year, month, day,
mysqlWeekMode(0)));
+ break;
+ case 'v':
+ if (month == 0) {
+ return null;
+ }
+ appendTwoDigits(builder, calcWeekNumber(year, month, day,
mysqlWeekMode(3)));
+ break;
+ case 'V':
+ if (month == 0) {
+ return null;
+ }
+ appendTwoDigits(builder, calcWeekNumber(year, month, day,
mysqlWeekMode(2)));
+ break;
+ case 'w':
+ if (month == 0 && year == 0) {
+ return null;
+ }
+ builder.append(calcWeekday(calcDayNr(year, month, day),
true));
+ break;
+ case 'W':
+ if (year == 0 && month == 0) {
+ return null;
+ }
+ builder.append(DAY_NAMES[calcWeekday(calcDayNr(year,
month, day), false)]);
+ break;
+ case 'x': {
+ if (month == 0 || day == 0) {
+ return null;
+ }
+ int[] weekYear = new int[1];
+ calcWeekNumberAndYear(year, month, day, mysqlWeekMode(3),
weekYear);
+ appendFourDigits(builder, weekYear[0]);
+ break;
+ }
+ case 'X': {
+ if (month == 0 || day == 0) {
+ return null;
+ }
+ int[] weekYear = new int[1];
+ calcWeekNumberAndYear(year, month, day, mysqlWeekMode(2),
weekYear);
+ appendFourDigits(builder, weekYear[0]);
+ break;
+ }
+ default:
+ builder.append(spec);
+ break;
+ }
+ }
+
+ if (builder.length() > MAX_FORMAT_RESULT_LENGTH) {
+ throw new AnalysisException("Formatted string length exceeds the
maximum allowed length");
+ }
+ return builder.toString();
+ }
+
+ private static String trimFormat(String pattern) {
+ if (pattern == null) {
+ throw new AnalysisException("Format string is null");
+ }
+ int start = 0;
+ int end = pattern.length();
+ while (start < end && Character.isWhitespace(pattern.charAt(start))) {
+ start++;
+ }
+ while (end > start && Character.isWhitespace(pattern.charAt(end - 1)))
{
+ end--;
+ }
+ String trimmed = pattern.substring(start, end);
+ if (trimmed.length() > MAX_FORMAT_STRING_LENGTH) {
+ throw new AnalysisException("Format string length exceeds the
maximum allowed length");
+ }
+ return trimmed;
+ }
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
index 50468baf16f..b0bc15aaa92 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
@@ -448,15 +448,16 @@ class FoldConstantTest extends
ExpressionRewriteTestHelper {
StringLiteral.of("%y %m %d"));
rewritten = executor.rewrite(d, context);
Assertions.assertEquals(new VarcharLiteral("01 01 01"), rewritten);
- d = new DateFormat(DateV2Literal.fromJavaDateType(LocalDateTime.of(1,
1, 1, 1, 1, 1)),
+ d = new
DateFormat(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1,
1)),
StringLiteral.of("%y %m %d"));
rewritten = executor.rewrite(d, context);
Assertions.assertEquals(new VarcharLiteral("01 01 01"), rewritten);
- d = new DateFormat(DateV2Literal.fromJavaDateType(LocalDateTime.of(1,
1, 1, 1, 1, 1)),
+ d = new
DateFormat(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1,
1)),
StringLiteral.of(StringUtils.repeat("s", 128) + " "));
rewritten = executor.rewrite(d, context);
- Assertions.assertEquals(new VarcharLiteral(StringUtils.repeat("s",
128) + " "), rewritten);
+ // Overlength output (>100 chars) is not folded
+ Assertions.assertEquals(d, rewritten);
DateTrunc t = new
DateTrunc(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1,
1)),
StringLiteral.of("week"));
@@ -1189,13 +1190,12 @@ class FoldConstantTest extends
ExpressionRewriteTestHelper {
void testDateV2TypeDateTimeArithmeticFunctions() {
DateV2Literal dateLiteral = new DateV2Literal("1999-12-31");
IntegerLiteral integerLiteral = new IntegerLiteral(30);
- VarcharLiteral format = new VarcharLiteral("%Y-%m-%d");
String[] answer = {
"'2000-01-30'", "'1999-12-01'", "'2029-12-31'", "'1969-12-31'",
"'2002-06-30'", "'1997-06-30'", "'2000-01-30'", "'1999-12-01'",
"1999", "4", "12", "6", "31", "365", "31",
- "'1999-12-31'", "'1999-12-27'", "'1999-12-31'"
+ "'1999-12-27'", "'1999-12-31'"
};
int answerIdx = 0;
@@ -1216,8 +1216,6 @@ class FoldConstantTest extends
ExpressionRewriteTestHelper {
Assertions.assertEquals(DateTimeExtractAndTransform.dayOfYear(dateLiteral).toSql(),
answer[answerIdx++]);
Assertions.assertEquals(DateTimeExtractAndTransform.day(dateLiteral).toSql(),
answer[answerIdx++]);
-
Assertions.assertEquals(DateTimeExtractAndTransform.dateFormat(dateLiteral,
format).toSql(),
- answer[answerIdx++]);
Assertions.assertEquals(DateTimeExtractAndTransform.toMonday(dateLiteral).toSql(),
answer[answerIdx++]);
Assertions.assertEquals(DateTimeExtractAndTransform.lastDay(dateLiteral).toSql(),
answer[answerIdx]);
}
diff --git
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
index 25c5b1672ab..a05c5e1a17b 100644
---
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
+++
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
@@ -128,6 +128,9 @@ Sun Oct 10
-- !date_format_8 --
\N
+-- !date_format_9 --
+Sunday October 2009
+
-- !date_1 --
2003-12-31
@@ -2029,6 +2032,110 @@ da fanadur
\N
07:23:25
+-- !time_format_1 --
+1 00:00:00 00 0 12 12 12 00 00
00 000000 AM 12:00:00 AM 00:00:00 00:00:00.000000 0 00 12
12 12 00 000000 00 AM 00:00:00 12:00:00 AM 12:12 12 0 12 00 12 AM
000000 00 00 00:00:00 12:00:00 AM
+2 00:00:00.123456 00 0 12 12 12 00 00
00 123456 AM 12:00:00 AM 00:00:00 00:00:00.123456 0 00 12
12 12 00 123456 00 AM 00:00:00 12:00:00 AM 12:12 12 0 12 00 12 AM
123456 00 00 00:00:00 12:00:00 AM
+3 12:34:56 12 12 12 12 12 34 56
56 000000 PM 12:34:56 PM 12:34:56 12:34:56.000000 12 12
12 12 12 56 000000 34 PM 12:34:56 12:34:56 PM 12:12 12 12 12 12 12 PM
000000 56 34 12:34:56 12:34:56 PM
+4 12:34:56.789012 12 12 12 12 12 34 56
56 789012 PM 12:34:56 PM 12:34:56 12:34:56.789012 12 12
12 12 12 56 789012 34 PM 12:34:56 12:34:56 PM 12:12 12 12 12 12 12 PM
789012 56 34 12:34:56 12:34:56 PM
+5 23:59:59 23 23 11 11 11 59 59
59 000000 PM 11:59:59 PM 23:59:59 23:59:59.000000 23 23
11 11 11 59 000000 59 PM 23:59:59 11:59:59 PM 11:11 11 23 11 23 11 PM
000000 59 59 23:59:59 11:59:59 PM
+6 23:59:59.999999 23 23 11 11 11 59 59
59 999999 PM 11:59:59 PM 23:59:59 23:59:59.999999 23 23
11 11 11 59 999999 59 PM 23:59:59 11:59:59 PM 11:11 11 23 11 23 11 PM
999999 59 59 23:59:59 11:59:59 PM
+7 08:00:00 08 8 08 08 8 00 00
00 000000 AM 08:00:00 AM 08:00:00 08:00:00.000000 8 08 8
08 08 00 000000 00 AM 08:00:00 08:00:00 AM 08:08 8 8 08 08 08 AM 000000
00 00 08:00:00 08:00:00 AM
+8 15:00:00 15 15 03 03 3 00 00
00 000000 PM 03:00:00 PM 15:00:00 15:00:00.000000 15 15 3
03 03 00 000000 00 PM 15:00:00 03:00:00 PM 03:03 3 15 03 15 03 PM
000000 00 00 15:00:00 03:00:00 PM
+9 100:00:00 100 100 04 04 4 00 00
00 000000 AM 04:00:00 AM 100:00:00 100:00:00.000000
100 100 4 04 04 00 000000 00 AM 100:00:00 04:00:00 AM 04:04 4 100 04 100 04
AM 000000 00 00 100:00:00 04:00:00 AM
+10 123:45:56 123 123 03 03 3 45 56
56 000000 AM 03:45:56 AM 123:45:56 123:45:56.000000
123 123 3 03 03 56 000000 45 AM 123:45:56 03:45:56 AM 03:03 3 123 03 123 03
AM 000000 56 45 123:45:56 03:45:56 AM
+11 838:59:59.999999 \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N
+12 -00:00:01 -00 -0 -12 -12 -12 -00 -01
-01 -000000 -AM -12:00:01 AM -00:00:01 -00:00:01.000000
-0 00 12 12 12 -01 000000 00 AM -00:00:01 12:00:01 AM 12:12 -12 0
12 00 12 AM -000000 01 00 00:00:01 12:00:01 AM
+13 -12:34:56.000001 -12 -12 -12 -12 -12 -34
-56 -56 -000001 -PM -12:34:56 PM -12:34:56
-12:34:56.000001 -12 12 12 12 12 -56 000001 34 PM -12:34:56
12:34:56 PM 12:12 -12 12 12 12 12 PM -000001 56 34 12:34:56 12:34:56 PM
+14 -838:59:59.999999 \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N
+15 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
+
+-- !time_format_2 --
+00 0 12 12 12 AM
+
+-- !time_format_3 --
+00 123456 00 00:00:00
+
+-- !time_format_4 --
+12:34:56 PM 12:34:56 12:12 12
+
+-- !time_format_5 --
+12 12 12 12 12 PM 56
+
+-- !time_format_6 --
+000000 59 59 PM 11:59:59 PM
+
+-- !time_format_7 --
+23:59:59 11:59:59 PM 23:59:59.999999
+
+-- !time_format_8 --
+8 8 08 08 08 AM 000000
+
+-- !time_format_9 --
+00 00 000000 15:00:00 03:00:00 PM PM
+
+-- !time_format_10 --
+\N
+
+-- !time_format_11 --
+\N
+
+-- !time_format_12 --
+\N
+
+-- !time_format_13 --
+00:00:01 00 12:00:01 AM 01 000000 12:12
+
+-- !time_format_14 --
+PM 12 12 12 12 12:34:56 000001
+
+-- !time_format_15 --
+\N
+
+-- !time_format_16 --
+0000-00-00 12:34:56
+
+-- !time_format_17 --
+00-00-00
+
+-- !time_format_18 --
+0000 00 00
+
+-- !time_format_19 --
+0-0
+
+-- !time_format_20 --
+0000/00/00 15:45:30.123456
+
+-- !time_format_21 --
+\N
+
+-- !time_format_22 --
+\N
+
+-- !time_format_23 --
+\N
+
+-- !time_format_24 --
+\N
+
+-- !time_format_25 --
+\N
+
+-- !time_format_26 --
+\N
+
+-- !time_format_27 --
+\N
+
+-- !time_format_28 --
+\N
+
+-- !time_format_29 --
+\N
+
+-- !time_format_30 --
+\N
+
-- !to_seconds_1 --
63358934400
diff --git
a/regression-test/suites/correctness_p0/test_function_signature_all_types.groovy
b/regression-test/suites/correctness_p0/test_function_signature_all_types.groovy
index 0e5f49433d7..31ea693bb3c 100644
---
a/regression-test/suites/correctness_p0/test_function_signature_all_types.groovy
+++
b/regression-test/suites/correctness_p0/test_function_signature_all_types.groovy
@@ -461,7 +461,7 @@ suite("test_function_signature_all_types", 'nonConcurrent')
{
explain {
sql("SELECT date_format(k14, '%Y-%m-%d') FROM test_sig_all_types")
- notContains("CAST")
+ contains("CAST")
}
qt_dateformat_datev2 "SELECT date_format(k14, '%Y-%m-%d') FROM
test_sig_all_types ORDER BY k1"
diff --git
a/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
b/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
index 41930efff3b..f64e6391bd3 100644
---
a/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
+++
b/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
@@ -194,6 +194,7 @@ suite("doc_date_functions_test") {
// Any parameter is NULL
qt_date_format_7 """SELECT DATE_FORMAT(NULL, '%Y-%m-%d')"""
qt_date_format_8 """SELECT DATE_FORMAT('2009-10-04', NULL)"""
+ qt_date_format_9 """SELECT DATE_FORMAT('2009-10-04 22:23:00', ' %W %M
%Y')"""
// 10. DATE function tests
// Extract date part from datetime
@@ -1405,6 +1406,87 @@ suite("doc_date_functions_test") {
testFoldConst("SELECT YEARWEEK('2023-01-02', 5) AS yearweek_mode5")
testFoldConst("SELECT YEARWEEK('2023-12-25', 1) AS date_type_mode1")
+ //101. TIME_FORMAT function tests
+ sql """ DROP TABLE IF EXISTS test_time_format; """
+ sql """CREATE TABLE test_time_format (
+ id INT,
+ tm VARCHAR(32)
+ ) DUPLICATE KEY(id)
+ PROPERTIES ( 'replication_num' = '1' );
+ """
+ sql """ INSERT INTO test_time_format VALUES
+ ( 1, '00:00:00'),
+ ( 2, '00:00:00.123456'),
+ ( 3, '12:34:56'),
+ ( 4, '12:34:56.789012'),
+ ( 5, '23:59:59'),
+ ( 6, '23:59:59.999999'),
+ ( 7, '08:00:00'),
+ ( 8, '15:00:00'),
+ ( 9, '100:00:00'),
+ (10, '123:45:56'),
+ (11, '838:59:59.999999'),
+ (12, '-00:00:01'),
+ (13, '-12:34:56.000001'),
+ (14, '-838:59:59.999999'),
+ (15, NULL);
+ """
+ qt_time_format_1 """SELECT
+ id,
+ tm,
+ TIME_FORMAT(tm, '%H'),
+ TIME_FORMAT(tm, '%k'),
+ TIME_FORMAT(tm, '%h'),
+ TIME_FORMAT(tm, '%I'),
+ TIME_FORMAT(tm, '%l'),
+ TIME_FORMAT(tm, '%i'),
+ TIME_FORMAT(tm, '%s'),
+ TIME_FORMAT(tm, '%S'),
+ TIME_FORMAT(tm, '%f'),
+ TIME_FORMAT(tm, '%p'),
+ TIME_FORMAT(tm, '%r'),
+ TIME_FORMAT(tm, '%T'),
+ TIME_FORMAT(tm, '%H:%i:%s.%f'),
+ TIME_FORMAT(tm, '%k %H %l %I %h'),
+ TIME_FORMAT(tm, '%s %f %i %p'),
+ TIME_FORMAT(tm, '%T %r %h:%I'),
+ TIME_FORMAT(tm, '%l %k %I %H %h %p'),
+ TIME_FORMAT(tm, '%f %s %i %T %r')
+ FROM test_time_format
+ ORDER BY id;
+ """
+ qt_time_format_2 """SELECT TIME_FORMAT('2023-01-01 00:00:00', '%H %k %l %I
%h %p')"""
+ qt_time_format_3 """SELECT TIME_FORMAT('2023-01-01 00:00:00.123456', '%s
%f %i %T')"""
+ qt_time_format_4 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%r %T %h:%I
%l')"""
+ qt_time_format_5 """SELECT TIME_FORMAT('2023-01-01 12:34:56.789012', '%k
%H %I %l %h %p %s')"""
+ qt_time_format_6 """SELECT TIME_FORMAT('2023-01-01 23:59:59', '%f %s %i %p
%r')"""
+ qt_time_format_7 """SELECT TIME_FORMAT('2023-01-01 23:59:59.999999', '%T
%r %H:%i:%s.%f')"""
+ qt_time_format_8 """SELECT TIME_FORMAT('2023-01-01 08:00:00', '%l %k %h %I
%H %p %f')"""
+ qt_time_format_9 """SELECT TIME_FORMAT('2023-01-01 15:00:00', '%s %i %f %T
%r %p')"""
+ qt_time_format_10 """SELECT TIME_FORMAT('2023-01-01 100:00:00', '%H %l %I
%k %h %s %f')"""
+ qt_time_format_11 """SELECT TIME_FORMAT('2023-01-01 123:45:56', '%p %r %T
%i %s %f %H')"""
+ qt_time_format_12 """SELECT TIME_FORMAT('2023-01-01 838:59:59.999999', '%k
%f %s %I %l %H %p')"""
+ qt_time_format_13 """SELECT TIME_FORMAT('2023-01-01 00:00:01', '%T %i %r
%s %f %h:%I')"""
+ qt_time_format_14 """SELECT TIME_FORMAT('2023-01-01 12:34:56.000001', '%p
%H %k %l %I %T %f')"""
+ qt_time_format_15 """SELECT TIME_FORMAT('2023-01-01 838:59:59.999999', '%s
%i %f %r %p %H:%i:%s')"""
+
+ // Time format with date placeholders (Year, Month, Day return zeros or
NULL)
+ qt_time_format_16 """SELECT TIME_FORMAT('2023-01-01 12:34:56.789012',
'%Y-%m-%d %H:%i:%s')"""
+ qt_time_format_17 """SELECT TIME_FORMAT('2023-01-01 01:02:03.456789',
'%y-%m-%d')"""
+ qt_time_format_18 """SELECT TIME_FORMAT('2023-01-01 23:59:59.999999', '%Y
%m %d')"""
+ qt_time_format_19 """SELECT TIME_FORMAT('2023-01-01 00:00:00', '%c-%e')"""
+ qt_time_format_20 """SELECT TIME_FORMAT('2023-01-01 15:45:30.123456',
'%Y/%m/%d %H:%i:%s.%f')"""
+ qt_time_format_21 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%M')"""
+ qt_time_format_22 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%W')"""
+ qt_time_format_23 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%j')"""
+ qt_time_format_24 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%D')"""
+ qt_time_format_25 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%U')"""
+ qt_time_format_26 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%u')"""
+ qt_time_format_27 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%V')"""
+ qt_time_format_28 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%v')"""
+ qt_time_format_29 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%x')"""
+ qt_time_format_30 """SELECT TIME_FORMAT('2023-01-01 12:34:56', '%X %w')"""
+
// TO_SECONDS function tests
qt_to_seconds_1 """select to_seconds('2007-10-07')"""
qt_to_seconds_2 """select to_seconds('2007-10-07 10:03:09')"""
@@ -1431,7 +1513,7 @@ suite("doc_date_functions_test") {
testFoldConst("SELECT to_seconds(20250101)")
testFoldConst("SELECT to_seconds(20250101123045)")
- // Test constant folding for Group 1 functions (基础日期函数)
+ // Test constant folding for Group 1 functions
// 1. CONVERT_TZ function constant folding tests
testFoldConst("SELECT CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATETIME),
'Asia/Shanghai', 'America/Los_Angeles')")
@@ -1489,6 +1571,7 @@ suite("doc_date_functions_test") {
testFoldConst("SELECT DATE_FORMAT('2009-10-04', '%D %e %f')")
testFoldConst("SELECT DATE_FORMAT(NULL, '%Y-%m-%d')")
testFoldConst("SELECT DATE_FORMAT('2009-10-04', NULL)")
+ testFoldConst("SELECT DATE_FORMAT('2009-10-04 22:23:00', ' %W %M
%Y')")
// 7. DATE function constant folding tests
testFoldConst("SELECT DATE('2003-12-31 01:02:03')")
@@ -2058,6 +2141,85 @@ suite("doc_date_functions_test") {
testFoldConst("SELECT MAKETIME(123, -4, 40)")
testFoldConst("SELECT MAKETIME(7, 8, -23)")
+ // 100. TIME_FORMAT function constant folding tests
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%H') AS zero_24hour")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%k') AS zero_24hour_no_pad")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%h') AS zero_12hour")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%I') AS zero_12hour_alt")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%l') AS zero_12hour_no_pad")
+ testFoldConst("SELECT TIME_FORMAT('838:59:59', '%k:%i:%S') AS
max_k_format")
+ testFoldConst("SELECT TIME_FORMAT('838:59:59', '%H.%i.%s.%f') AS
max_with_micro_sep")
+ testFoldConst("SELECT TIME_FORMAT('838:59:59', '%T') AS max_time_T")
+ testFoldConst("SELECT TIME_FORMAT('838:59:59', '%r') AS max_time_r")
+ testFoldConst("SELECT TIME_FORMAT('-838:59:59', '%k %i %S') AS
min_k_format")
+ testFoldConst("SELECT TIME_FORMAT('-838:59:59', '%H%i%S%f') AS
min_compact")
+ testFoldConst("SELECT TIME_FORMAT('839:00:00', '%T') AS beyond_max_T")
+ testFoldConst("SELECT TIME_FORMAT('-839:00:00', '%r') AS beyond_min_r")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56.123456', '%f') AS
only_microseconds")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56.789012', '%k.%f') AS
hour_microsec")
+ testFoldConst("SELECT TIME_FORMAT('23:59:59.999999', '%T.%f') AS
T_format_micro")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00.000001', '%f only') AS
micro_with_text")
+ testFoldConst("SELECT TIME_FORMAT('13:45:30', '%H vs %k vs %h vs %I vs
%l') AS all_hour_formats")
+ testFoldConst("SELECT TIME_FORMAT('03:07:09', '%H-%k-%h-%I-%l') AS
morning_all_formats")
+ testFoldConst("SELECT TIME_FORMAT('00:30:45', '%H|%k|%h|%I|%l') AS
midnight_all_formats")
+ testFoldConst("SELECT TIME_FORMAT('12:00:00', '%H/%k/%h/%I/%l') AS
noon_all_formats")
+ testFoldConst("SELECT TIME_FORMAT('23:59:59', '%k,%h,%l,%p') AS
late_night_formats")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%S') AS uppercase_S")
+ testFoldConst("SELECT TIME_FORMAT('12:34:09', '%S vs %s') AS both_seconds")
+ testFoldConst("SELECT TIME_FORMAT('12:34:05', '%k:%i:%S') AS k_i_S")
+ testFoldConst("SELECT TIME_FORMAT('15:30:45', '%T') AS T_afternoon")
+ testFoldConst("SELECT TIME_FORMAT('03:07:22', '%T') AS T_morning")
+ testFoldConst("SELECT TIME_FORMAT('15:30:45', '%r') AS r_afternoon")
+ testFoldConst("SELECT TIME_FORMAT('03:07:22', '%r') AS r_morning")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', '%T vs %r') AS
T_vs_r_midnight")
+ testFoldConst("SELECT TIME_FORMAT('12:00:00', '%T vs %r') AS T_vs_r_noon")
+ testFoldConst("SELECT TIME_FORMAT('13:45:30', '%p') AS only_pm")
+ testFoldConst("SELECT TIME_FORMAT('09:15:20', '%p') AS only_am")
+ testFoldConst("SELECT TIME_FORMAT('23:59:59', '%p at %l:%i') AS
pm_natural")
+ testFoldConst("SELECT TIME_FORMAT('00:30:45', '%p-%l-%i-%S') AS am_dashes")
+ testFoldConst("SELECT TIME_FORMAT('15:07:22', '%p%p%p') AS triple_pm")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%i') AS only_minutes")
+ testFoldConst("SELECT TIME_FORMAT('12:05:56', '%i') AS
minutes_leading_zero")
+ testFoldConst("SELECT TIME_FORMAT('12:00:00', '%i:%S') AS min_sec_only")
+ testFoldConst("SELECT TIME_FORMAT('23:59:59', '%i%S') AS min_sec_compact")
+ testFoldConst("SELECT TIME_FORMAT('500:30:45', '%k:%i:%S') AS large_k")
+ testFoldConst("SELECT TIME_FORMAT('700:00:00', '%H-%k') AS large_H_k")
+ testFoldConst("SELECT TIME_FORMAT('100:15:30', '%T') AS large_T")
+ testFoldConst("SELECT TIME_FORMAT('838:00:00', '%k only') AS max_hour_k")
+ testFoldConst("SELECT TIME_FORMAT('-12:34:56', '%k:%i:%S') AS negative_k")
+ testFoldConst("SELECT TIME_FORMAT('-100:30:45', '%T') AS negative_T")
+ testFoldConst("SELECT TIME_FORMAT('-05:07:09', '%r') AS negative_r")
+ testFoldConst("SELECT TIME_FORMAT('-838:59:59', '%H%k%h%I%l') AS
negative_max_all")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%%H=%%k') AS
percent_escaped")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%% %T %%') AS
percent_around_T")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%H\\:%i\\:%s') AS
backslash_colon")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '%k-%i-%S-%f') AS
all_with_dashes")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', '') AS empty_format")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', 'no specifiers at all') AS
literal_only")
+ testFoldConst("SELECT TIME_FORMAT('15:45:30', '%k%i%S%f%p%T%r') AS
everything_combined")
+ testFoldConst("SELECT TIME_FORMAT('03:07:09', '%l o clock %i minutes %S
seconds %p') AS natural_lang")
+ testFoldConst("SELECT TIME_FORMAT('23:59:59', '%H=%k, %h=%I=%l, %p') AS
hour_comparisons")
+ testFoldConst("SELECT TIME_FORMAT('12:00:00', 'Noon: %T or %r?') AS
noon_question")
+ testFoldConst("SELECT TIME_FORMAT('00:00:00', 'Midnight: %k|%h|%l %p') AS
midnight_formats")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%k:%i:%S') AS
datetime_k")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 03:07:22', '%l:%i %p') AS
datetime_12h")
+ testFoldConst("SELECT TIME_FORMAT(NULL, '%T') AS null_time_T")
+ testFoldConst("SELECT TIME_FORMAT('12:34:56', NULL) AS null_format")
+ testFoldConst("SELECT TIME_FORMAT(NULL, NULL) AS both_null")
+
+ // TIME_FORMAT with date placeholders constant folding tests
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%Y-%m-%d
%H:%i:%s') AS date_with_time")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 08:15:30.123456', '%y/%m/%d
%T.%f') AS short_date_format")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 23:59:59', '%Y %m %d') AS
year_month_day")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 12:34:56', '%c-%e') AS
month_day_no_pad")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 10:20:30.987654', '%Y/%m/%d
%H:%i:%s.%f') AS full_datetime")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%M') AS
month_name")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%W') AS
weekday_name")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%j') AS
day_of_year")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%D') AS
day_with_suffix")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%U %u') AS
week_numbers")
+ testFoldConst("SELECT TIME_FORMAT('2023-12-25 15:30:45', '%V %v %w') AS
week_variants")
+
// Additional NULL parameter tests for comprehensive coverage
// MINUTE functions NULL tests
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
