This is an automated email from the ASF dual-hosted git repository.
gabriellee pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 0b9b1be1f14 [fix](function) Fix from_second functions overflow and
wrong result (#28685)
0b9b1be1f14 is described below
commit 0b9b1be1f146d2f24767e3abfbf230019721bdbc
Author: zclllyybb <[email protected]>
AuthorDate: Fri Dec 22 10:22:49 2023 +0800
[fix](function) Fix from_second functions overflow and wrong result (#28685)
---
be/src/exec/es/es_scroll_parser.cpp | 14 +-
be/src/runtime/runtime_state.h | 1 +
be/src/vec/functions/date_time_transforms.h | 4 +-
be/src/vec/functions/function_convert_tz.h | 13 +-
.../function_date_or_datetime_computation.h | 144 +++++++++------------
be/src/vec/runtime/vdatetime_value.cpp | 30 +++--
be/src/vec/runtime/vdatetime_value.h | 17 +--
.../date-time-functions/from-second.md | 56 ++++----
docs/sidebars.json | 1 +
.../date-time-functions/from-second.md | 52 ++++----
.../test_from_millisecond_microsecond.out | 33 +++--
.../test_from_millisecond_microsecond.groovy | 5 +
12 files changed, 174 insertions(+), 196 deletions(-)
diff --git a/be/src/exec/es/es_scroll_parser.cpp
b/be/src/exec/es/es_scroll_parser.cpp
index d7b16f656c3..726e86ea74f 100644
--- a/be/src/exec/es/es_scroll_parser.cpp
+++ b/be/src/exec/es/es_scroll_parser.cpp
@@ -222,7 +222,8 @@ Status get_date_value_int(const rapidjson::Value& col,
PrimitiveType type, bool
if (ok) {
// The local time zone can change by session variable
`time_zone`
// We should use the user specified time zone, not the actual
system local time zone.
- success =
dt_val.from_unixtime(std::chrono::system_clock::to_time_t(tp), time_zone);
+ success = true;
+ dt_val.from_unixtime(std::chrono::system_clock::to_time_t(tp),
time_zone);
}
} else if (str_length == 19) {
// YYYY-MM-DDTHH:MM:SS
@@ -231,8 +232,8 @@ Status get_date_value_int(const rapidjson::Value& col,
PrimitiveType type, bool
const bool ok =
cctz::parse("%Y-%m-%dT%H:%M:%S", str_date,
cctz::utc_time_zone(), &tp);
if (ok) {
- success =
dt_val.from_unixtime(std::chrono::system_clock::to_time_t(tp),
- time_zone);
+ success = true;
+
dt_val.from_unixtime(std::chrono::system_clock::to_time_t(tp), time_zone);
}
} else {
// YYYY-MM-DD HH:MM:SS
@@ -243,7 +244,8 @@ Status get_date_value_int(const rapidjson::Value& col,
PrimitiveType type, bool
// string long like "1677895728000"
int64_t time_long = std::atol(str_date.c_str());
if (time_long > 0) {
- success = dt_val.from_unixtime(time_long / 1000, time_zone);
+ success = true;
+ dt_val.from_unixtime(time_long / 1000, time_zone);
}
} else {
// YYYY-MM-DD or others
@@ -255,9 +257,7 @@ Status get_date_value_int(const rapidjson::Value& col,
PrimitiveType type, bool
}
} else {
- if (!dt_val.from_unixtime(col.GetInt64() / 1000, time_zone)) {
- RETURN_ERROR_IF_CAST_FORMAT_ERROR(col, type);
- }
+ dt_val.from_unixtime(col.GetInt64() / 1000, time_zone);
}
if constexpr (is_datetime_v1) {
if (type == TYPE_DATE) {
diff --git a/be/src/runtime/runtime_state.h b/be/src/runtime/runtime_state.h
index a4524d846fb..989655a36c7 100644
--- a/be/src/runtime/runtime_state.h
+++ b/be/src/runtime/runtime_state.h
@@ -132,6 +132,7 @@ public:
TQueryType::type query_type() const { return _query_options.query_type; }
int64_t timestamp_ms() const { return _timestamp_ms; }
int32_t nano_seconds() const { return _nano_seconds; }
+ // if possible, use timezone_obj() rather than timezone()
const std::string& timezone() const { return _timezone; }
const cctz::time_zone& timezone_obj() const { return _timezone_obj; }
const std::string& user() const { return _user; }
diff --git a/be/src/vec/functions/date_time_transforms.h
b/be/src/vec/functions/date_time_transforms.h
index 877a66e002a..a513f9f154d 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -222,10 +222,10 @@ struct FromUnixTimeImpl {
static inline auto execute(FromType val, StringRef format,
ColumnString::Chars& res_data,
size_t& offset, const cctz::time_zone&
time_zone) {
DateType dt;
- if (format.size > 128 || val < 0 || val > TIMESTAMP_VALID_MAX ||
- !dt.from_unixtime(val, time_zone)) {
+ if (format.size > 128 || val < 0 || val > TIMESTAMP_VALID_MAX) {
return std::pair {offset, true};
}
+ dt.from_unixtime(val, time_zone);
char buf[128];
if (!dt.to_format_string(format.data, format.size, buf)) {
diff --git a/be/src/vec/functions/function_convert_tz.h
b/be/src/vec/functions/function_convert_tz.h
index c8957122abe..bfd261640c1 100644
--- a/be/src/vec/functions/function_convert_tz.h
+++ b/be/src/vec/functions/function_convert_tz.h
@@ -201,12 +201,7 @@ private:
result_column->insert_default();
return;
}
-
- if (!ts_value2.from_unixtime(timestamp, to_tz)) {
- result_null_map[index_now] = true;
- result_column->insert_default();
- return;
- }
+ ts_value2.from_unixtime(timestamp, to_tz);
} else {
int64_t timestamp;
if (!ts_value.unix_timestamp(×tamp, from_tz)) {
@@ -215,11 +210,7 @@ private:
return;
}
- if (!ts_value2.from_unixtime(timestamp, to_tz)) {
- result_null_map[index_now] = true;
- result_column->insert_default();
- return;
- }
+ ts_value2.from_unixtime(timestamp, to_tz);
}
result_column->insert(binary_cast<ReturnDateValueType,
ReturnNativeType>(ts_value2));
diff --git a/be/src/vec/functions/function_date_or_datetime_computation.h
b/be/src/vec/functions/function_date_or_datetime_computation.h
index 87d3fa8268b..383fb10e34a 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -918,34 +918,30 @@ struct CurrentDateTimeImpl {
DateValueType dtv;
bool use_const;
if constexpr (WithPrecision) {
- if (const ColumnConst* const_column =
check_and_get_column<ColumnConst>(
+ if (const auto* const_column = check_and_get_column<ColumnConst>(
block.get_by_position(arguments[0]).column)) {
int scale = const_column->get_int(0);
- if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
- context->state()->nano_seconds(),
- context->state()->timezone_obj(),
scale)) {
- if constexpr (std::is_same_v<DateValueType,
VecDateTimeValue>) {
-
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
- }
- auto date_packed_int = binary_cast<DateValueType,
NativeType>(dtv);
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
-
- } else {
- auto invalid_val = 0;
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)), 0);
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->nano_seconds(),
+ context->state()->timezone_obj(), scale);
+ if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>)
{
+
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
}
+ auto date_packed_int = binary_cast<DateValueType,
NativeType>(dtv);
+ col_to->insert_data(
+ const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
+
use_const = true;
- } else if (const ColumnNullable* nullable_column =
check_and_get_column<ColumnNullable>(
+ } else if (const auto* nullable_column =
check_and_get_column<ColumnNullable>(
block.get_by_position(arguments[0]).column)) {
const auto& null_map = nullable_column->get_null_map_data();
const auto& nested_column =
nullable_column->get_nested_column_ptr();
for (int i = 0; i < input_rows_count; i++) {
- if (!null_map[i] &&
dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
-
context->state()->nano_seconds(),
-
context->state()->timezone_obj(),
-
nested_column->get64(i))) {
+ if (!null_map[i]) {
+ dtv.from_unixtime(context->state()->timestamp_ms() /
1000,
+ context->state()->nano_seconds(),
+ context->state()->timezone_obj(),
+ nested_column->get64(i));
if constexpr (std::is_same_v<DateValueType,
VecDateTimeValue>) {
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
}
@@ -963,38 +959,27 @@ struct CurrentDateTimeImpl {
} else {
auto& int_column = block.get_by_position(arguments[0]).column;
for (int i = 0; i < input_rows_count; i++) {
- if (dtv.from_unixtime(context->state()->timestamp_ms() /
1000,
- context->state()->nano_seconds(),
- context->state()->timezone_obj(),
int_column->get64(i))) {
- if constexpr (std::is_same_v<DateValueType,
VecDateTimeValue>) {
-
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
- }
- auto date_packed_int = binary_cast<DateValueType,
NativeType>(dtv);
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)),
- 0);
- } else {
- auto invalid_val = 0;
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)), 0);
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->nano_seconds(),
+ context->state()->timezone_obj(),
int_column->get64(i));
+ if constexpr (std::is_same_v<DateValueType,
VecDateTimeValue>) {
+
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
}
+ auto date_packed_int = binary_cast<DateValueType,
NativeType>(dtv);
+ col_to->insert_data(
+ const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
}
use_const = false;
}
} else {
- if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
- context->state()->timezone_obj())) {
- if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>)
{
-
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
- }
- auto date_packed_int = binary_cast<DateValueType,
NativeType>(dtv);
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
- } else {
- auto invalid_val = 0;
- col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)),
- 0);
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->timezone_obj());
+ if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>) {
+
reinterpret_cast<DateValueType*>(&dtv)->set_type(TIME_DATETIME);
}
+ auto date_packed_int = binary_cast<DateValueType, NativeType>(dtv);
+ col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)),
+ 0);
use_const = true;
}
@@ -1017,31 +1002,21 @@ struct CurrentDateImpl {
auto col_to = ColumnVector<NativeType>::create();
if constexpr (std::is_same_v<DateType, DataTypeDateV2>) {
DateV2Value<DateV2ValueType> dtv;
- if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
- context->state()->timezone_obj())) {
- auto date_packed_int =
binary_cast<DateV2Value<DateV2ValueType>, uint32_t>(
-
*reinterpret_cast<DateV2Value<DateV2ValueType>*>(&dtv));
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
- } else {
- auto invalid_val = 0;
- col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)),
- 0);
- }
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->timezone_obj());
+ auto date_packed_int = binary_cast<DateV2Value<DateV2ValueType>,
uint32_t>(
+ *reinterpret_cast<DateV2Value<DateV2ValueType>*>(&dtv));
+ col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)),
+ 0);
} else {
VecDateTimeValue dtv;
- if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
- context->state()->timezone_obj())) {
- reinterpret_cast<VecDateTimeValue*>(&dtv)->set_type(TIME_DATE);
- auto date_packed_int = binary_cast<doris::VecDateTimeValue,
int64_t>(
- *reinterpret_cast<VecDateTimeValue*>(&dtv));
- col_to->insert_data(
- const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)), 0);
- } else {
- auto invalid_val = 0;
- col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)),
- 0);
- }
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->timezone_obj());
+ reinterpret_cast<VecDateTimeValue*>(&dtv)->set_type(TIME_DATE);
+ auto date_packed_int = binary_cast<doris::VecDateTimeValue,
int64_t>(
+ *reinterpret_cast<VecDateTimeValue*>(&dtv));
+ col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&date_packed_int)),
+ 0);
}
block.get_by_position(result).column =
ColumnConst::create(std::move(col_to), input_rows_count);
@@ -1057,15 +1032,11 @@ struct CurrentTimeImpl {
size_t result, size_t input_rows_count) {
auto col_to = ColumnVector<Float64>::create();
VecDateTimeValue dtv;
- if (dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
- context->state()->timezone_obj())) {
- double time = dtv.hour() * 3600l + dtv.minute() * 60l +
dtv.second();
- time *= (1000 * 1000);
- col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&time)), 0);
- } else {
- auto invalid_val = 0;
- col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&invalid_val)), 0);
- }
+ dtv.from_unixtime(context->state()->timestamp_ms() / 1000,
+ context->state()->timezone_obj());
+ double time = dtv.hour() * 3600l + dtv.minute() * 60l + dtv.second();
+ time *= (1000 * 1000);
+ col_to->insert_data(const_cast<const
char*>(reinterpret_cast<char*>(&time)), 0);
block.get_by_position(result).column =
ColumnConst::create(std::move(col_to), input_rows_count);
return Status::OK();
@@ -1144,15 +1115,28 @@ struct TimestampToDateTime : IFunction {
auto null_vector = ColumnVector<UInt8>::create();
res_col->get_data().resize_fill(input_rows_count, 0);
null_vector->get_data().resize_fill(input_rows_count, false);
+
NullMap& null_map = null_vector->get_data();
auto& res_data = res_col->get_data();
const cctz::time_zone& time_zone = context->state()->timezone_obj();
+
for (int i = 0; i < input_rows_count; ++i) {
Int64 value = column_data.get_element(i);
+ if (value < 0) {
+ null_map[i] = true;
+ continue;
+ }
+
auto& dt =
reinterpret_cast<DateV2Value<DateTimeV2ValueType>&>(res_data[i]);
- null_map[i] = !dt.from_unixtime(value / Impl::ratio, time_zone);
- dt.set_microsecond((value % Impl::ratio) * ratio_to_micro);
+ dt.from_unixtime(value / Impl::ratio, time_zone);
+
+ if (dt.is_valid_date()) [[likely]] {
+ dt.set_microsecond((value % Impl::ratio) * ratio_to_micro);
+ } else {
+ null_map[i] = true;
+ }
}
+
block.get_by_position(result).column =
ColumnNullable::create(std::move(res_col),
std::move(null_vector));
return Status::OK();
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index b177164b9ef..f625631ace9 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -1731,10 +1731,11 @@ bool VecDateTimeValue::from_unixtime(int64_t timestamp,
const std::string& timez
if (!TimezoneUtils::find_cctz_time_zone(timezone, ctz)) {
return false;
}
- return from_unixtime(timestamp, ctz);
+ from_unixtime(timestamp, ctz);
+ return true;
}
-bool VecDateTimeValue::from_unixtime(int64_t timestamp, const cctz::time_zone&
ctz) {
+void VecDateTimeValue::from_unixtime(int64_t timestamp, const cctz::time_zone&
ctz) {
static const cctz::time_point<cctz::sys_seconds> epoch =
std::chrono::time_point_cast<cctz::sys_seconds>(
std::chrono::system_clock::from_time_t(0));
@@ -1742,6 +1743,8 @@ bool VecDateTimeValue::from_unixtime(int64_t timestamp,
const cctz::time_zone& c
const auto tp = cctz::convert(t, ctz);
+ // there's no overflow check since it's hot path
+
_neg = 0;
_type = TIME_DATETIME;
_year = tp.year();
@@ -1750,8 +1753,6 @@ bool VecDateTimeValue::from_unixtime(int64_t timestamp,
const cctz::time_zone& c
_hour = tp.hour();
_minute = tp.minute();
_second = tp.second();
-
- return true;
}
const char* VecDateTimeValue::month_name() const {
@@ -3202,20 +3203,21 @@ bool DateV2Value<T>::from_unixtime(int64_t timestamp,
const std::string& timezon
if (!TimezoneUtils::find_cctz_time_zone(timezone, ctz)) {
return false;
}
- return from_unixtime(timestamp, ctz);
+ from_unixtime(timestamp, ctz);
+ return true;
}
template <typename T>
-bool DateV2Value<T>::from_unixtime(int64_t timestamp, const cctz::time_zone&
ctz) {
+void DateV2Value<T>::from_unixtime(int64_t timestamp, const cctz::time_zone&
ctz) {
static const cctz::time_point<cctz::sys_seconds> epoch =
std::chrono::time_point_cast<cctz::sys_seconds>(
std::chrono::system_clock::from_time_t(0));
cctz::time_point<cctz::sys_seconds> t = epoch + cctz::seconds(timestamp);
-
const auto tp = cctz::convert(t, ctz);
+ // there's no overflow check since it's hot path
+
set_time(tp.year(), tp.month(), tp.day(), tp.hour(), tp.minute(),
tp.second(), 0);
- return true;
}
template <typename T>
@@ -3225,11 +3227,12 @@ bool DateV2Value<T>::from_unixtime(std::pair<int64_t,
int64_t> timestamp,
if (!TimezoneUtils::find_cctz_time_zone(timezone, ctz)) {
return false;
}
- return from_unixtime(timestamp, ctz);
+ from_unixtime(timestamp, ctz);
+ return true;
}
template <typename T>
-bool DateV2Value<T>::from_unixtime(std::pair<int64_t, int64_t> timestamp,
+void DateV2Value<T>::from_unixtime(std::pair<int64_t, int64_t> timestamp,
const cctz::time_zone& ctz) {
static const cctz::time_point<cctz::sys_seconds> epoch =
std::chrono::time_point_cast<cctz::sys_seconds>(
@@ -3240,7 +3243,6 @@ bool DateV2Value<T>::from_unixtime(std::pair<int64_t,
int64_t> timestamp,
set_time(tp.year(), tp.month(), tp.day(), tp.hour(), tp.minute(),
tp.second(),
timestamp.second);
- return true;
}
template <typename T>
@@ -3250,11 +3252,12 @@ bool DateV2Value<T>::from_unixtime(int64_t timestamp,
int32_t nano_seconds,
if (!TimezoneUtils::find_cctz_time_zone(timezone, ctz)) {
return false;
}
- return from_unixtime(timestamp, nano_seconds, ctz, scale);
+ from_unixtime(timestamp, nano_seconds, ctz, scale);
+ return true;
}
template <typename T>
-bool DateV2Value<T>::from_unixtime(int64_t timestamp, int32_t nano_seconds,
+void DateV2Value<T>::from_unixtime(int64_t timestamp, int32_t nano_seconds,
const cctz::time_zone& ctz, int scale) {
static const cctz::time_point<cctz::sys_seconds> epoch =
std::chrono::time_point_cast<cctz::sys_seconds>(
@@ -3269,7 +3272,6 @@ bool DateV2Value<T>::from_unixtime(int64_t timestamp,
int32_t nano_seconds,
set_time(tp.year(), tp.month(), tp.day(), tp.hour(), tp.minute(),
tp.second(),
nano_seconds / int_exp10(9 - scale) * int_exp10(6 - scale));
- return true;
}
template <typename T>
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index 6fe7104ecdb..1999c230809 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -508,9 +508,10 @@ public:
bool unix_timestamp(int64_t* timestamp, const cctz::time_zone& ctz) const;
//construct datetime_value from timestamp and timezone
- //timestamp is an internal timestamp value representing seconds since
'1970-01-01 00:00:00' UTC
+ //timestamp is an internal timestamp value representing seconds since
'1970-01-01 00:00:00' UTC. negative avaliable.
+ //we don't do any check in it because it's hot path. any usage want ensure
the time legality should check itself.
bool from_unixtime(int64_t, const std::string& timezone);
- bool from_unixtime(int64_t, const cctz::time_zone& ctz);
+ void from_unixtime(int64_t, const cctz::time_zone& ctz);
bool operator==(const VecDateTimeValue& other) const {
// NOTE: This is not same with MySQL.
@@ -970,14 +971,14 @@ public:
bool unix_timestamp(std::pair<int64_t, int64_t>* timestamp, const
cctz::time_zone& ctz) const;
//construct datetime_value from timestamp and timezone
- //timestamp is an internal timestamp value representing seconds since
'1970-01-01 00:00:00' UTC
+ //timestamp is an internal timestamp value representing seconds since
'1970-01-01 00:00:00' UTC. negative avaliable.
+ //we don't do any check in it because it's hot path. any usage want ensure
the time legality should check itself.
bool from_unixtime(int64_t, const std::string& timezone);
- bool from_unixtime(int64_t, const cctz::time_zone& ctz);
+ void from_unixtime(int64_t, const cctz::time_zone& ctz);
bool from_unixtime(std::pair<int64_t, int64_t>, const std::string&
timezone);
- bool from_unixtime(std::pair<int64_t, int64_t>, const cctz::time_zone&
ctz);
-
- bool from_unixtime(int64_t, int32_t, const std::string& timezone, const
int scale);
- bool from_unixtime(int64_t, int32_t, const cctz::time_zone& ctz, int
scale);
+ void from_unixtime(std::pair<int64_t, int64_t>, const cctz::time_zone&
ctz);
+ bool from_unixtime(int64_t, int32_t, const std::string& timezone, int
scale);
+ void from_unixtime(int64_t, int32_t, const cctz::time_zone& ctz, int
scale);
bool operator==(const DateV2Value<T>& other) const {
// NOTE: This is not same with MySQL.
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/from-second.md
similarity index 54%
copy from
docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
copy to docs/en/docs/sql-manual/sql-functions/date-time-functions/from-second.md
index 601cb4a7071..bcbf92521ad 100644
---
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/from-second.md
@@ -1,7 +1,7 @@
---
{
"title": "FROM_SECOND",
- "language": "zh-CN"
+ "language": "en"
}
---
@@ -26,40 +26,25 @@ under the License.
## from_second
### description
-#### Syntax
+#### syntax
`DATETIME FROM_SECOND(BIGINT unix_timestamp)`
`DATETIME FROM_MILLISECOND(BIGINT unix_timestamp)`
`DATETIME FROM_MICROSECOND(BIGINT unix_timestamp)`
-将时间戳转化为对应的 DATETIME
-
-传入的是整型，返回的是DATETIME类型
-
+Converts a timestamp to its DATETIME represent, with argument as an integer
and returned as a DATETIME type. Returns `NULL` if `unix_timestamp < 0` or if
the function result is greater than `9999-12-31 23:59:59.999999`.
### example
```
-mysql> select from_microsecond(0);
-+----------------------------+
-| from_microsecond(0) |
-+----------------------------+
-| 1970-01-01 08:00:00.000000 |
-+----------------------------+
-
-mysql> select from_microsecond(12345678);
-+----------------------------+
-| from_microsecond(12345678) |
-+----------------------------+
-| 1970-01-01 08:00:12.345678 |
-+----------------------------+
+mysql> set time_zone='Asia/Shanghai';
-mysql> select from_millisecond(0);
-+-------------------------+
-| from_millisecond(0) |
-+-------------------------+
-| 1970-01-01 08:00:00.000 |
-+-------------------------+
+mysql> select from_second(-1);
++---------------------------+
+| from_second(-1) |
++---------------------------+
+| NULL |
++---------------------------+
mysql> select from_millisecond(12345678);
+----------------------------+
@@ -68,14 +53,21 @@ mysql> select from_millisecond(12345678);
| 1970-01-01 11:25:45.678 |
+----------------------------+
-mysql> select from_second(21474836470);
-+--------------------------+
-| from_second(21474836470) |
-+--------------------------+
-| 2650-07-06 16:21:10 |
-+--------------------------+
+mysql> select from_microsecond(253402271999999999);
++--------------------------------------+
+| from_microsecond(253402271999999999) |
++--------------------------------------+
+| 9999-12-31 23:59:59.999999 |
++--------------------------------------+
+
+mysql> select from_microsecond(253402272000000000);
++--------------------------------------+
+| from_microsecond(253402272000000000) |
++--------------------------------------+
+| NULL |
++--------------------------------------+
```
### keywords
- FROM_SECOND
+ FROM_SECOND,FROM,SECOND,MILLISECOND,MICROSECOND
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 0ba8f71b499..da910610f08 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -347,6 +347,7 @@
"sql-manual/sql-functions/date-time-functions/from-days",
"sql-manual/sql-functions/date-time-functions/last-day",
"sql-manual/sql-functions/date-time-functions/to-monday",
+
"sql-manual/sql-functions/date-time-functions/from-second",
"sql-manual/sql-functions/date-time-functions/from-unixtime",
"sql-manual/sql-functions/date-time-functions/unix-timestamp",
"sql-manual/sql-functions/date-time-functions/utc-timestamp",
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
index 601cb4a7071..df3c031966b 100644
---
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md
@@ -32,34 +32,19 @@ under the License.
`DATETIME FROM_MILLISECOND(BIGINT unix_timestamp)`
`DATETIME FROM_MICROSECOND(BIGINT unix_timestamp)`
-将时间戳转化为对应的 DATETIME
-
-传入的是整型，返回的是DATETIME类型
-
+将时间戳转化为对应的 DATETIME，传入的是整型，返回的是DATETIME类型。若`unix_timestamp < 0` 或函数结果大于
`9999-12-31 23:59:59.999999`，则返回`NULL`。
### example
```
-mysql> select from_microsecond(0);
-+----------------------------+
-| from_microsecond(0) |
-+----------------------------+
-| 1970-01-01 08:00:00.000000 |
-+----------------------------+
-
-mysql> select from_microsecond(12345678);
-+----------------------------+
-| from_microsecond(12345678) |
-+----------------------------+
-| 1970-01-01 08:00:12.345678 |
-+----------------------------+
+mysql> set time_zone='Asia/Shanghai';
-mysql> select from_millisecond(0);
-+-------------------------+
-| from_millisecond(0) |
-+-------------------------+
-| 1970-01-01 08:00:00.000 |
-+-------------------------+
+mysql> select from_second(-1);
++---------------------------+
+| from_second(-1) |
++---------------------------+
+| NULL |
++---------------------------+
mysql> select from_millisecond(12345678);
+----------------------------+
@@ -68,14 +53,21 @@ mysql> select from_millisecond(12345678);
| 1970-01-01 11:25:45.678 |
+----------------------------+
-mysql> select from_second(21474836470);
-+--------------------------+
-| from_second(21474836470) |
-+--------------------------+
-| 2650-07-06 16:21:10 |
-+--------------------------+
+mysql> select from_microsecond(253402271999999999);
++--------------------------------------+
+| from_microsecond(253402271999999999) |
++--------------------------------------+
+| 9999-12-31 23:59:59.999999 |
++--------------------------------------+
+
+mysql> select from_microsecond(253402272000000000);
++--------------------------------------+
+| from_microsecond(253402272000000000) |
++--------------------------------------+
+| NULL |
++--------------------------------------+
```
### keywords
- FROM_SECOND
+ FROM_SECOND,FROM,SECOND,MILLISECOND,MICROSECOND
diff --git
a/regression-test/data/correctness/test_from_millisecond_microsecond.out
b/regression-test/data/correctness/test_from_millisecond_microsecond.out
index 2dc7f81a74d..b4f5d9623f6 100644
--- a/regression-test/data/correctness/test_from_millisecond_microsecond.out
+++ b/regression-test/data/correctness/test_from_millisecond_microsecond.out
@@ -15,9 +15,9 @@
3001-01-19 07:59:59 3001-01-19T07:59:59 \N 3001-01-19T08:00
2650-07-06 16:21:10 2650-07-06T16:21:10
-- !select4 --
-1919810114514 1919810114514
-89417891234789 488885820389
-1235817896941 1235817896941
+1919810114514 \N \N
+89417891234789 \N \N
+1235817896941 \N \N
\N \N \N
-- !select5 --
@@ -48,9 +48,9 @@
2038-01-19 11:14:07 2038-01-19T11:14:07 2038-01-19 11:14:08
2038-01-19T11:14:08 2650-07-06 16:21:10 2650-07-06T16:21:10
-- !select10 --
-1919810114514 1919810114514
-89417891234789 488885820389
-1235817896941 1235817896941
+1919810114514 \N \N
+89417891234789 \N \N
+1235817896941 \N \N
\N \N \N
-- !select11 --
@@ -99,9 +99,9 @@
3001-01-19 07:59:59 3001-01-19T07:59:59 \N 3001-01-19T08:00
2650-07-06 16:21:10 2650-07-06T16:21:10
-- !select4 --
-1919810114514 1919810114514
-89417891234789 488885820389
-1235817896941 1235817896941
+1919810114514 \N \N
+89417891234789 \N \N
+1235817896941 \N \N
\N \N \N
-- !select5 --
@@ -132,9 +132,9 @@
2038-01-19 11:14:07 2038-01-19T11:14:07 2038-01-19 11:14:08
2038-01-19T11:14:08 2650-07-06 16:21:10 2650-07-06T16:21:10
-- !select10 --
-1919810114514 1919810114514
-89417891234789 488885820389
-1235817896941 1235817896941
+1919810114514 \N \N
+89417891234789 \N \N
+1235817896941 \N \N
\N \N \N
-- !select11 --
@@ -159,3 +159,12 @@
2 1672502400 1672502400123 1672502400123000
3 1672502400 1672502400123 1672502400123456
+-- !sql --
+\N
+
+-- !sql --
+9999-12-31T23:59:59.999999
+
+-- !sql --
+\N
+
diff --git
a/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
index 39b5ab9c89d..3e5548f0ac6 100644
---
a/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
+++
b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
@@ -320,4 +320,9 @@ suite("test_from_millisecond_microsecond") {
from millimicro
order by id;
"""
+
+ sql " set time_zone='Asia/Shanghai' "
+ qt_sql " select from_second(-1) "
+ qt_sql " select from_microsecond(253402271999999999) "
+ qt_sql " select from_microsecond(253402272000000000) "
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
