YQL-18303: Introduce StartOf/EndOf/TimeOfDay overloads

commit_hash:ed323f55ce6ca64b9a912772866d7bfb4fc1235f

5 files changed, 999 insertions, 25 deletions

diff --git a/yql/essentials/udfs/common/datetime2/datetime_udf.cpp b/yql/essentials/udfs/common/datetime2/datetime_udf.cpp
index edc65c12a2..61ee555e56 100644
--- a/yql/essentials/udfs/common/datetime2/datetime_udf.cpp
+++ b/yql/essentials/udfs/common/datetime2/datetime_udf.cpp
@@ -37,11 +37,13 @@ extern const char StartOfQuarterUDF[] = "StartOfQuarter";
 extern const char StartOfMonthUDF[] = "StartOfMonth";
 extern const char StartOfWeekUDF[] = "StartOfWeek";
 extern const char StartOfDayUDF[] = "StartOfDay";
+extern const char StartOfUDF[] = "StartOf";
 extern const char EndOfYearUDF[] = "EndOfYear";
 extern const char EndOfQuarterUDF[] = "EndOfQuarter";
 extern const char EndOfMonthUDF[] = "EndOfMonth";
 extern const char EndOfWeekUDF[] = "EndOfWeek";
 extern const char EndOfDayUDF[] = "EndOfDay";
+extern const char EndOfUDF[] = "EndOf";
 extern const char ShiftYearsUDF[] = "ShiftYears";
 extern const char ShiftQuartersUDF[] = "ShiftQuarters";
 extern const char ShiftMonthsUDF[] = "ShiftMonths";
@@ -1981,7 +1983,8 @@ private:
         return storage;
     }
 
-    TMaybe<TTMStorage> StartOf(TTMStorage storage, ui64 interval, const IValueBuilder& valueBuilder) {
+    template<typename TStorage>
+    TMaybe<TStorage> StartOf(TStorage storage, ui64 interval, const IValueBuilder& valueBuilder) {
         if (interval >= 86400000000ull) {
             // treat as StartOfDay
             SetStartOfDay(storage);
@@ -1998,7 +2001,8 @@ private:
         return storage;
     }
 
-    TMaybe<TTMStorage> EndOf(TTMStorage storage, ui64 interval, const IValueBuilder& valueBuilder) {
+    template<typename TStorage>
+    TMaybe<TStorage> EndOf(TStorage storage, ui64 interval, const IValueBuilder& valueBuilder) {
         if (interval >= 86400000000ull) {
             // treat as EndOfDay
             SetEndOfDay(storage);
@@ -2026,7 +2030,7 @@ private:
                 return;
             }
 
-            if (auto res = (UseEnd ? EndOf : StartOf)(storage, interval, *valueBuilder)) {
+            if (auto res = (UseEnd ? EndOf<TTMStorage> : StartOf<TTMStorage>)(storage, interval, *valueBuilder)) {
                 storage = res.GetRef();
                 sink(arg1);
             } else {
@@ -2035,33 +2039,141 @@ private:
         }
     };
 
-    BEGIN_SIMPLE_STRICT_ARROW_UDF(TStartOf, TOptional<TResource<TMResourceName>>(TAutoMap<TResource<TMResourceName>>, TAutoMap<TInterval>)) {
+    template<const char* TResourceName, auto Core>
+    TUnboxedValue SimpleDatetimeToIntervalUdf(const IValueBuilder* valueBuilder, const TUnboxedValuePod* args) {
         auto result = args[0];
         ui64 interval = std::abs(args[1].Get<i64>());
         if (interval == 0) {
             return result;
         }
-        if (auto res = StartOf(Reference(result), interval, *valueBuilder)) {
-            Reference(result) = res.GetRef();
+        auto& storage = Reference<TResourceName>(result);
+        if (auto res = Core(storage, interval, *valueBuilder)) {
+            storage = res.GetRef();
             return result;
         }
         return TUnboxedValuePod{};
     }
-    END_SIMPLE_ARROW_UDF(TStartOf, TStartEndOfBinaryKernelExec<false>::Do);
 
-    BEGIN_SIMPLE_STRICT_ARROW_UDF(TEndOf, TOptional<TResource<TMResourceName>>(TAutoMap<TResource<TMResourceName>>, TAutoMap<TInterval>)) {
-        auto result = args[0];
-        ui64 interval = std::abs(args[1].Get<i64>());
-        if (interval == 0) {
-            return result;
+template<const char* TUdfName, auto Boundary, auto WBoundary>
+class TBoundaryOfInterval: public ::NYql::NUdf::TBoxedValue {
+public:
+    typedef bool TTypeAwareMarker;
+    static const TStringRef& Name() {
+        static auto name = TStringRef(TUdfName, std::strlen(TUdfName));
+        return name;
+    }
+
+    static bool DeclareSignature(
+        const ::NYql::NUdf::TStringRef& name,
+        ::NYql::NUdf::TType* userType,
+        ::NYql::NUdf::IFunctionTypeInfoBuilder& builder,
+        bool typesOnly)
+    {
+        if (Name() != name) {
+            return false;
         }
-        if (auto res = EndOf(Reference(result), interval, *valueBuilder)) {
-            Reference(result) = res.GetRef();
-            return result;
+
+        if (!userType) {
+            builder.SetError("User type is missing");
+            return true;
         }
-        return TUnboxedValuePod{};
+
+        builder.UserType(userType);
+
+        const auto typeInfoHelper = builder.TypeInfoHelper();
+        TTupleTypeInspector tuple(*typeInfoHelper, userType);
+        Y_ENSURE(tuple, "Tuple with args and options tuples expected");
+        Y_ENSURE(tuple.GetElementsCount() > 0,
+                 "Tuple has to contain positional arguments");
+
+        TTupleTypeInspector argsTuple(*typeInfoHelper, tuple.GetElementType(0));
+        Y_ENSURE(argsTuple, "Tuple with args expected");
+        if (argsTuple.GetElementsCount() != 2) {
+            builder.SetError("Single argument expected");
+            return true;
+        }
+
+        auto argType = argsTuple.GetElementType(0);
+
+        if (const auto optType = TOptionalTypeInspector(*typeInfoHelper, argType)) {
+            argType = optType.GetItemType();
+        }
+
+        TResourceTypeInspector resource(*typeInfoHelper, argType);
+        if (!resource) {
+            TDataTypeInspector data(*typeInfoHelper, argType);
+            if (!data) {
+                SetInvalidTypeError(builder, typeInfoHelper, argType);
+                return true;
+            }
+
+            const auto features = NUdf::GetDataTypeInfo(NUdf::GetDataSlot(data.GetTypeId())).Features;
+            if (features & NUdf::BigDateType) {
+                BuildSignature<TM64ResourceName, WBoundary>(builder, typesOnly);
+                return true;
+            }
+            if (features & (NUdf::DateType | NUdf::TzDateType)) {
+                BuildSignature<TMResourceName, Boundary>(builder, typesOnly);
+                return true;
+            }
+
+            SetInvalidTypeError(builder, typeInfoHelper, argType);
+            return true;
+        }
+
+        if (resource.GetTag() == TStringRef::Of(TM64ResourceName)) {
+            BuildSignature<TM64ResourceName, WBoundary>(builder, typesOnly);
+            return true;
+        }
+
+        if (resource.GetTag() == TStringRef::Of(TMResourceName)) {
+            BuildSignature<TMResourceName, Boundary>(builder, typesOnly);
+            return true;
+        }
+
+        ::TStringBuilder sb;
+        sb << "Unexpected Resource tag: got '" << resource.GetTag() << "'";
+        builder.SetError(sb);
+        return true;
     }
-    END_SIMPLE_ARROW_UDF(TEndOf, TStartEndOfBinaryKernelExec<true>::Do);
+private:
+    template<auto Func>
+    class TImpl : public TBoxedValue {
+    public:
+        TUnboxedValue Run(const IValueBuilder* valueBuilder, const TUnboxedValuePod* args) const final {
+            try {
+                return Func(valueBuilder, args);
+            } catch (const std::exception&) {
+                    TStringBuilder sb;
+                    sb << CurrentExceptionMessage();
+                    sb << Endl << "[" << TStringBuf(Name()) << "]" ;
+                    UdfTerminate(sb.c_str());
+            }
+        }
+    };
+
+    static void SetInvalidTypeError(NUdf::IFunctionTypeInfoBuilder& builder,
+        ITypeInfoHelper::TPtr typeInfoHelper, const TType* argType)
+    {
+        ::TStringBuilder sb;
+        sb << "Invalid argument type: got ";
+        TTypePrinter(*typeInfoHelper, argType).Out(sb.Out);
+        sb << ", but Resource<" << TMResourceName <<"> or Resource<"
+           << TM64ResourceName << "> expected";
+        builder.SetError(sb);
+    }
+
+    template<const char* TResourceName, auto Func>
+    static void BuildSignature(NUdf::IFunctionTypeInfoBuilder& builder, bool typesOnly) {
+        builder.Returns<TOptional<TResource<TResourceName>>>();
+        builder.Args()->Add<TAutoMap<TResource<TResourceName>>>()
+            .template Add<TAutoMap<std::conditional_t<TResourceName == TMResourceName, TInterval, TInterval64>>>();
+        builder.IsStrict();
+        if (!typesOnly) {
+            builder.Implementation(new TImpl<Func>());
+        }
+    }
+};
 
     struct TTimeOfDayKernelExec : TUnaryKernelExec<TTimeOfDayKernelExec, TReaderTraits::TResource<false>, TFixedSizeArrayBuilder<TDataType<TInterval>::TLayout, false>> {
         template<typename TSink>
@@ -2072,13 +2184,126 @@ private:
         }
     };
 
-    const auto timeOfDayKernelExecDo = TTimeOfDayKernelExec::Do;
-    BEGIN_SIMPLE_STRICT_ARROW_UDF(TTimeOfDay, TInterval(TAutoMap<TResource<TMResourceName>>)) {
-        Y_UNUSED(valueBuilder);
-        auto& storage = Reference(args[0]);
-        return TUnboxedValuePod((i64)storage.ToTimeOfDay());
+class TTimeOfDay: public ::NYql::NUdf::TBoxedValue {
+public:
+    typedef bool TTypeAwareMarker;
+    static const ::NYql::NUdf::TStringRef& Name() {
+        static auto name = TStringRef::Of("TimeOfDay");
+        return name;
     }
-    END_SIMPLE_ARROW_UDF(TTimeOfDay, timeOfDayKernelExecDo);
+
+    static bool DeclareSignature(
+        const ::NYql::NUdf::TStringRef& name,
+        ::NYql::NUdf::TType* userType,
+        ::NYql::NUdf::IFunctionTypeInfoBuilder& builder,
+        bool typesOnly)
+    {
+        if (Name() != name) {
+            return false;
+        }
+
+        if (!userType) {
+            builder.SetError("User type is missing");
+            return true;
+        }
+
+        builder.UserType(userType);
+
+        const auto typeInfoHelper = builder.TypeInfoHelper();
+        TTupleTypeInspector tuple(*typeInfoHelper, userType);
+        Y_ENSURE(tuple, "Tuple with args and options tuples expected");
+        Y_ENSURE(tuple.GetElementsCount() > 0,
+                 "Tuple has to contain positional arguments");
+
+        TTupleTypeInspector argsTuple(*typeInfoHelper, tuple.GetElementType(0));
+        Y_ENSURE(argsTuple, "Tuple with args expected");
+        if (argsTuple.GetElementsCount() != 1) {
+            builder.SetError("Single argument expected");
+            return true;
+        }
+
+        auto argType = argsTuple.GetElementType(0);
+
+        if (const auto optType = TOptionalTypeInspector(*typeInfoHelper, argType)) {
+            argType = optType.GetItemType();
+        }
+
+        TResourceTypeInspector resource(*typeInfoHelper, argType);
+        if (!resource) {
+            TDataTypeInspector data(*typeInfoHelper, argType);
+            if (!data) {
+                SetInvalidTypeError(builder, typeInfoHelper, argType);
+                return true;
+            }
+
+            const auto features = NUdf::GetDataTypeInfo(NUdf::GetDataSlot(data.GetTypeId())).Features;
+            if (features & NUdf::BigDateType) {
+                BuildSignature<TM64ResourceName>(builder, typesOnly);
+                return true;
+            }
+            if (features & (NUdf::DateType | NUdf::TzDateType)) {
+                BuildSignature<TMResourceName>(builder, typesOnly);
+                return true;
+            }
+
+            SetInvalidTypeError(builder, typeInfoHelper, argType);
+            return true;
+        }
+
+        if (resource.GetTag() == TStringRef::Of(TM64ResourceName)) {
+            BuildSignature<TM64ResourceName>(builder, typesOnly);
+            return true;
+        }
+
+        if (resource.GetTag() == TStringRef::Of(TMResourceName)) {
+            BuildSignature<TMResourceName>(builder, typesOnly);
+            return true;
+        }
+
+        ::TStringBuilder sb;
+        sb << "Unexpected Resource tag: got '" << resource.GetTag() << "'";
+        builder.SetError(sb);
+        return true;
+    }
+private:
+    template<const char* TResourceName>
+    class TImpl : public TBoxedValue {
+    public:
+        TUnboxedValue Run(const IValueBuilder* valueBuilder, const TUnboxedValuePod* args) const final {
+            try {
+                Y_UNUSED(valueBuilder);
+                auto& storage = Reference<TResourceName>(args[0]);
+                return TUnboxedValuePod((i64)storage.ToTimeOfDay());
+            } catch (const std::exception&) {
+                    TStringBuilder sb;
+                    sb << CurrentExceptionMessage();
+                    sb << Endl << "[" << TStringBuf(Name()) << "]" ;
+                    UdfTerminate(sb.c_str());
+            }
+        }
+    };
+
+    static void SetInvalidTypeError(NUdf::IFunctionTypeInfoBuilder& builder,
+        ITypeInfoHelper::TPtr typeInfoHelper, const TType* argType)
+    {
+        ::TStringBuilder sb;
+        sb << "Invalid argument type: got ";
+        TTypePrinter(*typeInfoHelper, argType).Out(sb.Out);
+        sb << ", but Resource<" << TMResourceName <<"> or Resource<"
+           << TM64ResourceName << "> expected";
+        builder.SetError(sb);
+    }
+
+    template< const char* TResourceName>
+    static void BuildSignature(NUdf::IFunctionTypeInfoBuilder& builder, bool typesOnly) {
+        builder.Returns<std::conditional_t<TResourceName == TMResourceName, TInterval, TInterval64>>();
+        builder.Args()->Add<TAutoMap<TResource<TResourceName>>>();
+        builder.IsStrict();
+        if (!typesOnly) {
+            builder.Implementation(new TImpl<TResourceName>());
+        }
+    }
+};
 
 
     // Add ...
@@ -2959,7 +3184,8 @@ private:
                                     SimpleDatetimeToDatetimeUdf<TM64ResourceName, StartOfWeek<TTM64Storage>>>,
         TBoundaryOf<StartOfDayUDF, SimpleDatetimeToDatetimeUdf<TMResourceName, StartOfDay<TTMStorage>>,
                                     SimpleDatetimeToDatetimeUdf<TM64ResourceName, StartOfDay<TTM64Storage>>>,
-        TStartOf,
+        TBoundaryOfInterval<StartOfUDF, SimpleDatetimeToIntervalUdf<TMResourceName, StartOf<TTMStorage>>,
+                                        SimpleDatetimeToIntervalUdf<TM64ResourceName, StartOf<TTM64Storage>>>,
         TTimeOfDay,
 
         TShift<ShiftYearsUDF, DoAddYears<TMResourceName>, DoAddYears<TM64ResourceName>>,
@@ -2976,7 +3202,8 @@ private:
                                  SimpleDatetimeToDatetimeUdf<TM64ResourceName, EndOfWeek<TTM64Storage>>>,
         TBoundaryOf<EndOfDayUDF, SimpleDatetimeToDatetimeUdf<TMResourceName, EndOfDay<TTMStorage>>,
                                  SimpleDatetimeToDatetimeUdf<TM64ResourceName, EndOfDay<TTM64Storage>>>,
-        TEndOf,
+        TBoundaryOfInterval<EndOfUDF, SimpleDatetimeToIntervalUdf<TMResourceName, EndOf<TTMStorage>>,
+                                      SimpleDatetimeToIntervalUdf<TM64ResourceName, EndOf<TTM64Storage>>>,
 
         TToUnits<ToSecondsUDF, ui32, 1>,
         TToUnits<ToMillisecondsUDF, ui64, 1000>,
diff --git a/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_EndOf_/results.txt b/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_EndOf_/results.txt
index 1dc768bfd5..42f6cef30c 100644
--- a/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_EndOf_/results.txt
+++ b/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_EndOf_/results.txt
@@ -61,6 +61,56 @@
                                                     "Timestamp64"
                                                 ]
                                             ]
+                                        ];
+                                        [
+                                            "sopt13h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt15m";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt20s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt4h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt7s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
                                         ]
                                     ]
                                 ]
@@ -119,6 +169,56 @@
                                                     "Timestamp64"
                                                 ]
                                             ]
+                                        ];
+                                        [
+                                            "sopt13h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt15m";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt20s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt4h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt7s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
                                         ]
                                     ]
                                 ]
@@ -143,6 +243,21 @@
                             ];
                             [
                                 "-4611638275200000001"
+                            ];
+                            [
+                                "-4611669850800000001"
+                            ];
+                            [
+                                "-4611669893100000001"
+                            ];
+                            [
+                                "-4611669893860000001"
+                            ];
+                            [
+                                "-4611669883200000001"
+                            ];
+                            [
+                                "-4611669893876000001"
                             ]
                         ];
                         [
@@ -160,6 +275,21 @@
                             ];
                             [
                                 "-4611638275200000001"
+                            ];
+                            [
+                                "-4611669850800000001"
+                            ];
+                            [
+                                "-4611669893100000001"
+                            ];
+                            [
+                                "-4611669893860000001"
+                            ];
+                            [
+                                "-4611669883200000001"
+                            ];
+                            [
+                                "-4611669893876000001"
                             ]
                         ]
                     ];
@@ -179,6 +309,19 @@
                             ];
                             [
                                 "-62135596800000001"
+                            ];
+                            #;
+                            [
+                                "-62167132800000001"
+                            ];
+                            [
+                                "-62167132860000001"
+                            ];
+                            [
+                                "-62167132800000001"
+                            ];
+                            [
+                                "-62167132862000001"
                             ]
                         ];
                         [
@@ -196,6 +339,19 @@
                             ];
                             [
                                 "-62135596800000001"
+                            ];
+                            #;
+                            [
+                                "-62167132800000001"
+                            ];
+                            [
+                                "-62167132860000001"
+                            ];
+                            [
+                                "-62167132800000001"
+                            ];
+                            [
+                                "-62167132862000001"
                             ]
                         ]
                     ];
@@ -215,6 +371,21 @@
                             ];
                             [
                                 "-62104060800000001"
+                            ];
+                            [
+                                "-62135550000000001"
+                            ];
+                            [
+                                "-62135563500000001"
+                            ];
+                            [
+                                "-62135563840000001"
+                            ];
+                            [
+                                "-62135553600000001"
+                            ];
+                            [
+                                "-62135563844000001"
                             ]
                         ];
                         [
@@ -232,6 +403,21 @@
                             ];
                             [
                                 "-62104060800000001"
+                            ];
+                            [
+                                "-62135550000000001"
+                            ];
+                            [
+                                "-62135563500000001"
+                            ];
+                            [
+                                "-62135563840000001"
+                            ];
+                            [
+                                "-62135553600000001"
+                            ];
+                            [
+                                "-62135563844000001"
                             ]
                         ]
                     ];
@@ -251,6 +437,19 @@
                             ];
                             [
                                 "-1"
+                            ];
+                            #;
+                            [
+                                "-1"
+                            ];
+                            [
+                                "-60000001"
+                            ];
+                            [
+                                "-1"
+                            ];
+                            [
+                                "-62000001"
                             ]
                         ];
                         [
@@ -268,6 +467,19 @@
                             ];
                             [
                                 "-1"
+                            ];
+                            #;
+                            [
+                                "-1"
+                            ];
+                            [
+                                "-60000001"
+                            ];
+                            [
+                                "-1"
+                            ];
+                            [
+                                "-62000001"
                             ]
                         ]
                     ];
@@ -287,6 +499,21 @@
                             ];
                             [
                                 "31535999999999"
+                            ];
+                            [
+                                "46799999999"
+                            ];
+                            [
+                                "33299999999"
+                            ];
+                            [
+                                "32959999999"
+                            ];
+                            [
+                                "43199999999"
+                            ];
+                            [
+                                "32955999999"
                             ]
                         ];
                         [
@@ -304,6 +531,21 @@
                             ];
                             [
                                 "31535999999999"
+                            ];
+                            [
+                                "46799999999"
+                            ];
+                            [
+                                "33299999999"
+                            ];
+                            [
+                                "32959999999"
+                            ];
+                            [
+                                "43199999999"
+                            ];
+                            [
+                                "32955999999"
                             ]
                         ]
                     ];
@@ -323,6 +565,21 @@
                             ];
                             [
                                 "1767225599999999"
+                            ];
+                            [
+                                "1738673999999999"
+                            ];
+                            [
+                                "1738660499999999"
+                            ];
+                            [
+                                "1738660159999999"
+                            ];
+                            [
+                                "1738670399999999"
+                            ];
+                            [
+                                "1738660155999999"
                             ]
                         ];
                         [
@@ -340,6 +597,21 @@
                             ];
                             [
                                 "1767225599999999"
+                            ];
+                            [
+                                "1738673999999999"
+                            ];
+                            [
+                                "1738660499999999"
+                            ];
+                            [
+                                "1738660159999999"
+                            ];
+                            [
+                                "1738670399999999"
+                            ];
+                            [
+                                "1738660155999999"
                             ]
                         ]
                     ];
@@ -359,6 +631,21 @@
                             ];
                             [
                                 "4323283199999999"
+                            ];
+                            [
+                                "4291793999999999"
+                            ];
+                            [
+                                "4291780499999999"
+                            ];
+                            [
+                                "4291780159999999"
+                            ];
+                            [
+                                "4291790399999999"
+                            ];
+                            [
+                                "4291780155999999"
                             ]
                         ];
                         [
@@ -376,6 +663,21 @@
                             ];
                             [
                                 "4323283199999999"
+                            ];
+                            [
+                                "4291793999999999"
+                            ];
+                            [
+                                "4291780499999999"
+                            ];
+                            [
+                                "4291780159999999"
+                            ];
+                            [
+                                "4291790399999999"
+                            ];
+                            [
+                                "4291780155999999"
                             ]
                         ]
                     ];
@@ -393,6 +695,19 @@
                             #;
                             [
                                 "4611669811199999999"
+                            ];
+                            #;
+                            [
+                                "4611669811199999999"
+                            ];
+                            [
+                                "4611669811139999999"
+                            ];
+                            [
+                                "4611669811199999999"
+                            ];
+                            [
+                                "4611669811137999999"
                             ]
                         ];
                         [
@@ -408,6 +723,19 @@
                             #;
                             [
                                 "4611669811199999999"
+                            ];
+                            #;
+                            [
+                                "4611669811199999999"
+                            ];
+                            [
+                                "4611669811139999999"
+                            ];
+                            [
+                                "4611669811199999999"
+                            ];
+                            [
+                                "4611669811137999999"
                             ]
                         ]
                     ]
diff --git a/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_StartOf_/results.txt b/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_StartOf_/results.txt
index 8d83a5788c..52bd1cbf87 100644
--- a/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_StartOf_/results.txt
+++ b/yql/essentials/udfs/common/datetime2/test_bigdates/canondata/test.test_StartOf_/results.txt
@@ -33,6 +33,56 @@
                                             ]
                                         ];
                                         [
+                                            "sopt13h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt15m";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt20s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt4h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt7s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
                                             "soquarter";
                                             [
                                                 "OptionalType";
@@ -61,6 +111,16 @@
                                                     "Timestamp64"
                                                 ]
                                             ]
+                                        ];
+                                        [
+                                            "timeofday";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Interval64"
+                                                ]
+                                            ]
                                         ]
                                     ]
                                 ]
@@ -91,6 +151,56 @@
                                             ]
                                         ];
                                         [
+                                            "sopt13h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt15m";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt20s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt4h";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
+                                            "sopt7s";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Timestamp64"
+                                                ]
+                                            ]
+                                        ];
+                                        [
                                             "soquarter";
                                             [
                                                 "OptionalType";
@@ -119,6 +229,16 @@
                                                     "Timestamp64"
                                                 ]
                                             ]
+                                        ];
+                                        [
+                                            "timeofday";
+                                            [
+                                                "OptionalType";
+                                                [
+                                                    "DataType";
+                                                    "Interval64"
+                                                ]
+                                            ]
                                         ]
                                     ]
                                 ]
@@ -138,9 +258,27 @@
                             [
                                 "-4611669897600000000"
                             ];
+                            [
+                                "-4611669894000000000"
+                            ];
+                            [
+                                "-4611669893880000000"
+                            ];
+                            [
+                                "-4611669897600000000"
+                            ];
+                            [
+                                "-4611669893883000000"
+                            ];
+                            [
+                                "-4611669897600000000"
+                            ];
                             #;
                             [
                                 "-4611669897600000000"
+                            ];
+                            [
+                                "3723456789"
                             ]
                         ];
                         [
@@ -153,9 +291,27 @@
                             [
                                 "-4611669897600000000"
                             ];
+                            [
+                                "-4611669894000000000"
+                            ];
+                            [
+                                "-4611669893880000000"
+                            ];
+                            [
+                                "-4611669897600000000"
+                            ];
+                            [
+                                "-4611669893883000000"
+                            ];
+                            [
+                                "-4611669897600000000"
+                            ];
                             #;
                             [
                                 "-4611669897600000000"
+                            ];
+                            [
+                                "3723456789"
                             ]
                         ]
                     ];
@@ -168,11 +324,29 @@
                                 "-62167219200000000"
                             ];
                             [
+                                "-62167172400000000"
+                            ];
+                            [
+                                "-62167133700000000"
+                            ];
+                            [
+                                "-62167132880000000"
+                            ];
+                            [
+                                "-62167147200000000"
+                            ];
+                            [
+                                "-62167132869000000"
+                            ];
+                            [
                                 "-62167219200000000"
                             ];
                             #;
                             [
                                 "-62167219200000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ];
                         [
@@ -183,11 +357,29 @@
                                 "-62167219200000000"
                             ];
                             [
+                                "-62167172400000000"
+                            ];
+                            [
+                                "-62167133700000000"
+                            ];
+                            [
+                                "-62167132880000000"
+                            ];
+                            [
+                                "-62167147200000000"
+                            ];
+                            [
+                                "-62167132869000000"
+                            ];
+                            [
                                 "-62167219200000000"
                             ];
                             #;
                             [
                                 "-62167219200000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ]
                     ];
@@ -202,9 +394,27 @@
                             [
                                 "-62135596800000000"
                             ];
+                            [
+                                "-62135564400000000"
+                            ];
+                            [
+                                "-62135563860000000"
+                            ];
+                            [
+                                "-62135568000000000"
+                            ];
+                            [
+                                "-62135563851000000"
+                            ];
+                            [
+                                "-62135596800000000"
+                            ];
                             #;
                             [
                                 "-62135596800000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ];
                         [
@@ -217,9 +427,27 @@
                             [
                                 "-62135596800000000"
                             ];
+                            [
+                                "-62135564400000000"
+                            ];
+                            [
+                                "-62135563860000000"
+                            ];
+                            [
+                                "-62135568000000000"
+                            ];
+                            [
+                                "-62135563851000000"
+                            ];
+                            [
+                                "-62135596800000000"
+                            ];
                             #;
                             [
                                 "-62135596800000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ]
                     ];
@@ -232,11 +460,29 @@
                                 "-2678400000000"
                             ];
                             [
+                                "-39600000000"
+                            ];
+                            [
+                                "-900000000"
+                            ];
+                            [
+                                "-80000000"
+                            ];
+                            [
+                                "-14400000000"
+                            ];
+                            [
+                                "-69000000"
+                            ];
+                            [
                                 "-7948800000000"
                             ];
                             #;
                             [
                                 "-31536000000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ];
                         [
@@ -247,11 +493,29 @@
                                 "-2678400000000"
                             ];
                             [
+                                "-39600000000"
+                            ];
+                            [
+                                "-900000000"
+                            ];
+                            [
+                                "-80000000"
+                            ];
+                            [
+                                "-14400000000"
+                            ];
+                            [
+                                "-69000000"
+                            ];
+                            [
                                 "-7948800000000"
                             ];
                             #;
                             [
                                 "-31536000000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ]
                     ];
@@ -266,9 +530,27 @@
                             [
                                 "0"
                             ];
+                            [
+                                "32400000000"
+                            ];
+                            [
+                                "32940000000"
+                            ];
+                            [
+                                "28800000000"
+                            ];
+                            [
+                                "32949000000"
+                            ];
+                            [
+                                "0"
+                            ];
                             #;
                             [
                                 "0"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ];
                         [
@@ -281,9 +563,27 @@
                             [
                                 "0"
                             ];
+                            [
+                                "32400000000"
+                            ];
+                            [
+                                "32940000000"
+                            ];
+                            [
+                                "28800000000"
+                            ];
+                            [
+                                "32949000000"
+                            ];
+                            [
+                                "0"
+                            ];
                             #;
                             [
                                 "0"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ]
                     ];
@@ -296,6 +596,21 @@
                                 "1738368000000000"
                             ];
                             [
+                                "1738627200000000"
+                            ];
+                            [
+                                "1738659600000000"
+                            ];
+                            [
+                                "1738660140000000"
+                            ];
+                            [
+                                "1738656000000000"
+                            ];
+                            [
+                                "1738660149000000"
+                            ];
+                            [
                                 "1735689600000000"
                             ];
                             [
@@ -303,6 +618,9 @@
                             ];
                             [
                                 "1735689600000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ];
                         [
@@ -313,6 +631,21 @@
                                 "1738368000000000"
                             ];
                             [
+                                "1738627200000000"
+                            ];
+                            [
+                                "1738659600000000"
+                            ];
+                            [
+                                "1738660140000000"
+                            ];
+                            [
+                                "1738656000000000"
+                            ];
+                            [
+                                "1738660149000000"
+                            ];
+                            [
                                 "1735689600000000"
                             ];
                             [
@@ -320,6 +653,9 @@
                             ];
                             [
                                 "1735689600000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ]
                     ];
@@ -335,10 +671,28 @@
                                 "4291747200000000"
                             ];
                             [
+                                "4291779600000000"
+                            ];
+                            [
+                                "4291780140000000"
+                            ];
+                            [
+                                "4291776000000000"
+                            ];
+                            [
+                                "4291780149000000"
+                            ];
+                            [
+                                "4291747200000000"
+                            ];
+                            [
                                 "4291401600000000"
                             ];
                             [
                                 "4291747200000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ];
                         [
@@ -352,10 +706,28 @@
                                 "4291747200000000"
                             ];
                             [
+                                "4291779600000000"
+                            ];
+                            [
+                                "4291780140000000"
+                            ];
+                            [
+                                "4291776000000000"
+                            ];
+                            [
+                                "4291780149000000"
+                            ];
+                            [
+                                "4291747200000000"
+                            ];
+                            [
                                 "4291401600000000"
                             ];
                             [
                                 "4291747200000000"
+                            ];
+                            [
+                                "32949000009"
                             ]
                         ]
                     ];
@@ -368,6 +740,21 @@
                                 "4611667132800000000"
                             ];
                             [
+                                "4611669771600000000"
+                            ];
+                            [
+                                "4611669810300000000"
+                            ];
+                            [
+                                "4611669811120000000"
+                            ];
+                            [
+                                "4611669796800000000"
+                            ];
+                            [
+                                "4611669811131000000"
+                            ];
+                            [
                                 "4611661862400000000"
                             ];
                             [
@@ -375,6 +762,9 @@
                             ];
                             [
                                 "4611638275200000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ];
                         [
@@ -385,6 +775,21 @@
                                 "4611667132800000000"
                             ];
                             [
+                                "4611669771600000000"
+                            ];
+                            [
+                                "4611669810300000000"
+                            ];
+                            [
+                                "4611669811120000000"
+                            ];
+                            [
+                                "4611669796800000000"
+                            ];
+                            [
+                                "4611669811131000000"
+                            ];
+                            [
                                 "4611661862400000000"
                             ];
                             [
@@ -392,6 +797,9 @@
                             ];
                             [
                                 "4611638275200000000"
+                            ];
+                            [
+                                "86337654321"
                             ]
                         ]
                     ]
diff --git a/yql/essentials/udfs/common/datetime2/test_bigdates/cases/EndOf.sql b/yql/essentials/udfs/common/datetime2/test_bigdates/cases/EndOf.sql
index 5edefe615c..c9f01f4149 100644
--- a/yql/essentials/udfs/common/datetime2/test_bigdates/cases/EndOf.sql
+++ b/yql/essentials/udfs/common/datetime2/test_bigdates/cases/EndOf.sql
@@ -6,6 +6,11 @@ $check = ($arg) -> {
         eomonth:   DateTime::MakeTimestamp64(DateTime::EndOfMonth($arg)),
         eoweek:    DateTime::MakeTimestamp64(DateTime::EndOfWeek($arg)),
         eoday:     DateTime::MakeTimestamp64(DateTime::EndOfDay($arg)),
+        sopt13h:   DateTime::MakeTimestamp64(DateTime::EndOf($arg, Interval("PT13H"))),
+        sopt4h:    DateTime::MakeTimestamp64(DateTime::EndOf($arg, Interval("PT4H"))),
+        sopt15m:   DateTime::MakeTimestamp64(DateTime::EndOf($arg, Interval("PT15M"))),
+        sopt20s:   DateTime::MakeTimestamp64(DateTime::EndOf($arg, Interval("PT20S"))),
+        sopt7s:    DateTime::MakeTimestamp64(DateTime::EndOf($arg, Interval("PT7S"))),
     |>
 };
 
diff --git a/yql/essentials/udfs/common/datetime2/test_bigdates/cases/StartOf.sql b/yql/essentials/udfs/common/datetime2/test_bigdates/cases/StartOf.sql
index 342eb0a19a..a87567e6fa 100644
--- a/yql/essentials/udfs/common/datetime2/test_bigdates/cases/StartOf.sql
+++ b/yql/essentials/udfs/common/datetime2/test_bigdates/cases/StartOf.sql
@@ -6,6 +6,12 @@ $check = ($arg) -> {
         somonth:   DateTime::MakeTimestamp64(DateTime::StartOfMonth($arg)),
         soweek:    DateTime::MakeTimestamp64(DateTime::StartOfWeek($arg)),
         soday:     DateTime::MakeTimestamp64(DateTime::StartOfDay($arg)),
+        sopt13h:   DateTime::MakeTimestamp64(DateTime::StartOf($arg, Interval("PT13H"))),
+        sopt4h:    DateTime::MakeTimestamp64(DateTime::StartOf($arg, Interval("PT4H"))),
+        sopt15m:   DateTime::MakeTimestamp64(DateTime::StartOf($arg, Interval("PT15M"))),
+        sopt20s:   DateTime::MakeTimestamp64(DateTime::StartOf($arg, Interval("PT20S"))),
+        sopt7s:    DateTime::MakeTimestamp64(DateTime::StartOf($arg, Interval("PT7S"))),
+        timeofday: DateTime::TimeOfDay($arg),
     |>
 };